Category Archives: Admin

Symbols

Posted on by
Reply

I just want to write the setup I went through for using debug symbols within SQL Server to resolve call stacks. At a high level this is what you need to do – Assuming that you are using 64 BIT software.

  • Download the debugger kit – I downloaded Windows Kit 8.1 http://www.microsoft.com/en-us/download/details.aspx?id=42273.This will get installed @ C:\Program Files\Windows Kits\8.1\Debuggers\
  • Using CMD you will then need to CD to the Microsoft Binn location – this will depend on the version you are using – for example SQL 2008 R2 =CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
  • Then you will need to issue this command:”C:\Program Files\Windows Kits\8.1\Debuggers\x64\symchk” sqlservr.exe /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols
  • This will then pull down a sqlservr.pdb file (to your C drive) which then you should move to the SQL Server Binn location.
  • Repeat this but replace the sqlservr..exe with *dll hence:“C:\Program Files\Windows Kits\8.1\Debuggers\x64\symchk” *.dll /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols

 

That’s it! You will then be able to resolve call stacks.

You may have issues if you cut and paste from this article – usually it is the following  “ “ which you may need to enter manually.

A Handy function

Posted on by
Reply

If you like learning about internals use the undocumented function to get the FileID:PageID of your table rows and use that in DBCC PAGE.


SELECT sys.fn_PhysLocFormatter (%%physloc%%)
AS RID, *
FROM Person.EmailAddress;

Output:

RID
(1:3392:0)
(1:3392:1)
(1:3392:2)

Now set your trace flag on and read the contents of DBCC PAGE to satisfy your curiosity.

Enjoy.

SQL Server Internals

Posted on by
Reply

If you like learning about internals use the undocumented function to get the FileID:PageID of your table rows and use that in DBCC PAGE.


SELECT sys.fn_PhysLocFormatter (%%physloc%%)
AS RID, *
FROM Person.EmailAddress;

Output:

RID
(1:3392:0)
(1:3392:1)
(1:3392:2)

Now set your trace flag on and read the contents of DBCC PAGE to satisfy your curiosity.

Enjoy.

Timed SQL Server tracing

Posted on by
Reply

Have you ever wanted to start a SQL trace (server-side trace!) from SQL agent and let it stop after X minutes and write the TRC to a location? Well this is how you would do it:

 ALTER PROCEDURE [dbo].[ScriptTrace] @RunTime INT,

 @FileName NVARCHAR(100) 

AS 

SET NOCOUNT ON 

DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT -- Specify Max File Size
DECLARE @Now DATETIME
DECLARE @StopTime DATETIME
DECLARE @FileStamp NVARCHAR(25)
DECLARE @COMMAND NVARCHAR(125) 

SET @Now = GETDATE() 

SET @StopTime = DATEADD (Mi , @RunTime , @Now ) 

SET @FileName = @FileName+'_' SET @FileStamp = CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR) + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR) + CAST(DATEPART(HOUR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MINUTE, GETDATE()) AS NVARCHAR) 

SET @FileName = @FileName + @FileStamp SET @maxfilesize = 18000 

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0,
@FQFileName, @maxfilesize, @StopTime 

IF (@rc != 0) GOTO error 

SET @COMMAND = 'SQL Trace Log @' + @FQFileName + ' activated for ' + CAST(@RunTime AS VARCHAR) + ' minute(s)' PRINT @COMMAND 

--Set the events for example

DECLARE @on BIT SET @on = 1 

EXEC sp_trace_setevent @TraceID, 14, 1, @on
EXEC sp_trace_setevent @TraceID, 14, 9, @on
EXEC sp_trace_setevent @TraceID, 14, 6, @on
EXEC sp_trace_setevent @TraceID, 14, 10, @on
EXEC sp_trace_setevent @TraceID, 14, 14, @on
EXEC sp_trace_setevent @TraceID, 14, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 10, @on
EXEC sp_trace_setevent @TraceID, 15, 14, @on
EXEC sp_trace_setevent @TraceID, 15, 18, @on
EXEC sp_trace_setevent @TraceID, 15, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 12, @on 

--Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT 

-- filter 1 include databaseId 5
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 5 

-- filter 2
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @TraceID, 1 

SELECT TraceID=@TraceID
GOTO finish ERROR:
SELECT ErrorCode=@rc FINISH:

--Now call it
EXEC dbo.ScriptTrace '1','G:\Logs\Trace'

output example: SQL Trace Log @G:\Logs\Trace_20153101419 activated for 1 minute(s)

SQL Server tracing

Posted on by
Reply

Have you ever wanted to start a SQL trace (server-side trace!) from SQL agent and let it stop after X minutes and write the TRC to a location? Well this is how you would do it:

 ALTER PROCEDURE [dbo].[ScriptTrace] @RunTime INT,

 @FileName NVARCHAR(100) 

AS 

SET NOCOUNT ON 

DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT -- Specify Max File Size
DECLARE @Now DATETIME
DECLARE @StopTime DATETIME
DECLARE @FileStamp NVARCHAR(25)
DECLARE @COMMAND NVARCHAR(125) 

SET @Now = GETDATE() 

SET @StopTime = DATEADD (Mi , @RunTime , @Now ) 

SET @FileName = @FileName+'_' SET @FileStamp = CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR) + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR) + CAST(DATEPART(HOUR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MINUTE, GETDATE()) AS NVARCHAR) 

SET @FileName = @FileName + @FileStamp SET @maxfilesize = 18000 

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0,
@FQFileName, @maxfilesize, @StopTime 

IF (@rc != 0) GOTO error 

SET @COMMAND = 'SQL Trace Log @' + @FQFileName + ' activated for ' + CAST(@RunTime AS VARCHAR) + ' minute(s)' PRINT @COMMAND 

--Set the events for example

DECLARE @on BIT SET @on = 1 

EXEC sp_trace_setevent @TraceID, 14, 1, @on
EXEC sp_trace_setevent @TraceID, 14, 9, @on
EXEC sp_trace_setevent @TraceID, 14, 6, @on
EXEC sp_trace_setevent @TraceID, 14, 10, @on
EXEC sp_trace_setevent @TraceID, 14, 14, @on
EXEC sp_trace_setevent @TraceID, 14, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 10, @on
EXEC sp_trace_setevent @TraceID, 15, 14, @on
EXEC sp_trace_setevent @TraceID, 15, 18, @on
EXEC sp_trace_setevent @TraceID, 15, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 12, @on 

--Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT 

-- filter 1 include databaseId 5
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 5 

-- filter 2
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @TraceID, 1 

SELECT TraceID=@TraceID
GOTO finish ERROR:
SELECT ErrorCode=@rc FINISH:

--Now call it
EXEC dbo.ScriptTrace '1','G:LogsTrace'

output example: SQL Trace Log @G:LogsTrace_20153101419 activated for 1 minute(s)