Re: Query failures
Hi,
Thanks for your reply.
I think the problem with the database being offline is easy enough to fix. In essence an extra join is required to sys.databases, and a check for a state_desc of ONLINE. Some sample code for this is given below.
That said, I cannot reproduce this OFFLINE problem. If I take a database offline (on both a 2008 and 2005 SQL Server), and run the below code without the join to sys.databases, it still works... have I missed something?
I was hoping I could use a similar approach for checking the compatibility level (which is also recorded in sys.databases). However, it still fails. Let me investigate this a bit further...
I will make a note about this problem in the document.
Thanks for discovering this.
Ian
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(avg_fragmentation_in_percent,2) AS [Fragmentation %]
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempFragmentation
SELECT TOP 20
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
INNER JOIN sys.databases d ON d.database_id = DB_ID()
AND state_desc = ''ONLINE''
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC'
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
DROP TABLE #TempFragmentation Message was edited by:
ian.stirk
|