Determining the size of SQL 2005 database 1

[vba317]

Hello,
I am trying to backup my database for the first time. Before I can do this I need to know how big my database is. I figured out by using the following code:

USE ARC_Rpts
GO
EXEC sp_spaceused
GO

This code works when trying to determine the size of individual databases in this case ARC_rpts.
How do I determine the whole database size? Any help is appreciated.


Tom

 

[SQLBill]

What are you really trying to figure out?

You say that code give you the size of the individual database. Then you ask 'how do I determine the whole database size?'

EXEC sp_spaceused does give you the whole database size.

Are you looking for the sum of ALL your databases? If so, you could create a loop that runs sp_spaceused on all your databases, saves it to a temp table, and the returns the sum of the values.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[vba317]

Yes, I am looking to loop through all my databases I have a total of 12 how would I do that?
I am doing this as a first step because currently the database is not being backed up and I want to start backing it up.
The first question I am asked is how big is it and I don't have an answer to that question.

ARC_rpts
Portland_Data
PwrPivotSrc
rptdata_ahs
rptdata_monthly
rptdata_other
rptdata_process
rptdata_vm
rptdata_xl
rptdata_xl1
tss
VM_data


Tom

 

[gmmastros]

This should list all of the database files and their sizes:

Select DB_Name(database_id), 
       Type_desc, 
       Size 
from   sys.master_files 
Order By DB_Name(database_id)

To get the combined sizes:

Select Sum(Size) From sys.master_files

Please note that the sizes list are in kilobytes.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

Where do I fine my database name?

Tom

 

[vba317]

Forget the previous question
What are rows and log and do I combine them?

Tom

 

[gmmastros]

You do combine them. There are at least 2 files associated with every sql server database (there can be more). You need to backup the data file (the one labeled ROWS) as well as the log file.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Using George's code, this 'should' give you the total (ROWS + LOG) size of every database.

Select DB_Name(database_id), 
       SUM(Size) 
from   sys.master_files 
GROUP By DB_Name(database_id)

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875