How do you get the size of a database - sql server 2005
-
How do you get the size of a database - (sql server 2005) using either query analyzer or one of the admin tools? Thanks...
-
I figured it out... it's: sp_spaceused
-
Yep... also try sp_helpdb. Not as robust but will tell you the overall size. And thanks for posting even though you found your own answer.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
you can also get a lot more specific info using some of the standard reports available in 2005's right-click functionality (appeared in Sp1 or 2?). Things like physical space vs free space, space used by table (with index usage, etc...).
Worth a perusal if you have something in mind. Also tends to be a lot kinder on the eyes if you need to show it around.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I put these queries together some time ago. Feel free to use them as you wish.
-- SQL Server 2000 only.
--
-- Author: Damon T. Wilson
-- Creation Date: 13-DEC-2006
--
-- Usage:
-- Display the Database ID, Database Name, Logical File Name,
-- MB Size on Disk, GB Size on Disk and Physical File Name
-- for all databases in this instance.
use master;
go
select
db.[dbid] as 'DB ID'
,db.[name] as 'Database Name'
,af.[name] as 'Logical Name'
--,af. as 'File Size (in 8-kilobyte (KB) pages)'
,(((CAST(af. as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
,((((CAST(af. as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
,af.[filename] as 'Physical Name'
from sysdatabases db
inner join sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1,2);
-- SQL Server 2005 only.
--
-- Author: Damon T. Wilson
-- Creation Date: 13-DEC-2006
--
-- Usage:
-- Display the Database ID, Database Name, Logical File Name,
-- MB Size on Disk, GB Size on Disk and Physical File Name
-- for all databases in this instance.
use master;
go
select
db.[dbid] as 'DB ID'
,db.[name] as 'Database Name'
,af.[name] as 'Logical Name'
--,af. as 'File Size (in 8-kilobyte (KB) pages)'
,(((CAST(af. as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
,((((CAST(af. as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
,af.[filename] as 'Physical Name'
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1,2);
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP -
My favorite way to view the breakdown of the used / total space in all the files of a database is with this HTML Application. It connects to a server (you must have sysadmin rights or similar) retrieves a list of all the databases, then loops through each one and figures out the used / total space for every file and displays it.
-
Heh... that chart shows a high potential for performance problems... 2Mb temp table. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (6/11/2009)
Heh... that chart shows a high potential for performance problems... 2Mb temp table. 😉[font="Verdana"]Agreed. 2mb is far too large. You shouldn't ever need a temp database larger than 16kb. :hehe:[/font]
-
Yeah -- of course that's just my local express instance for testing... clearly tempdb is just *slightly* too small. hehe 😀
-
Just as Jeff had said sp_helpdb is what I would have used.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply