Friday 29 November 2013

SQL Server Server Side Trace


Often I get request by my clients to run SQL traces in odd hours for performance analysis, SQL Traces are resource expensive operation and we have to be careful how we run the SQL Profiler.
It is not recommended to run SQL Trace through SQL profiler GUI for a long time over the network, You may end up creating performance issues with the server you are running the trace for,
The most effective way to run SQL traces is running a Server side trace and most importantly we can automate this and schedule it as when required though SQL Agent. Let us see in this article
we can run a server side SQL trace.

First thing we need to prepare for a server side trace is the trace template, It can be easily prepared for your events and columns using SQL Profiler as below,



Select the event and columns you require in the trace,

 
Add the appropriate column filter to reduce the unwanted data in the trace,
 
 

Now Run the trace for couple of seconds and stop it but do not close the window. Go To File->Export->Script Trace Definition->For SQL Server 2005 – SQL11…  and save the template as .SQL file locally.

 
 

Now Open the trace definition file, edit the sp_trace create parameters to give the output trace file names and rollover parameters (http://technet.microsoft.com/en-us/library/ms190362.aspx ) and save the file.

 




/****************************************************/
/* Created by: SQL Server 2012  Profiler          */
/* Date: 14/11/2013  10:44:08         */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'D:\Vinoth\TestServer_SQLTrace', 20, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted


If you note at the bottom of the trace definition you may find the filters you have added in the GUI and a store procedure exec sp_trace_setstatus @TraceID, 1.

This procedure is used to start/stop and delete traces automatically (http://technet.microsoft.com/en-us/library/ms176034.aspx ).

 Now that we have set up the template, we can copy the trace definition create a SQL Agent Job. This Job will start the trace you have define on the server to the trace file as provided in the definition.

Use the below system table to find if the trace has started successfully,

select * from sys.traces

we have now set a Job to start the trace successfully we can schedule the job as per the requirement. Let us now look into how we can stop the trace in an automated way.I use the below TSQL to stop your server side trace using the sp_trace_setstatus store proc again with the stop parameter.

DECLARE @tcid int
 

if exists(select *  from sys.traces where path like '%<your Trace Definition base path>%')
BEGIN
select @tcid = id from sys.traces where path like '%<your Trace Definition base path>%'
exec sp_trace_setstatus @tcid, 0
exec sp_trace_setstatus @tcid, 2
END

 

You can set up this code in a SQL Agent Job again and Schedule it to run when you want to stop the trace thus making the whole process automated without manual intervention and running as server side trace takes much less resource to SQL Profiler.

Thursday 21 November 2013

Executing powershell script in a SQL Agent job - Host errors

Recently I created a SQL Agent job that’s runs a PowerShell script, I tested the PowerShell script in my PowerShell editor and the script was running fine, but when I ran the same script in SQL agent job it failed with the following error

The corresponding line is '$space.ForegroundColor = $host.ui.rawui.ForegroundColor'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "ForegroundColor": "Cannot convert null to type "System.ConsoleColor" due to invalid enumeration values. Specify one of the following enumeration values and try again. The possible enumeration values are "Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White"."

This error happens when you have a cls or clear-host command on your script as PowerShell run under SQL Agent job does not like any of the host commands. Once I cleared I received another error which was related to Host commands,

 A job step received an error at line 107 in a PowerShell script. The corresponding line is ' if($sleepcount -ne 2){write-host "Waiting for 10 Seconds...........";Start-Sleep 10}'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot invoke this function because the current host does not implement it.  '.  Process Exit Code -1.  The step failed.

Similar to our first error this was due to write-host commands in my script I had to comment all my write-host commands in my script to run the job successfully.