How to check database size when it is offline in sql server 2005

Question

Post reply

How to check database size when it is offline in sql server 2005

nikhil.verma

SSCrazy

Points: 2603
More actions
May 27, 2010 at 9:03 am

#141251

How to check database size when it is offline in sql server 2005 ?
Can anyone help on the above.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Renuka__ Hall of Fame Points: 3656 More actions · Answer 1

Hi,

Try querying sys.master_files table in Master database.

Thank you

[font="Verdana"]Renuka__[/font]

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 2

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

laveen.bondugula SSC Enthusiast Points: 169 More actions · Answer 3

Get to the physical location the database files and check the properties for the file size.

Laveen Bondugula

SQL DBA

nikhil.verma SSCrazy Points: 2603 More actions · Answer 4

Thanks for such a nice reply. BTW my requirement is to check the database size in Offline mode.

cavaliersa SSC Journeyman Points: 95 More actions · Answer 5

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]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

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.
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]

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;-)

How to check database size when it is offline in sql server 2005

Cookies on SQLServerCentral