Script: finding the object_id from the page_id

I had a situation where there was a page split ( monitored through Extended Events) and the page_id was 46565645. I wanted to find the table so that I could work with the fill factor of the indexes.   First I turned the following trace ON     DBCC TRACEON (3604); DBCC PAGE (6, 1, 295, 0); DBCC TRACEOFF (3604); GO

 

PAGE: (1:295)   BUFFER:   BUF @0x00000004FD8C7980   bpage = 0x00000004A2D14000          bhash = 0x0000000000000000          bpageno = (1:295) bdbid = 6                           breferences = 0                     bcputicks = 0 bsampleCount = 0                    bUse1 = 55116                       bstat = 0x809 blog = 0x15ab215a                   bnext = 0x0000000000000000            PAGE HEADER:   Page @0x00000004A2D14000   m_pageId = (1:295)                  m_headerVersion = 17                m_type = 17 m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 84    m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594043432960 Metadata: PartitionId = 72057594039042048                                Metadata: IndexId = 0 Metadata: ObjectId = 245575913      m_prevPage = (0:0)                  m_nextPage = (0:0) pminlen = 8008                      m_slotCnt = 1                       m_freeCnt = 83 m_freeData = 8107                   m_reservedCnt = 0                   m_lsn = (35:200:9) m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0 m_tornBits = 1093512791             DB Frag ID = 1                        Allocation Status   GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                        DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED     In the above result Metadata: ObjectId = 245575913    gave me the object_id   I was able to find the table name with the page splits once I had the object_id .

Trace status

Following displays the current status of the trace

DBCC TRACESTATUS (1222, -1);
GO


DBCC TRACEON(1222,-1)
GO

--Turns ON the trace 1222 globally (not just for that session)


DBCC TRACEON(1222,-1)
GO

--turns OFF the trace 1222 globally (not just for that session)

Schedule a SQL profiler to run at a specific time

I needed to reserach some external backups on the database that ran in the middle of the night. To find out the source and the status. I decided to set up a trace in sql profiler that consisted teh processID, Loginame, NTusername etc .

Steps on SQL Server 2008 R2
---

• Connect to SSMS
• Go to Tools
• Go to SQL Profiler
• Chose the appropriate trace template according to requirements ( or create your own)
• Check enable trace stop time and specify the date and time that you want it to stop
• Click on the Run

If you run the follwing query you will see our trace

select * from ::fn_trace_getinfo(0)
Immediately stop the trace

Now we want to export the create trace script and add this script as  one of the step in the schedule job

Click on File----Select Export--Select Script Trace Definition

Select the entire script and add it as a step in the schedule job.

In the script - Search for key word in the script 'InsertFileNameHere' and replace with where to store the trace file.

exec @rc = sp_trace_create @TraceID output, 0, N'E:\profilertrace\trace1.trc', @maxfilesize, @Datetime

 

Save the Job.

That's all !!! Schedule the job to run at the specific time that you would like it to run.

SQL server side traces handy commands

select * from ::fn_trace_getinfo(0)    //list all the traces--where property = 5
--stops the tracet of raceid 2 EXEC sp_trace_setstatus 2 ,0

---restart the trace of traceid 2
EXEC sp_trace_setstatus 2 ,1
--deletes the trace of traceid 2 from the server EXEC sp_trace_setstatus 2 ,2

Using default trace to capture the event in the server

We had a situation whem the database was running fine since yesterday but itwas noticable slow today. So, first thing I suspected that a developer might have altered a stored procedure that ight have aused a delay ( bad query or a missing join ????)

In order to find out I took the following step.

--Find if the default race is enabled

SELECT * FROM sys.configurations WHERE configuration_id = 1568

--if it is not enabled we can enable it by running the following
sp_configure 'show advanced options' ,1;GO RECONFIGURE ;
GO
sp_configure 'default trace enabled', 1;
GO

RECONFIGURE;GO  --find the location of the default trace in the server
select * from :: fn_trace_getinfo(default)Go  --property 1 - value 2 means the trace is set for rollover when the file  has reached the maximum size.
--property 2 shows the location of the default trce file in the server
--property 3 shows the maximum size in MB of the file
--property 4 shows if there is a stop time. NULL means the trace is not set to stop
--property  5 shows if the trace is enabled, value 1 means yes it is.

Now to find all the event since yesterday

select e.name , t.starttime , t.DatabaseName , objectname , LoginName , t.HostName , t.ApplicationName , t.spid , t.eventclass
from fn_trace_gettable( 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_469.trc' , 5 ) as t
join sys.trace_events e
on e.trace_event_id = t.EventClass
where ObjectName is not null
and t.EventClass = 164 -- object altered
 and starttime > '08/30/2012'
order by t.StartTime

This pointed me to the right direction as I started to analyze the store procedures that was rolled out.