Monday, February 18, 2013

Querying Compressed Objects

If you use page or row compression, which is a feature new to SQL Server 2008, I've found it rather cumbersome to quickly identify which objects are being compressed.  Below is a quick query I put together to help me locate these objects - hopefully it helps you also!

SELECT  schema_ = SCHEMA_NAME(sys.objects.schema_id)
       ,table_ = OBJECT_NAME(i.object_id)
       ,index_ =
FROM    sys.partitions
        INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id
        INNER JOIN sys.indexes AS i ON sys.objects.object_id = i.object_id AND sys.partitions.INDEX_id = i.index_id
WHERE   data_compression > 0
        AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY schema_

Friday, February 15, 2013

SQL Server Object Modified! Who Did It?

Today I would like to share another useful way to take advantage of SQL Server's default trace, which by default is enabled on every installed instance of yours.  Let's say a developer recognizes that something broke in their application, and it would appear as though it was caused by the modification of a table.  But they don't know who did it, when it happened, nothing - they just know something has changed.  This actually happened to me this week, so it's very fresh in my mind!  While there are various third party tools and configurable items you can enable to track object creation/modification, etc., SQL Server's default trace is there for you by default and doesn't require you to do anything.

So, back to our example - what changed?  The first thing I would recommend is to query sys.trace_events and perhaps paste the results into Excel, remove the ones you likely won't use often and print it out as a quick  reference.  After all, time is of the essence when you are troubleshooting live issues.

Here is the query I used to create my quick reference:

SELECT  te.category_id
FROM    sys.trace_events te
        INNER JOIN sys.trace_categories tc ON te.category_id = tc.category_id
ORDER BY tc.[name]

So, in this particular case you want to focus on the following subset of events:

Next, you want to pull up a query that will go through all five default traces, filtering for the events you are interested in.  I recently wrote a post on how to query all five default traces at once, so I won't go into that here.

Here is the query I use (I know it's in the Test database, so I'll filter on that also):
DECLARE @filename NVARCHAR(4000);

-- Current default trace
SELECT  @filename = CAST(value AS NVARCHAR(4000))
FROM    ::
WHERE   traceid = 1
        AND property = 2;

SET @filename = LEFT(@filename,LEN(@filename) - CHARINDEX('\',REVERSE(@filename))) + '\log.trc'

-- Find auto growth events in the current trace file
SELECT  ftg.StartTime
       , AS EventName
       ,DB_NAME(ftg.databaseid) AS DatabaseName
       ,'Developer' AS NTUserName
FROM    ::
        FN_TRACE_GETTABLE(@filename,DEFAULT) AS ftg
        INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE   DB_NAME(ftg.databaseid) = 'Test'
        AND te.category_id = 5  -- Objects
ORDER BY ftg.StartTime DESC

And... what have we found?  A developer, using SSMS, has altered an object called DarnDeveloper in the Test database this morning at around 10am!
I hope this helps you with your forensic analysis and investigative work!

Friday, February 8, 2013

Estimated time to complete...?

Have you ever been restoring a database and wondered when it might complete?  Perhaps you wanted to give an update to others waiting on that restore?  The following query will not only give you the percent complete and estimated time to complete for backups and restores, but also ROLLBACKs, ALTER INDEX ... REORGANIZE, and many other DBCC commands.  Comes in handy - I'm actually in the middle of a restore right now!

SELECT  percent_complete AS 'PctComplete'
       ,start_time AS 'StartTime'
       ,command AS 'Command'
       , AS 'DatabaseName'
       ,DATEADD(ms, estimated_completion_time, GETDATE()) AS 'EstimatedEndTime'
       ,( estimated_completion_time / 1000 / 60 ) AS 'EstimatedMinutesToEnd'
FROM    sys.dm_exec_requests a
        INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE   estimated_completion_time > 0