Run SQL Server BLOG – Page 3 – Just another Select of Ms SQL DBA site

20th September 2021

Get the subfolder sizes in a folder with PowerShell

List the subfolder sizes in a folder

Get-ChildItem  -force '\\servername\sql_backup\BACKUP_OLA' -ErrorAction SilentlyContinue | Where-Object { $_ -is [io.directoryinfo] } | % {
    $len = 0
    Get-ChildItem -recurse -force $_.fullname -ErrorAction SilentlyContinue | % { $len += $_.length }
    $_.fullname, '{0:N2} GB' -f ($len / 1Gb)
    }

Get-ChildItem -force '\\servername\sql_backup\BACKUP_OLA' -ErrorAction SilentlyContinue | Where-Object { $_ -is [io.directoryinfo] } | % {

$len = 0

Get-ChildItem -recurse -force $_.fullname -ErrorAction SilentlyContinue | % { $len += $_.length }

$_.fullname, '{0:N2} GB' -f ($len / 1Gb)

}

Result sample:

    /#  result

    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AI 15.61 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e002b 718.37 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BI 22.81 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e001b 94.21 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102184_STSS50E 97.33 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AP 17.52 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BP 36.99 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e003c 355.39 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e004b 3312.04 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS05AP 1.85 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS05BP 2.39 GB
    \\servername\sql_backup\BACKUP_OLA\AGSQL102318E005 0.90 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102185_STSS50P 0.07 GB


    #/

/# result

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AI 15.61 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e002b 718.37 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BI 22.81 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e001b 94.21 GB

\\servername\sql_backup\BACKUP_OLA\SQL102184_STSS50E 97.33 GB

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AP 17.52 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BP 36.99 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e003c 355.39 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e004b 3312.04 GB

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS05AP 1.85 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS05BP 2.39 GB

\\servername\sql_backup\BACKUP_OLA\AGSQL102318E005 0.90 GB

\\servername\sql_backup\BACKUP_OLA\SQL102185_STSS50P 0.07 GB

20th September 2021

Find old files in subfoders – older than 30 days with PowerShell

Find old files recursively with powershell script:

Get-ChildItem -path \\servername\fileshare\folder -Recurse -Force -File | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)}

1	Get-ChildItem -path \\servername\fileshare\folder -Recurse -Force -File \| Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)}

Find the oldest file:

Get-ChildItem -path \\servername\share\folder -Recurse -Force -File  | Sort-Object CreationTime | Select-Object -First 1

1	Get-ChildItem -path \\servername\share\folder -Recurse -Force -File \| Sort-Object CreationTime \| Select-Object -First 1

7th September 20217th September 2021

DISTRIBUTED TRANSACTION: DTC in SQL server

There is two SQL servers and DTC is enabled both of them.

One SQL server is local and another one is a linked server named test.

Run a Distributed Transaction in SQL server for test purpose:

USE AdventureWorks2014;  

Select * from  [AdventureWorks2014].[Production].[WorkOrder] 
where WorkOrderID <100



USE AdventureWorks2014;  
GO  
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION;  
-- Delete candidate from local instance.  
DELETE [AdventureWorks2014].[Production].[WorkOrder]
     WHERE WorkOrderID = 2;  
-- Delete candidate from remote instance.  
DELETE test.[AdventureWorks2014].[Production].[WorkOrder]
      WHERE WorkOrderID = 2;   
COMMIT TRANSACTION;  
GO

USE AdventureWorks2014;

Select * from [AdventureWorks2014].[Production].[WorkOrder]

where WorkOrderID <100

USE AdventureWorks2014;

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION;

-- Delete candidate from local instance.

DELETE [AdventureWorks2014].[Production].[WorkOrder]

WHERE WorkOrderID = 2;

-- Delete candidate from remote instance.

DELETE test.[AdventureWorks2014].[Production].[WorkOrder]

WHERE WorkOrderID = 2;

COMMIT TRANSACTION;

after running the T-SQL transaction you can check in the DTC report.

you can check the DTC connection and firewall settings:

DTC ping tool

In powershell:

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0058" -ResourceManagerPort 17100 -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0059"  -ResourceManagerPort 17100 -Verbose
Test-Dtc -RemoteComputerName "SERVER0059"  -ResourceManagerPort 17100 -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "APPSERVER418"

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0058" -ResourceManagerPort 17100 -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0059" -ResourceManagerPort 17100 -Verbose

Test-Dtc -RemoteComputerName "SERVER0059" -ResourceManagerPort 17100 -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "APPSERVER418"

If the remote server is another domain, you will get an error:
New-CimSession : WinRM cannot process the request. The following error with errorcode 0x80090322 occurred while using Kerberos authentication: An unknown security error occurred.

In this case you can set: No Authentication Required.

7th September 20217th September 2021

DTC and Java

How to enable DTC in SQL server for Java:

– copy sqljdbc_xa.dll to sql server binn folder from the EXTRACTED (but not installed) jdbc driver
– enable xa transactions in component services > computerts > Distributed Transaction Coordinator > Local DTC (properties, security tab) Enable XA transactions
– restart sql server (all components to be sure)
– run script on master db. (xa_install.sql it is also in the jdbc driver, next to the .sql)
– the user that will perform distributed transactions must have the role “SqlJDBCXAUser” set on the MASTER db!

20th August 202115th September 2021

Windows Performance tools

install WPT on the affected host. WPT is part of ADK. Or they can just download this archive – extract it on the server into C:\msdata folder. They should have at least 5Gb free space on that disk
Run elevated CMD and execute:

#perf_counters

logman delete %computername%_IO

logman create counter %computername%_IO -c “\LogicalDisk(*)\*” “\Memory\*” “\Network Adapter(*)\*” “\Network Interface(*)\*” “\Paging File(*)\*” “\PhysicalDisk(*)\*” “\Process(*)\*” “\Processor Information(*)\*” -max 512 -f bincirc -si 00:00:01 -ow

logman start %computername%_IO

#etw_trace

C:\msdata\xperf -on PROC_THREAD+LOADER+PROFILE+INTERRUPT+DPC+FILENAME+FILE_IO+FILE_IO_INIT+FLT_IO_INIT+FLT_IO+FLT_FASTIO+FLT_IO_FAILURE+DISK_IO+DISK_IO_INIT -stackwalk Profile+FileCreate+FileCleanup+FileClose+FileRead+FileWrite+FileSetInformation+FileDelete+FileRename+FileDirEnum+FileFlush+FileQueryInformation+MiniFilterPreOpInit+MiniFilterPostOpInit+DiskReadInit+DiskWriteInit+DiskFlushInit -BufferSize 1024 -MinBuffers 256 -MaxBuffers 2048 -MaxFile 4096 -FileMode Circular

In the folder C:\msdata create data_stop.cmd with following content:

logman stop %computername%_IO

C:\msdata\xperf -d C:\msdata\io.etl

Then ask customer to find an event id 17883 in the application event log, do right click -> attach and “attach task to this event”, in action for the task point it to the script – C:\msdata\data_stop.cmd
Also make sure following options set for the task:
Run whehet user is logged on or not
Runt with Hihgest privileges

Test it, write to the app event log:
Write-EventLog -LogName Application -EntryType Error -Source MSSQLSERVER -EventId 17883 -Message “TEST ERROR EVENT – PLEASE IGNORE IT.”

Test if they are running:

xperf -loggers “NT Kernel Logger”
or
logman query “NT Kernel Logger” -ets

If session exist – trace is running.

9th August 2021

SQL server or agent or any other login can access a fileshare?

Yes, but you need to check:

SELECT CURRENT_USER;  
xp_cmdshell 'whoami';

EXECUTE AS user = 'domain\user'
SELECT CURRENT_USER;  
xp_cmdshell 'whoami'
xp_cmdshell 'dir \\server\sharename'

Revert; -- exit from current user 
SELECT CURRENT_USER;

SELECT CURRENT_USER;

xp_cmdshell 'whoami';

EXECUTE AS user = 'domain\user'

SELECT CURRENT_USER;

xp_cmdshell 'whoami'

xp_cmdshell 'dir \\server\sharename'

Revert; -- exit from current user

SELECT CURRENT_USER;

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