producing a space report - 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 2005 Forums

SQL Server Administration (2005)

producing a space report

Author

Topic

wardsan
Starting Member

48 Posts

Posted - 2009-05-15 : 11:24:37

Hi, I have asked to produce a space report for a selection of databases on a sql server. I basically need the allocated space for data, the used space and also, how much space is left on the drive where the datafile/s is/are. Datafiles for a database are always on the drive. Can anyone help? I am going around in circles.
TIA

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-15 : 12:37:59

sp_spaceused @updateusage = 'TRUE' should work for you.

Jim

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-15 : 14:02:04

This will produce a result set of all databases on a server:

CREATE PROCEDURE usp_DB_SpacedUsed AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
-- Declare local variables
EXEC master..sp_MSForeachdb '
USE

IF DB_ID(''?'')>4
BEGIN

insert into DBMaint..DBSizeUsed
SELECT name AS [File], filename as File_Name
, CAST(size/128.0 as DECIMAL(10,2)) AS Size_in_MB
, CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 as DECIMAL(10,2)) as Space_Used
, CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS Available_Space
, getdate() as RunDate
FROM SYSFILES

END'

GO

Terry

-- Procrastinate now!

wardsan
Starting Member

48 Posts

Posted - 2009-05-18 : 03:43:39

Terry,
thanks for that. Where is that table created? I can't find it.

wardsan
Starting Member

48 Posts

Posted - 2009-05-18 : 04:20:28

Ignore previous remark Terry, do you have a a table definition ?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-18 : 08:20:27

I have a database called DBMaint but you can put this where ever is appropriate.

CREATE TABLE [dbo].[DBSizeUsed] (
[File] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[File_Name] [nchar] (260) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Size_in_MB] [decimal](10, 2) NULL ,
[Space_Used] [decimal](10, 2) NULL ,
[Available_Space] [decimal](10, 2) NULL ,
[RunDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

I run this weekly. Nice little metric to have for projecting growth.

Terry

-- Procrastinate now!

wardsan
Starting Member

48 Posts

Posted - 2009-05-18 : 08:28:15

Thanks Terry appreciate it. Also, thanks to Jim

wardsan
Starting Member

48 Posts

Posted - 2009-05-18 : 09:14:52

Terry, one last thing. Ideally I would like to email the rows from that table or just write to a file. I used osql for the file version but the formatting is all over the place with the column sizes so wide. Would you have any advice particularly on the mailing option?
Thanks
Sandra

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-18 : 09:27:50

If you're only using SQL to report, you may try putting something together in reporting services. If you have Crystal (or some other 3rd party reporting app), that may work as well. DTS/SSIS can produce a nice little comma-delimited file that can be emailed and opened in Excel for formatting purposes.

Terry

-- Procrastinate now!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-18 : 10:33:11

Take a look at the script on the link below.

Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058
"This script gets the file information for every database on a server, and inserts it into temp table #DB_INFO. #DB_INFO is queried multiple ways to give various levels of analysis of file space usage.

This script was tested on SQL Server 7.0, 2000, and 2005."

CODO ERGO SUM

dz
Starting Member

3 Posts

Posted - 2009-06-19 : 20:03:17

My favorite way to retrieve a breakdown of available space in databases and on the server is with an HTML Application. I use this HTA constantly throughout the day, and I also have the same script modified slightly to run as a daily scheduled task. It connects to all my servers, gets the space breakdown, and logs it to a management database so that I can view the space change over time for all servers.

If you're not familiar with HTML Applications, they are essentially web pages renamed to .hta instead of .html. The scripting language I use in them is vbscript, although you can use other scripting languages like javascript too. HTAs like these are very easy to convert to .asp web sites as well, which is convenient. For this case you can easily combine the two scripts into a single vbscript or HTA. I hope you find them as useful as I do.

[url]http://dougzuck.com/hta[/url]

http://dougzuck.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources