INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Determining the size of SQL 2005 database

Determining the size of SQL 2005 database

(OP)
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:

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

RE: Determining the size of SQL 2005 database

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: What should I know before I post?

RE: Determining the size of SQL 2005 database

(OP)
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

RE: Determining the size of SQL 2005 database

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

CODE

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

To get the combined sizes:

CODE

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

RE: Determining the size of SQL 2005 database

(OP)
Where do I fine my database name?

Tom

RE: Determining the size of SQL 2005 database

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

Tom

RE: Determining the size of SQL 2005 database

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

RE: Determining the size of SQL 2005 database

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

CODE

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: What should I know before I post?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close