DB Size for a specific date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DB Size for a specific date

Author

Topic

jshurak
Starting Member

44 Posts

Posted - 2007-01-03 : 10:55:16

I'm trying to get sizes for our databases for november. Is there a way to do this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 11:51:25

Unless you took efforts to keep track of it, SQL Server doesn't help much in this regard.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-03 : 11:56:16

Restore a copy of the database to a new database from one of your November tape backups and look at the size of the database.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-01-03 : 13:49:00

Dunno if it helps, but


SELECT TOP 100 backup_start_date, backup_size, database_name
FROM	msdb.dbo.backupset
WHERE	database = 'MyDatabase'
ORDER BY backup_start_date DESC

will tell you something about the backup sizes - not quite the same as the database size, but ...

Kristen

jshurak
Starting Member

44 Posts

Posted - 2007-01-04 : 08:47:58

Thanks for your responses everyone. Michael - that would work, except we have atleast 50 dbs. Kristen - Do you know of an equation or calculation that would convert the bak size to db size?

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 08:49:46

"Do you know of an equation or calculation that would convert the bak size to db size"

Nope. You can have a 50GB database with only 100MB of data in it - or you could have a 100MB database with 100MB of data in it.

Kristen

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-04-23 : 09:55:55

What route have you guys chosen to keep track of it? Log the size to a stat table every night? Get that data from a system table somewhere like:
use myDB
select * from sys.database_files

?

It doesn't seem to work. A file on the disk has a size of 25 gig (25,139,008 KB). But this table has 3,142,376 in the size column. What am I missing?
---------------------------------------------
And sp_spaceused returns 33 366.06 MB...
---------------------------------------------
And this approach:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058
is just over half a gig lower at 24550
---------------------------------------------
And this approach:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68404
comes back with 32.583984 gig for the data and log file when the disk says 34.1 gig
---------------------------------------------

Why is nothing matching up to the size of the file on the disk?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-23 : 22:30:56

You can get table size with 'sp_spaceused tab_name'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources