I have a stored procedure that selects the dbsize from sysfiles table for each database and then inserts it into the user table. One of the databases was taken offline and my sp fails. My question is how to check if the db is offline, so my sp won't fail?
Here is the select portion:
FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 1'
I queryied sysdatabase table and only four tables have status of 0, the other ones have statuses of 16, 8, 24. But there is only one db that is offline. Also, the database that is offline has a status of 528, but when I checked the BOL the offline status is 512. I am a little bit confused now, not sure what to do.
Yes but what version? You should not really be querying that in 2005.
Now - status in dbo.sysdatabases is a bitmask. Do you know how to read a bitmask in SQL Server?
EDIT - or use MCrowley's cool snippet. New to me. I didn't know the old one was offski.
NOTE from BoL:
If the database is not started, properties that the Microsoft SQL Server 2005 Database Engine retrieves by accessing the database directly instead of retrieving the value from metadata will return NULL. That is, if the database has AUTO_CLOSE set to ON, or the database is otherwise offline.