Gabor – Page 3 – Run SQL Server BLOG

25th February 202210th May 2022

Send email if performance monitor counter value average is high

Do you need an alert for CPU, for network?
Here is an example for one network card.
You can put in a ps1 file and run it from task scheduler.
You should set the email and SMTP addresses:

######## task scheduler #######################
#
# prereq for powershel by task scheduler : 
# !! -   Set-ExecutionPolicy Unrestricted  - !!
#
#  user:  SYSTEM
#
# run whereher user is logged on or not
# run with highest privileges
#
# action: start a program
# program/script powershell
# add arguments (optional) : -file D:\Budapest_DBA\MonitoringDiskSpaceReporting\volumespace-monitoring-smtp-mail.ps1
# Start in (optional): D:\Budapest_DBA\MonitoringDiskSpaceReporting\
################################################

    
    $perfvalue= (Get-Counter -counter "\\server0056\network interface(hp nc375i integrated quad port multifunction gigabit server adapter)\bytes total/sec" -MaxSamples 3 -SampleInterval 5 | select -expand CounterSamples | Measure-Object -average cookedvalue).Average
    Write-Output $perfvalue
        if(   $perfvalue -ge 5000){ 

            $body ="Automata monitoring alert email.
                    Network Traffic is high, maybe take a look:<br>
                    \Network Interface(*)\Bytes Total/sec  value:<b> $perfvalue </b><br>
                    Gábor" 
            
                
            Send-MailMessage -Body $body -BodyAsHtml  -From "sqlserver@company.com" -SmtpServer "smtp.company.com" -Subject "performance alert $env:computername Network Traffic" -To "dbateam@company.com"  -Encoding utf8
        }

######## task scheduler #######################

# prereq for powershel by task scheduler :

# !! - Set-ExecutionPolicy Unrestricted - !!

# user: SYSTEM

# run whereher user is logged on or not

# run with highest privileges

# action: start a program

# program/script powershell

# add arguments (optional) : -file D:\Budapest_DBA\MonitoringDiskSpaceReporting\volumespace-monitoring-smtp-mail.ps1

# Start in (optional): D:\Budapest_DBA\MonitoringDiskSpaceReporting\

################################################

$perfvalue= (Get-Counter -counter "\\server0056\network interface(hp nc375i integrated quad port multifunction gigabit server adapter)\bytes total/sec" -MaxSamples 3 -SampleInterval 5 | select -expand CounterSamples | Measure-Object -average cookedvalue).Average

Write-Output $perfvalue

if( $perfvalue -ge 5000){

$body ="Automata monitoring alert email.

Network Traffic is high, maybe take a look:<br>

\Network Interface(*)\Bytes Total/sec value:<b> $perfvalue </b><br>

Gábor"

Send-MailMessage -Body $body -BodyAsHtml -From "sqlserver@company.com" -SmtpServer "smtp.company.com" -Subject "performance alert $env:computername Network Traffic" -To "dbateam@company.com" -Encoding utf8

}

27th January 202210th May 2022

SQL AO AG failover in cluster group and check the synchronization status

Get info from Cluster:

Get-Cluster
Get-ClusterNode
Get-ClusterGroup
Get-ClusterResource
Get-ClusterResource | Select-Object * | Sort-Object cluster,ownernode,name | Out-GridView
Get-ClusterResource | Select-Object cluster,ownernode,name, ownergroup,resourcetype, state,characteristics  | Sort-Object cluster,ownernode,name | Out-GridView

Get-Cluster

Get-ClusterNode

Get-ClusterGroup

Get-ClusterResource

Get-ClusterResource | Select-Object * | Sort-Object cluster,ownernode,name | Out-GridView

Get-ClusterResource | Select-Object cluster,ownernode,name, ownergroup,resourcetype, state,characteristics | Sort-Object cluster,ownernode,name | Out-GridView

Failover general example, you can use Invoke-DbaAgFailover for SQL server:

# Invoke-DbaAgFailover dbatools powershell command to failover

#  Switch the all cluster group to the another node in verbose:
# do NOT do in production environment:
Get-ClusterGroup | Move-ClusterGroup -verbose


#  Switch the cluster to the particular Node:
# do NOT do in production environment:
#  Get-ClusterGroup | Move-ClusterGroup -Node ideanodeneve


# move one group example:
Move-ClusterGroup "Available Storage"
Move-ClusterGroup "Cluster Group"

# Invoke-DbaAgFailover dbatools powershell command to failover

# Switch the all cluster group to the another node in verbose:

# do NOT do in production environment:

Get-ClusterGroup | Move-ClusterGroup -verbose

# Switch the cluster to the particular Node:

# do NOT do in production environment:

# Get-ClusterGroup | Move-ClusterGroup -Node ideanodeneve

# move one group example:

Move-ClusterGroup "Available Storage"

Move-ClusterGroup "Cluster Group"

DBATools makes your life happy, you can find the SQL server instances and you can failover them.

To find all of the SQL instances in a cluster server . To Check AG before and after the failover, and failover the SQL Server AO AG :

# cluster::::::
# (Get-ClusterNode).name | Find-DbaInstance
# find SQL server instances in the cluster nodes
$SqlServers = (Get-ClusterNode).name | Find-DbaInstance
# remote cluster server example:
# $SqlServers = (Get-ClusterNode -cluster wvvzrz0020cl).name | Find-DbaInstance
$SqlServers
$SqlServersOnline = $SqlServers | Where-Object {$_.Availability -eq "Available" }
$SqlServersOnline
if ($SqlServers.Count -ne $SqlServersOnline.Count) { Write-Host "There are unavailable instances!!!!!" -ForegroundColor Red  -BackgroundColor Yellow}
# failover GUI
Get-DbaAvailabilityGroup -SqlInstance $SqlServersOnline | Out-GridView -Passthru | Invoke-DbaAgFailover # -Confirm:$false -Whatif
# instance ellenorzes
Get-DbaAgDatabase -SqlInstance $SqlServersOnline | Out-GridView

# cluster::::::

# (Get-ClusterNode).name | Find-DbaInstance

# find SQL server instances in the cluster nodes

$SqlServers = (Get-ClusterNode).name | Find-DbaInstance

# remote cluster server example:

# $SqlServers = (Get-ClusterNode -cluster wvvzrz0020cl).name | Find-DbaInstance

$SqlServers

$SqlServersOnline = $SqlServers | Where-Object {$_.Availability -eq "Available" }

$SqlServersOnline

if ($SqlServers.Count -ne $SqlServersOnline.Count) { Write-Host "There are unavailable instances!!!!!" -ForegroundColor Red -BackgroundColor Yellow}

# failover GUI

Get-DbaAvailabilityGroup -SqlInstance $SqlServersOnline | Out-GridView -Passthru | Invoke-DbaAgFailover # -Confirm:$false -Whatif

# instance ellenorzes

Get-DbaAgDatabase -SqlInstance $SqlServersOnline | Out-GridView

if you have to wait a lot for synchronization you can check how many servers are synchronized.

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }
do { Write-Host; Write-Host "*********************************************************************************************";`
    $Databases= Get-DbaAgDatabase -SqlInstance $SqlServersOnline;     Start-Sleep -Seconds 1.0 ; `
    Start-Sleep -Seconds 0.5 ; `
    Write-Host -NoNewline (Get-Cluster).Name "has" $Databases.Count " AG databases. "  ; `
    Write-Host ($Databases | Where-Object {$_.SynchronizationState -eq "Synchronized" }).count "databases are synchronized at" (Get-Date).ToString();  `
    Write-Host -NoNewline "Out of Synchron:" ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }) | Select-Object Name ; `
} `
while ( ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }).count -ne 0 );

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }

do { Write-Host; Write-Host "*********************************************************************************************";`

$Databases= Get-DbaAgDatabase -SqlInstance $SqlServersOnline; Start-Sleep -Seconds 1.0 ; `

Start-Sleep -Seconds 0.5 ; `

Write-Host -NoNewline (Get-Cluster).Name "has" $Databases.Count " AG databases. " ; `

Write-Host ($Databases | Where-Object {$_.SynchronizationState -eq "Synchronized" }).count "databases are synchronized at" (Get-Date).ToString(); `

Write-Host -NoNewline "Out of Synchron:" ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }) | Select-Object Name ; `

} `

while ( ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }).count -ne 0 );

6th January 20226th January 2022

What is my default backup BUFFERCOUNT and MAXTRANSFERSIZE Data Transfer Options?

Error message: BackupIoRequest::ReportIoError: write failure on backup device \\filer\sharename\.. bak Operating system error 8(Not enough memory resources are available to process this command.).

It may you want a better performance for backup (less cpu and shorter backup time, so you can tune the BUFFERCOUNT and MAXTRANSFERSIZE

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
–Data Transfer Options BUFFERCOUNT = { buffercount | @buffercount_variable } | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

Books Online gives the following definition for these two options:

BUFFERCOUNT = { buffercount | @ buffercount_variable }

Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause “out of memory” errors because of inadequate virtual address space in the Sqlservr.exe process.

Based on the values that you provide or do not provide, SQL Server specifies the size contiguous buffers that it will use to perform the backup. This is of utmost importance in 32-bit environments as large amount of contiguous memory allocation can prove to be fatal to the non-Buffer Pool region of the SQL Virtual Address Space. This can cause your backups to fail. When SQLVDI is being used, this is of utmost importance because we cannot change the MAXTRANSFERSIZE after the VDI Configuration has been completed. The amount of contiguous virtual memory is determined by the number of backup devices and by the number of volumes on which the database files reside on.

You can use Trace Flag 3213 to review your backup/restore configuration parameters while performing a backup/restore operation. I shall proceed to show you how specifying the incorrect BUFFERCOUNT values or not providing it can prove to be fatal.

So how to get the buffercount?

DBCC TRACEON (3605); 
DBCC TRACEON (3213); 
BACKUP DATABASE WideWorldImporters TO DISK = 'WideWorldImporters.bak';
DBCC TRACEOFF (3213); 
DBCC TRACEOFF (3605);

DBCC TRACEON (3605);

DBCC TRACEON (3213);

BACKUP DATABASE WideWorldImporters TO DISK = 'WideWorldImporters.bak';

DBCC TRACEOFF (3213);

DBCC TRACEOFF (3605);

Sample result:

out of memory backup BUFFERCOUNT and MAXTRANSFERSIZE

28th December 20211st February 2022

Monitor the active session scheduled by SQL agent service

It is very common that you should investigate what is going on the server at a certain time and you can not find any scheduled job. Yes, the application run a big job. If you want to see current processes, you can run:

EXEC sp_WhoIsActive

1	EXEC sp_WhoIsActive

If you want to log it, you can schedule for every 1-10 minutes (you have to set the @destination_table and @destination_database, and the table will be created at the first running time):

SET NOCOUNT ON;

DECLARE @retention INT = 7,
        @destination_table VARCHAR(500) = 'WhoIsActive',
        @destination_database sysname = 'SQLMonitor',
        @schema VARCHAR(MAX),
        @SQL NVARCHAR(4000),
        @parameters NVARCHAR(500),
        @exists BIT;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
    BEGIN;
        EXEC dbo.sp_WhoIsActive 
                           		@get_full_inner_text = 1,
                                @get_plans = 1,
                                @get_outer_command = 1,
                                @find_block_leaders = 1,
                                @get_transaction_info = 1,
                                @return_schema = 1,
                                @schema = @schema OUTPUT;
        SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
        EXEC ( @schema );
    END;

--create index on collection_time
SET @SQL
    = 'USE ' + QUOTENAME(@destination_database)
      + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0
    BEGIN;
        SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
        EXEC ( @SQL );
    END;

--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_full_inner_text = 1,
                                @get_plans = 1,
                                @get_outer_command = 1,
                                @find_block_leaders = 1,
                                @get_transaction_info = 1,
                        @destination_table = @destination_table;

--purge older data
SET @SQL
    = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
      + ', GETDATE());';
EXEC ( @SQL );

SET NOCOUNT ON;

DECLARE @retention INT = 7,

@destination_table VARCHAR(500) = 'WhoIsActive',

@destination_database sysname = 'SQLMonitor',

@schema VARCHAR(MAX),

@SQL NVARCHAR(4000),

@parameters NVARCHAR(500),

@exists BIT;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

--create the logging table

IF OBJECT_ID(@destination_table) IS NULL

BEGIN;

EXEC dbo.sp_WhoIsActive

@get_full_inner_text = 1,

@get_plans = 1,

@get_outer_command = 1,

@find_block_leaders = 1,

@get_transaction_info = 1,

@return_schema = 1,

@schema = @schema OUTPUT;

SET @schema = REPLACE(@schema, '<table_name>', @destination_table);

EXEC ( @schema );

END;

--create index on collection_time

SET @SQL

= 'USE ' + QUOTENAME(@destination_database)

+ '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';

SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';

EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0

BEGIN;

SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';

EXEC ( @SQL );

END;

--collect activity into logging table

EXEC dbo.sp_WhoIsActive @get_full_inner_text = 1,

@get_plans = 1,

@get_outer_command = 1,

@find_block_leaders = 1,

@get_transaction_info = 1,

@destination_table = @destination_table;

--purge older data

SET @SQL

= 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))

+ ', GETDATE());';

EXEC ( @SQL );

30th October 2021

Install or update PowerShell 7 on Windows Server (v10)

You can use this single command in Windows PowerShell to install PowerShell 7.

iex "& { $(irm https://aka.ms/install-powershell.ps1) } -UseMSI"

1	iex "& { $(irm https://aka.ms/install-powershell.ps1) } -UseMSI"

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.