Can allocated/actual database space be checked using a script

Question

Post reply

Can allocated/actual database space be checked using a script

maria_gallagher

SSC Rookie

Points: 38
More actions
November 26, 2009 at 11:54 pm

#228182

Hi,
In SQL Server 2000, you can right click on a database and select view - TaskPad, which will give you the Space Allocated information: allocated data size for data and log files and used space for data and log files.
Is there a way to check this using a script? How to check all databases for those values?
Will it work in SQL Server 2005?
Thanks heaps in advance!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Nick_UK SSCommitted Points: 1909 More actions · Answer 1

does the following do the job for you, this would work in 2005 but I'm not sure if it would in 2000 or not.

USE DatabaseName

GO

EXEC sp_helpfile

GO

books online link to sp_helpfile

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 2

Nick_UK (11/27/2009)
does the following do the job for you, this would work in 2005 but I'm not sure if it would in 2000 or not.
USE DatabaseName
GO
EXEC sp_helpfile
GO
books online link to sp_helpfile

I guess it does not solve the OP's problem.

Use this

select * from Master.dbo.sysperfinfo where [object_name] = 'SQLServer:Databases'

and (counter_name = 'Log File(s) Size (KB)' or counter_name = 'Log File(s) Used Size (KB)')

It will display the details for log file.

I am in a rush for a meeting, will reply with the code for Data files.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

This script gets the file information for every database on a server, inserts it into temp table, and queries it multiple ways to give various levels of analysis of file space usage.

Get Server Database File Information

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

maria_gallagher SSC Rookie Points: 38 More actions · Answer 4

Hi, thank you for replying. I will try those scripts and get back to you. 🙂

maria_gallagher SSC Rookie Points: 38 More actions · Answer 5

Hi Michael,

The script worked great. It's very detailed!

Is there a way to stop rounding of the File_size_used and file_size_unused values? Currently it may display 7606, while in Taskpad view it's actually 7606.6, and 1139 instead of 1182.88?

thanks again!:cool:

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 6

Yes, there is way to stop rounding of the values,

In the code try to remove the Round () function. I do not have SQL system to test on.

Also it is "Michael Valentine Jones " Not SSC Committed, it is a Level Name give to every user based on their points. 🙂

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

maria_gallagher SSC Rookie Points: 38 More actions · Answer 7

LOL. 😀

Sorry, I'm a newbie here as you can tell. I"ll give that a go. Thanks.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

maria_gallagher (11/29/2009)
Hi Michael,
The script worked great. It's very detailed!
Is there a way to stop rounding of the File_size_used and file_size_unused values? Currently it may display 7606, while in Taskpad view it's actually 7606.6, and 1139 instead of 1182.88?
thanks again!:cool:

As suggested by Bru Medishetty, you can modify the script to any level of rounding that you want. However, it seems like a waste of time to me to worry about amounts less than 1 MB.