Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

deadlocks

Posted on 2011-04-27
4
Medium Priority
?
619 Views
Last Modified: 2012-05-11
Finding: A trace is not running to capture deadlock information
Recommendation ID: SDR-DL1
Impact: LOW
Category: DEADLOCKS
Rank: 13

Not running a trace capturing deadlock events prevents you from knowing whether or not deadlocks are occurring and the underlying cause.

Recommendation:
It is recommended that you run a SQL Server trace in order to monitor for deadlocks.
 
Learn more about: http://msdn.microsoft.com/en-us/library/ms178104(v=SQL.90).aspx




what is created by running the code snippet
and where is this created

does this increase or decrease cpu and ram usage in sql server

-- *******************************************************
-- This SQL doctor optimization script was created 
-- based on the recommendations you selected from 
-- the following analysis: 
-- 
-- SQLdoctor version: 2.0.144.0
-- Date: Wednesday, April 27, 2011
-- Time: 12:00:26 PM
-- SQL Server Instance: purchase
-- Analysis Type: General Health Check, Workload Analysis, Database Object Analysis
-- *******************************************************


--  A trace is not running to capture deadlock information
USE master
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
GO 

if (object_id('sp_CreateSQLdoctorDeadlockTrace') is not null)
begin
	drop procedure [dbo].[sp_CreateSQLdoctorDeadlockTrace]
end
GO

create procedure [dbo].[sp_CreateSQLdoctorDeadlockTrace]
as
begin
	declare @traceid int
	declare @traceopts int
	declare @tracemax bigint
	declare @tracefile nvarchar(512)
	declare @tracestop datetime
	declare @on bit
	declare @rc int

	set @on = 1
	set @traceopts = 0
	set @tracemax = 5
	
	select @traceid = traceid 
		from sys.fn_trace_getinfo(0) 
		where property = 2 and convert(nvarchar(1024),value) like '%\SQLdrDeadlocks_%.trc'  
	
	IF (@traceid is not null)	
	begin
		exec sp_trace_setstatus @traceid, 0 -- stop the trace
		exec sp_trace_setstatus @traceid, 2 -- close the trace
		waitfor delay '00:00:01'
		set @traceid = null
	END
	
	-- place tracefile in qtemp log directory
	select @tracefile = left(physical_name, len(physical_name) - charindex('\',reverse(physical_name))) 
		from sys.master_files where database_id = 2 and file_id = 1 
	set @tracefile = @tracefile + '\SQLdrDeadlocks_0' -- trace file name

	BEGIN TRY
		exec @rc = sp_trace_create @traceid=@traceid output, 
							 @options=@traceopts, 
							 @tracefile=@tracefile, 
							 @maxfilesize=@tracemax,
							 @stoptime=@tracestop 
	END TRY
	BEGIN CATCH
		declare @cmd sysname
		declare @curconfig int 

		print 'Please ignore the preceeding error.  We will delete the trace file and try to configure the trace again.'

		-- assume that an old file exists - delete it and try again
		select @curconfig = cast(value_in_use as int)
			from sys.configurations 
			where configuration_id = 16390 -- command shell 
		if (@curconfig = 1)
		BEGIN
			SET @cmd = 'del "' + @tracefile + '.trc"'
			EXEC master.dbo.xp_cmdshell @cmd
		END
		ELSE	
		BEGIN		
			-----------------------------------------------------------------------------
			-- without cmd shell being enabled we will try to use ole automation.
			--
			DECLARE @hr int
			DECLARE @FSO_Token int
			DECLARE @showAdvOpts bigint;
			DECLARE @oleAuto bigint;
			select @showAdvOpts = cast(value_in_use as bigint) from sys.configurations where configuration_id = 518; -- show advanced options
			select @oleAuto = cast(value_in_use as bigint) from sys.configurations where configuration_id = 16388; -- Ole Automation Procedures
			if 0 = @showAdvOpts
			begin
				exec sp_configure 'show advanced options', 1;
				reconfigure with override;
			end;
			if 0 = @oleAuto
			begin
				exec sp_configure 'Ole Automation Procedures', 1;
				reconfigure with override;
			end;
			-----------------------------------------------------------------------------
			-- Ensure that the stored procedure exists or we will not be able to call it
			--
			if (object_id('sp_OACreate') is null)
			begin
			   RAISERROR('The sp_OACreate stored procedure was not found on the server', 16, 1)
			   return
			end

			-----------------------------------------------------------------------------
			-- Ensure that the stored procedure exists or we will not be able to call it
			--
			if (object_id('sp_OAMethod') is null)
			begin
			   RAISERROR('The sp_OAMethod stored procedure was not found on the server', 16, 1)
			   return
			end

			-----------------------------------------------------------------------------
			-- Ensure that we can get access the object for locating the service.
			--
			exec @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token output
			if @hr <> 0
			BEGIN
			   RAISERROR('Failed to use OLE for FileSystem access when cleaning up trace file', 16, 1)
			   return
			END
			SET @cmd = @tracefile + '.trc'
			exec @hr = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, @cmd
			exec @hr = sp_OADestroy @FSO_Token
			
			if 0 = @showAdvOpts
			begin
				exec sp_configure 'show advanced options', 0;
				reconfigure with override;
			end;
			if 0 = @oleAuto
			begin
				exec sp_configure 'Ole Automation Procedures', 0;
				reconfigure with override;
			end;
		END

		exec sp_trace_create @traceid=@traceid output, 
						 @options=@traceopts, 
						 @tracefile=@tracefile, 
						 @maxfilesize=@tracemax,
						 @stoptime=@tracestop 
	END CATCH

	if (@traceid >0)
	begin
		exec sp_trace_setevent @traceid, 148,  1, @on	-- TextData
		exec sp_trace_setevent @traceid, 148, 12, @on	-- SPID
		exec sp_trace_setevent @traceid, 148, 14, @on	-- StartTime
		exec sp_trace_setevent @traceid, 148, 27, @on	-- EventClass
		exec sp_trace_setevent @traceid, 148, 51, @on	-- EventSequence
		exec sp_trace_setstatus @traceid, 1 -- start the trace
	end
end
GO
	
-- set it to run at sql server start-up
exec sp_procoption N'sp_CreateSQLdoctorDeadlockTrace', 'startup', 'on'

-- go ahead and start the trace now
exec sp_CreateSQLdoctorDeadlockTrace

Open in new window

0
Comment
Question by:rgb192
  • 2
4 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 35479069
What is your problem? Explain it.
0
 
LVL 1

Author Comment

by:rgb192
ID: 35479744
i am using 'sql doctor' to scan and find problems
and it may have solved a problem but I am not sure
0
 
LVL 8

Accepted Solution

by:
Kobe_Lenjou earned 2000 total points
ID: 35696293
The script you provided does the following:
- create a stored proc 'sp_CreateSQLdoctorDeadlockTrace' in the master database
- configures this proc to start on startup of the SQL engine
- runs the proc

This in turn does:
- create a sever side trace that will write it's info to a text file

This will slightly increase the CPU & RAM usage of the engine, but this will be extremely minimal, since you won't be experiencing a lot of deadlocks.
0
 
LVL 1

Author Closing Comment

by:rgb192
ID: 35700581
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question