October 18, 2009 at 8:48 pm
Hi,
I have some code that rebuilds indexes conditionally and uses sys.dm_db_index_physical_stats to determine fragmentation. When any one database is offline, the code breaks.
I have the logic to exclude the off-line databases from my database list to defrag, but that doesn't help. As soon as sys.dm_db_index_physical_stats hits the offline db, code stops.
Is that a known issue? I am also having issues to try...catch it.
INSERT INTO ##Temp
(
DatabaseId
, DatabaseName
, StatsStatus
)
SELECT d.database_id
, d.[name]
, 0 -- statistics not yet updated
FROM sys.databases d
WHERE [name] Not In ('tempdb','pubs','Northwind','master','model', 'msdb')
AND [state] = 0 -- ON-LINE
October 19, 2009 at 7:51 am
I think you need to share your code later in the script. All you have shown here is getting the database list. If you call the inline function with a specific database_id you should not get an error. If you call with NULL for the database_id you do get an error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2009 at 8:11 am
not a problem, including the code here:
Note, the procedure is table driven, so there's an additional file to create tables. As far as I can tell the following section breaks when there's a database that is off-line. Any suggestions are appreciated.
INSERT INTO #IndexDefragList
(
DatabaseId
, DatabaseName
, ObjectId
, IndexId
, PartitionNumber
, Fragmentation
, [PageCount]
, DefragStatus
, SchemaName
, ObjectName
, IndexName
)
SELECT
ps.database_id
, QUOTENAME(DB_NAME(ps.database_id))
, ps.[object_id]
, ps.index_id
, ps.partition_number
, ps.avg_fragmentation_in_percent
, ps.page_count
, 0 -- 0 = unprocessed, 1 = processed
, Null
, Null
, Null
FROM sys.dm_db_index_physical_stats (Null, Null, Null , Null, @ScanMode) AS ps
INNER JOIN ##DatabaseList dl ON dl.DatabaseId = ps.database_id
WHERE
ps.index_id > 0 -- ignore heaps
AND ps.page_count > 8 -- ignore objects with 1 extent
AND ps.index_level = 0 -- leaf-level nodes only, supports @ScanMode
AND avg_fragmentation_in_percent >= @MinFragmentation
October 19, 2009 at 8:30 am
Thanks for posting the code. I wouldn't call what is happening a BUG per se, but I would call it a poor design decision. Because you are calling the function with NULL as the database_id parameter it is trying to enumerate the indexes for ALL databases BEFORE you are doing the INNER JOIN on your database list. The DMV fails when a DB is offline. Unfortunately you cannot use CROSS APPLY with this DMF so in order to get it to work the way you would like you will have to use a CURSOR/LOOP and call the DMF with the database_id parameter. Not ideal, but doable.
I think I'll put in a CONNECT item on this one, because you should be able to do something like this:
SELECT
d.database_id,
d.[name],
0 -- statistics not yet updated
FROM
sys.databases d CROSS APPLY
sys.dm_db_index_physical_stats(D.database_id, NULL, NULL, NULL, NULL) AS DDIPS
WHERE
D.[name] Not In ('tempdb', 'pubs', 'Northwind', 'master', 'model', 'msdb') AND
D.[state] = 0 -- ON-LINE
But you can't so you have to loop through your databases to get the info.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2009 at 1:22 pm
Jack, thanks for your reply.
From what I understand the problem is really that you can not do CROSS APPLY on dm_db_index_physical_stats function. Would it help if I also send in a CONNECT report? I know MSFT are more likely to fix stuff when multiple people report issues.
Thanks again
October 19, 2009 at 2:24 pm
I have created a Connect Item and all you need to do is vote it up, verify it, and comment on it as desired.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply