Recording Results from sp_spaceused

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

Transact-SQL (2000)

Recording Results from sp_spaceused

Author

Topic

KatrinaBlue
Starting Member

3 Posts

Posted - 2008-06-03 : 18:04:58

I would like to be able to historically track the space utilization of my production database to allow me to monitor data file utilization.

Our executive management wants to be able to track this statistic (sigh!!) and I also need to be able to manage file growth so that it never occurs during a period of peak demand.

Can anyone comment on the easist way to capture the results of sp_spacedused so that I can store them in a historical table?

I dont think I can do this from T-SQL, but maybe the native client would allow me to capture the info from a .Net program?

My version of the command that I had planned on using is:

EXEC sp_spaceused @updateusage = N'TRUE';

Katrina

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-03 : 18:22:15

Create table with columns based on result of sp_spaceused, then run following:

insert into tabel execute sp_spaceused

KatrinaBlue
Starting Member

3 Posts

Posted - 2008-06-04 : 11:31:27

Thx for the help, but this stored procedure returns 2 result sets. The first had 3 columns and the second has 4. Any ideas on how to get around that?

Katrina

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-06-04 : 17:05:34

I use something like the following. It inserts into a table, and I have a query that compares the two most recent runs to determine growth. I run it weekly, it all depends on your needs. It only does non-system databases (">4") and is written for SQL2K. It tracks space used of all the data and log files for each DB on the server and the free space remaining in that database. HTH.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 17:17:55

I just use sp_databases. You can't DBCC UPDATEUSAGE from it like you can sp_spaceused, but at least you don't have to loop and it only provides one result set back.

I collect the data on all of my servers into one central server. Here is what I use on the central server:


SET NOCOUNT ON

DECLARE @serverId int, @serverName sysname, @max int

SET @serverId = 1

SELECT IDENTITY(int, 1, 1) AS ServerId, ServerName
INTO #Server
FROM Server

SELECT @max = MAX(ServerId)
FROM #Server

WHILE @serverId <= @max
BEGIN
	SELECT @serverId = ServerId, @serverName = ServerName
	FROM #Server
	WHERE ServerId = @serverId

	EXEC dbo.isp_DatabaseGrowth @serverName

	SET @serverId = @serverId + 1
END

DROP TABLE #Server

Where Server is a table with a list of my instances and isp_DatabaseGrowth is a CLR stored procedure that I wrote that grabs the results from sp_databases and puts them into a table.

I'll have to get around to posting the CLR object some day.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources