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.
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)
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)
All About Tech 