July 2021 – Run SQL Server BLOG

30th July 2021

LOGMAN for SQL Server

en example for

logman.exe create counter SQLServerPerf -f bincirc -v mmddhhmm -max 500 -c "\Processor(*)\*" "\LogicalDisk(*)\*" "\Memory(*)\*" "\TCP\*" "\Network Interface(*)\*" "\PhysicalDisk(*)\*" "\Process(*)\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:SQL Statistics\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Memory Node(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Databases(*)\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Access Methods\*" "\SQLServer:Transactions\*" "\SQLServer:SQL Errors\*" "\ProcessorPerformance\*" -si 00:00:10

logman.exe create counter SQLServerPerf -f bincirc -v mmddhhmm -max 500 -c "\Processor(*)\*" "\LogicalDisk(*)\*" "\Memory(*)\*" "\TCP\*" "\Network Interface(*)\*" "\PhysicalDisk(*)\*" "\Process(*)\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:SQL Statistics\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Memory Node(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Databases(*)\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Access Methods\*" "\SQLServer:Transactions\*" "\SQLServer:SQL Errors\*" "\ProcessorPerformance\*" -si 00:00:10

28th July 202128th July 2021

Increased the default XEvent trace: system_health size and retention policy are in SQL Server

If you want to investigate a case the default trace setting can be readable for few day and after that they are overwritten.

The default setting is 10 x 100 MB files.

You can check the oldest log:

DECLARE @file_name AS nvarchar(max);
DECLARE @file_path AS nvarchar(max);

SELECT 
    @file_name = 
        CAST(st.target_data AS xml).value(
            N'(EventFileTarget/File/@name)[1]', N'nvarchar(max)')
FROM sys.dm_xe_sessions s
    INNER JOIN sys.dm_xe_session_targets st
        ON s.[address] = st.event_session_address
WHERE 
    st.target_name = 'event_file'
    AND s.[name] = 'system_health';


SELECT @file_path = LEFT(
    @file_name,
    LEN(@file_name) - CHARINDEX('\', REVERSE(@file_name)) + 1);

SELECT
    files.[file_name],
    MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event
FROM sys.fn_xe_file_target_read_file
(
    @file_path + 'system_health*',
    null, null, null
) files
GROUP BY files.[file_name]
OPTION(NO_PERFORMANCE_SPOOL, QUERYTRACEON 8649);

DECLARE @file_name AS nvarchar(max);

DECLARE @file_path AS nvarchar(max);

SELECT

@file_name =

CAST(st.target_data AS xml).value(

N'(EventFileTarget/File/@name)[1]', N'nvarchar(max)')

FROM sys.dm_xe_sessions s

INNER JOIN sys.dm_xe_session_targets st

ON s.[address] = st.event_session_address

WHERE

st.target_name = 'event_file'

AND s.[name] = 'system_health';

SELECT @file_path = LEFT(

@file_name,

LEN(@file_name) - CHARINDEX('\', REVERSE(@file_name)) + 1);

SELECT

files.[file_name],

MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event

FROM sys.fn_xe_file_target_read_file

(

@file_path + 'system_health*',

null, null, null

) files

GROUP BY files.[file_name]

OPTION(NO_PERFORMANCE_SPOOL, QUERYTRACEON 8649);

Hey , it is only 3 days! you have to backup the files. A better solution to survive a long weekend to increase the retention, for example using 30 files:

ALTER EVENT SESSION [system_health] ON SERVER DROP TARGET package0.event_file
ALTER EVENT SESSION [system_health] ON SERVER
ADD TARGET package0.event_file
(
SET filename=N'system_health.xel',
max_file_size=(100),
max_rollover_files=(30)
)

ALTER EVENT SESSION [system_health] ON SERVER DROP TARGET package0.event_file

ALTER EVENT SESSION [system_health] ON SERVER

ADD TARGET package0.event_file

(

SET filename=N'system_health.xel',

max_file_size=(100),

max_rollover_files=(30)

)

21st July 202121st July 2021

Collect Index defragmentation

I do it for registered servers:

Declare @TableIndex table (ServerName sql_variant, DBName nvarchar(128), SchemaName varchar(128), TableName nvarchar (128), IndexName nvarchar (128), index_type_desc nvarchar (60),FragPercent float, record_count bigint, create_date datetime, modify_date datetime);

INSERT into @TableIndex 
exec SP_MSforeachdb @command1 = 
            'use [?];
			 IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'',''SQLMonitor'')
			 BEGIN

                SELECT 
					
					SERVERPROPERTY(''ServerName'')  
					,DB_NAME() as DBName
					,s.[name]  AS SchemaName
					,t.[name] AS TableName
					 ,i.NAME AS index_name
					 ,index_type_desc

					 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
					 ,record_count AS table_record_count
					 ,create_date
					 ,modify_date
					FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''SAMPLED'') ips
					INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
					INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
					INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
					where record_count>1000
					ORDER BY record_count desc, avg_fragmentation_in_percent DESC
				END'
select * from @TableIndex 
go

Declare @TableIndex table (ServerName sql_variant, DBName nvarchar(128), SchemaName varchar(128), TableName nvarchar (128), IndexName nvarchar (128), index_type_desc nvarchar (60),FragPercent float, record_count bigint, create_date datetime, modify_date datetime);

INSERT into @TableIndex

exec SP_MSforeachdb @command1 =

'use [?];

IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'',''SQLMonitor'')

BEGIN

SELECT

SERVERPROPERTY(''ServerName'')

,DB_NAME() as DBName

,s.[name] AS SchemaName

,t.[name] AS TableName

,i.NAME AS index_name

,index_type_desc

,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

,record_count AS table_record_count

,create_date

,modify_date

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''SAMPLED'') ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

where record_count>1000

ORDER BY record_count desc, avg_fragmentation_in_percent DESC

END'

select * from @TableIndex

10th July 202110th July 2021

Install SSMS without Azure Data Studio

Starting with SSMS 18.7, SSMS installs a system version of Azure Data Studio by default. If an equal or greater system version of Azure Data Studio stable or insiders is already present on the workstation compared to the included version of Azure Data Studio, the installation of Azure Data Studio by SSMS is skipped. The Azure Data Studio version can be found in the release notes.

If you do not want to install ADS (Azure Data Studio) use the SSMS installer with the command line flag DoNotInstallAzureDataStudio=1.

SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1

1	SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1

10th July 202110th July 2021

SSISDB update failed during the SQL CU or SP udpate

Reason: the cu found a SSIDB and wanted to upgrade it.
It failed because SSIS component is not installed to the server.

It needs: Cannot bulk load. The file “C:\Program Files\Microsoft SQL Server\150\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll” does not exist or you don’t have file access rights.
Yes it is missing , because SSIS component installed.

Without the ssisdb the sql server was not able to start.

It recommended to reinstall the master. Do NOT reinstall the master.

My solution was:

Install (add ) the Integration Service component.
Start the server with 902 flag (admin command prompt)

net start MSSQL$SVAL26T /T902

1

net start MSSQL$SVAL26T /T902

create a missing SQL login and connect to hte SSISD

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'anything', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE SSISDB
GO

ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'anything', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE SSISDB

ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

Reason:

If you don’t create SSISDB locally (you attach by migration), the SQL login will missing.

Help:

After installing Integration service it gave enough hint:

2021-07-10 14:12:47.73 spid10s Cannot find the login ‘##MS_SSISServerCleanupJobLogin##’, because it does not exist or you do not have permission.

2021-07-10 14:12:47.73 spid10s Error: 912, Severity: 21, State: 2.

2021-07-10 14:12:47.73 spid10s Script level upgrade for database ‘master’ failed because upgrade step ‘ISServer_upgrade.sql’ encountered error 15151, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

SUM: Please check if there is a SSIDB in the SQL server before install SP or CU.

9th July 2021

Find and list read and writeable user databases which are only in local instance or in primary AG group

List database names, which are in AO AG:

SELECT name FROM sys.sysdatabases where dbid>4 and name  in(
SELECT  DB_NAME(drs.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
and drs.replica_id = ars.replica_id
where ars.role_desc='PRIMARY' 
OR ars.role_desc='SECONDARY'
)

SELECT name FROM sys.sysdatabases where dbid>4 and name in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY'

OR ars.role_desc='SECONDARY'

)

Local databases , which are outside of AO AG:

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(drs.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
and drs.replica_id = ars.replica_id
where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

Writeable databases : primary db or only in local instance

    SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
    SELECT  DB_NAME(drs.database_id) as name
    FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
    JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
    JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
    and drs.replica_id = ars.replica_id
    where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

    UNION

    SELECT name FROM sys.sysdatabases where dbid>4 and name  in(
    SELECT  DB_NAME(drs.database_id) as name
    FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
    JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
    JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
    and drs.replica_id = ars.replica_id
    where ars.role_desc='PRIMARY' 
    --OR ars.role_desc='SECONDARY'

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

UNION

SELECT name FROM sys.sysdatabases where dbid>4 and name in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY'

--OR ars.role_desc='SECONDARY'

2nd July 2021

Find limited SQL database files and log files

How to list limited database file size in a SQL server?

SELECT DB_NAME(database_id) AS DatabaseName,
[Name] AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB 
--, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]
,* 
FROM sys.master_files
where  not (max_size = -1 or   max_size = 2684354561 )   -- (268435456 * 8000 = 2147483648000 = 2TB)
--  and DB_NAME(database_id) not in ('master','tempdb','model')
order by DatabaseName

SELECT DB_NAME(database_id) AS DatabaseName,

[Name] AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB

--, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]

FROM sys.master_files

where not (max_size = -1 or max_size = 2684354561 ) -- (268435456 * 8000 = 2147483648000 = 2TB)

-- and DB_NAME(database_id) not in ('master','tempdb','model')

order by DatabaseName