dm_db_index_physical_stats Error if DB is Offline

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

dm_db_index_physical_stats Error if DB is Offline

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-12-11 : 02:54:09

[code]
SELECT *
FROM
(
SELECT *
FROM sys.databases AS db
WHERE db.name = DB_Name()
) AS db
JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') AS ps
ON db.database_id = ps.database_id
[/code]
gives me lots of rows from the current database and then:
[code]
Server: Msg 942, Level 14, State 4, Line 1
Database 'XXXXX_STAGING' cannot be opened because it is offline.
[/code]
Any ideas for how to work around this (preferably other than droppign the XXXXX_STAGING database)?

Thanks

SELECT @@version

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-11 : 08:06:16

Ummmm, you can't get index stats on a DB if it's not readable. You can schedule something to get snapshots of index stats at periodic intervals and query them instead, but there's no way to work around an unreadable database.

Kristen
Test

22859 Posts

Posted - 2010-12-11 : 08:50:43

But why is it trying to give me stats for anything other than db.database_id (that [the current DB] is online, and its the only one I want stats for ...)

I put that in a sub-query in case the JOIN was not restrictive enough.

Maybe I'm misunderstanding how this is supposed to work?

Sommerville
Starting Member

4 Posts

Posted - 2010-12-11 : 09:53:30

If you just want to suppress the error message - you may use try..catch blocks.

Begin try

SELECT *
FROM
(
SELECT db_id(db_name()) as database_id, DB_Name() as name
) AS db
JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') AS ps
ON db.database_id = ps.database_id where ps.database_id = db_id(db_name())

End try
Begin catch

End catch

regards,
Sommerville

Kristen
Test

22859 Posts

Posted - 2010-12-11 : 11:34:49

Thanks that does indeed catch the error. But I do need the values from the tables in the current database (so that I can rebuild the fragmented indexes)

This does not store anything in #TEMP:



DROP TABLE #TEMP
GO
BEGIN TRY
SELECT	*
INTO	#TEMP
FROM	sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') AS ps
WHERE	ps.database_id = db_id(db_name())
END TRY
BEGIN CATCH
PRINT '-- *********** An error was caught ***********'
END CATCH

SELECT * FROM #TEMP

Kristen
Test

22859 Posts

Posted - 2010-12-11 : 11:38:56

I have found a workaround which is to provide the Database ID in the first parameter - which will mean looping round the databases.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2010-12-11 : 13:37:00

Because you specify the db Id in the first param. That's the way it works. It always scopes to the instance and not a db.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!

SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-12-12 : 06:38:14

Yes, sorry, just "borrowed" some code from somewhere which broke when I put a database offline. In practice I want to defragment all indexes on all databases, so wanted all in a single query ... but I can loop round each one to get the dm_db_index_physical_stats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources