Not sure what causes these keystrokes to stop working, but if you haven’t made any changes to SSMS keyboard options you can just push the RESET button. And it works again.
SQL SMO Scripting Options broken in versions 2012-2016
Some notes about SSMS.
Here’s where the problem lies. If you are installing SQL 2012-2016 your Scripting Options may not work.
For SQL Server 2014 SSMS is included in the SQL install and SP.
The fix:
Even though you might only be installing SQL Server 2014 because SMO Options didn’t always work between versions 2012-2016 we need to install SQL Server 2017 SSMS 17.9.1.
e.g. What was broken was the SMO Scripting Option for
$Scripter.Options.NoFileGroup = $false
which is the default but even explicitly forcing it had no effect.
Simply adding SSMS 17.9.1 allowed correct scripting from the server.
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
SSMS 17.9.1 is the current General Availability (GA) version of SSMS
1) download Download SQL Server Management Studio 17.9.1
2) download Download SQL Server Management Studio 17.9.1 Upgrade Package (upgrades 17.x to 17.9.1)
Version Information
Release number: 17.9.1
Build number: 14.0.17289.0
Release date: November 21, 2018
REF: Page allocation changes in Sql Server 2016
While searching for information about MIXED_PAGE_ALLOCATION in SQL 2016+ and TF1118 I came across great examples about Page allocation changes in Sql Server 2016 here
Summary: For user databases in the versions of Sql Server older than 2016, the first 8 data pages were allocated from the mixed extent and next pages from uniform extent. In Sql Server 2016 even the first eight data pages were allocated from the uniform extent. Below image summaries this page allocation mechanism comparison between Sql Server 2016 and older versions of Sql Server
PowerShell: Get list of registered SQL Servers
No reason to maintain more than one list of SQL Servers when you can query SSMS’s Registered Servers.
Import-Module SQLPS -DisableNameChecking
$SQLInstanceProd = @()
$ServerGroup = 'PRODUCTION'
$SQLPath = "SQLSERVER:\sqlregistration\Database Engine Server Group\PROD*\"
$SQLInstanceProd += @(get-childitem $SQLPath -Recurse).Name | ?{$_ -notmatch '\d\d\d\d'} | % {
$MyPSObject = New-Object PSObject -Property @{
SQLInstance = $_
ServerGroup = $ServerGroup;
}
$MyPSObject
}
$SQLInstanceProd
Putting that logic into Get-RegisteredServers I populate the following list in my Profile.
$AllServersList = (Get-RegisteredServers).SQLInstance
$AllComputersList = $AllServersList | %{ $_.split('\')[0] }
$SQLInstanceDev = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'DEV'}).SQLInstance
$SQLInstanceTest = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'TEST'}).SQLInstance
$SQLInstanceProd = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'PROD'}).SQLInstance
Where are tables located in database files?
This uses indexes to locate the files.
SELECT [o].[crdate],
DATEDIFF(MINUTE, [o].[crdate], GETDATE()) AS [MinAgo],
[o].[name] AS [Tablename],
[i].[indid],
[i].[name] AS [IndexName],
[i].[rowcnt],
[i].[groupid],
[f].[name] AS [FileGroupName],
[d].[file_id],
[d].[physical_name],
[s].[name] AS [DataSpace]
FROM [sys].[sysobjects] [o]
JOIN [sys].[objects] [so]
ON [so].[object_id] = [o].[id]
JOIN [sys].[sysindexes] [i]
ON [i].[id] = [o].[id]
JOIN [sys].[filegroups] [f]
ON [f].[data_space_id] = [i].[groupid]
JOIN [sys].[database_files] [d]
ON [f].[data_space_id] = [d].[data_space_id]
JOIN [sys].[data_spaces] [s]
ON [f].[data_space_id] = [s].[data_space_id]
WHERE [is_ms_shipped] = 0
AND [i].[name] IS NOT NULL
ORDER BY [Tablename],
[IndexName],
[d].[file_id]
SQL: Where are the indexes located?
Where are the indexes located?
SELECT 'table_name' = OBJECT_NAME(i.id),
i.indid,
'index_name' = i.name,
i.groupid,
'filegroup' = f.name,
'file_name' = d.physical_name,
'dataspace' = s.name
FROM sys.sysindexes i,
sys.filegroups f,
sys.database_files d,
sys.data_spaces s
WHERE OBJECTPROPERTY(i.id, 'IsUserTable') = 1
AND f.data_space_id = i.groupid
AND f.data_space_id = d.data_space_id
AND f.data_space_id = s.data_space_id
AND i.name LIKE 'ndx%'
ORDER BY f.name,
OBJECT_NAME(i.id),
groupid
g
SSMS 2016 individual download
You can now download the SQL Server 2016 SSMS here.
SQL Lessons: SQL Server Configuration Manger
Part of my series pointing to valuable instruction on the web.
What Every Accidental DBA Needs to Know Now: SQL Server Configuration Manger
The SQL Server Configuration Manager is an critical but often overlooked application for proper SQL Server instance management and, well, “configuration”. In Tim Ford’s continuing series aimed at the Accidental Database Administrator he takes time to explain the basics of this tool, its use, and its features.
Do NT Service accounts need to be SysAdmin when using Domain Accounts
Yes. You should keep the accounts.
SQL Configuration: Auto Update Stats Async Enabled
Brent Ozar is the best starting place for “Auto Update Stats Async Enabled” and has the reference links for further research. He says:
We recommend setting it back to the default (disabled) unless you’ve got evidence that delays caused by statistics updates are causing you problems.
Tara Kizer warns:
Here is how to enable the option:
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ONEnabling the async option affects your ability to put a database into single-user mode. The option must be disabled to put a database into single-user mode as the async option uses a background thread which takes a connection in the database.
Since I’ll probably forget changing the setting and we don’t have problems at the moment with rogue queries, and we sometimes change to SINGLE USER MODE, I’ll pass.