May 27, 2010 at 9:03 am
How to check database size when it is offline in sql server 2005 ?
Can anyone help on the above.
May 27, 2010 at 9:08 am
Hi,
Try querying sys.master_files table in Master database.
Thank you
[font="Verdana"]Renuka__[/font]
May 27, 2010 at 11:44 am
Or even just look at the file size.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2010 at 1:10 pm
Get to the physical location the database files and check the properties for the file size.
Laveen Bondugula
SQL DBA
May 27, 2010 at 9:10 pm
Thanks for such a nice reply. BTW my requirement is to check the database size in Offline mode.
October 23, 2013 at 5:24 am
I realise this thread is old but it is still worth answering.
The below query will give you the space of offline databases.
SELECT
DB_NAME(db.database_id) as [DB Name],
CONVERT(DECIMAL(6,3),SUM((CONVERT(DECIMAL(20,5),mf.size)*8)/1048576)) as [DB Size in GB]
FROM
sys.databases db
LEFT JOIN sys.master_files mf
ON db.database_id = mf.database_id
WHERE
db.state_desc = 'OFFLINE'
GROUP BY
DB_NAME(db.database_id)
ORDER BY
[DB Name]
October 23, 2013 at 8:25 am
cavaliersa (10/23/2013)
I realise this thread is old but it is still worth answering.The below query will give you the space of offline databases.
SELECTDB_NAME(db.database_id) as [DB Name],
CONVERT(DECIMAL(6,3),SUM((CONVERT(DECIMAL(20,5),mf.size)*8)/1048576)) as [DB Size in GB]
FROM
sys.databases db
LEFT JOIN sys.master_files mf
ON db.database_id = mf.database_id
WHERE
db.state_desc = 'OFFLINE'
GROUP BY
DB_NAME(db.database_id)
ORDER BY
[DB Name]
it might not be worth ..if its is 3 years old thread. 😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply