SQLServerCentral / Discuss Content Posted by David Poole / Article Discussions / Article Discussions by Author / Automated Monitoring Database Size Using sp_spaceused / Latest Posts

RE: Automated Monitoring Database Size Using sp_spaceused

Mat Culpepper — Tue, 08 May 2012 08:26:47 GMT

The line:MIN(row_count) AS Rows,Returns incorrect results for partitioned tables.It shoudl read:SUM(row_count) as Rows,to correctly sum the rowcount across all partitions.As for the exclusion of XML data, does the subquery (LOBDATA) against sys.dm_partition_stats joined to sys.internal_tables not return this metric?Just curious...

RE: Automated Monitoring Database Size Using sp_spaceused

David.Poole — Sun, 14 Aug 2011 09:05:44 GMT

The problem seems to be that the underlying objects for the query are system views.If you take an exact copy of sp_spaceused and create your own copy in the master database then it simply doesn't work outside of the context of the master database even though your code is identical!It seems that the original sp_spaceused, being on the sys schema treats other sys views as if they were local where as the bespoke sp_spaceused being on the dbo schema treats them as if they reside in the master database.Perhaps the best way of dealing with this issue is to capture the SQL thrown at the DB by the built in report "Disk Used By Top Tables" and put it in the MODEL database and all user databases.

RE: Automated Monitoring Database Size Using sp_spaceused

David.Poole — Fri, 12 Aug 2011 15:14:32 GMT

the original article was written using SQL2005 but there appears to be subtle differences in SQL2008.I'll have a look into it ASAP.

RE: Automated Monitoring Database Size Using sp_spaceused

ikinal 11406 — Fri, 12 Aug 2011 14:04:03 GMT

When I try to run the code given, I get some results [for 3 dbs], but the following error for the remaining[[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'hCForEachDatabase' already exists.(42000,16915)Any ideas?Thanks,Ihor Kinalikinal@ieee.org

RE: Automated Monitoring Database Size Using sp_spaceused

LoveSQL — Thu, 08 Apr 2010 17:12:42 GMT

Does this work on SQL 2008?I am getting the following error.Any help is appreciated.ThanksMsg 451, Level 16, State 1, Procedure sp_DBA_spaceused_AllTables2, Line 49Cannot resolve collation conflict for column 2 in GROUP BY statement.

RE: Automated Monitoring Database Size Using sp_spaceused

JC-3113 — Mon, 27 Jul 2009 15:00:01 GMT

Hi Folkscan someone tell me if there is a way to just select certain attributes from sp_spaceused I am trying this out as an example:USE AdventureWorks;GOEXEC sp_spaceused @updateusage = N'True';GOit gives me al the columns and databut I would like to be able to just select the database_name,database_size, and maybe unused as an exampleis that possible or do I need to look somewher else to get that infoThanksJim

RE: Automated Monitoring Database Size Using sp_spaceused

Bekir-212336 — Wed, 17 Jun 2009 09:57:00 GMT

what if we have partitioned tables :); row counts do not show up based on this script; I will work on getting that info to the script. Thanks David

RE: Automated Monitoring Database Size Using sp_spaceused

Konrad Willmert — Wed, 02 Jan 2008 09:41:14 GMT

What might it mean if I get negative numbers for the "Unused" column? Would having indexes on a separate filegroup affect these numbers?

RE: Automated Monitoring Database Size Using sp_spaceused

Dexter Jones — Thu, 06 Dec 2007 15:16:48 GMT

Why doesn't Microsoft just provide this information in a DMV and be done with it?Sure would make a few DBAs happy...

RE: Automated Monitoring Database Size Using sp_spaceused

bwaller18 — Thu, 06 Dec 2007 07:41:08 GMT

I'd like to give this a try on my 2000 engines. Can someone please post the SQL2000 equivalent for sp_DBA_spaceused?

RE: Automated Monitoring Database Size Using sp_spaceused

David.Poole — Wed, 06 Jun 2007 16:54:00 GMT

I'll look into it Marc. It should be possible but there is one thing I have found with SQL2005 and system tables. Basically you can't query them directly, you can only query the management views therefore coming up with a generic solution may not be possible.Just to clarify in SQL2000 you have sysobjects as a system table. In SQL2005 you have sysobjects as a view on underlying tables.

RE: Automated Monitoring Database Size Using sp_spaceused

Marc Scheuner — Wed, 06 Jun 2007 13:39:00 GMT

Thanks, David - and I agree, XML in a relational database might not be the best choice in all cases. But that discussion aside - what I was really hoping to find out is how to include the XML indices in your code snippet, e.g. how do I tweak your code so that the XML indices (which use up A LOT of space!) are also included? Any ideas? Anyone?

Thanks!Marc

RE: Automated Monitoring Database Size Using sp_spaceused

David.Poole — Wed, 06 Jun 2007 12:46:00 GMT

I must admit I am somewhat ambivalent to storing XML in a relational database.If I am going to store XML data it is because I want to retrieve it in entirety. Being able to search an XML document within a relational database engine strikes me as a kludge. If I wanted to search the content of an XML document I would have shredded into tables. It is one of those arguments where you either support "The Judean Peoples Front" or "The peoples Front of Judea"My method actually used the source of sp_spaceused minus some of the branches.

RE: Automated Monitoring Database Size Using sp_spaceused

Marc Scheuner — Tue, 29 May 2007 01:11:00 GMT

Quite nice - however, your stored proc (like all the ones I've seen so far) does not take into account space used by XML indices - and that space is MASSIVE! I see very large discrepancies between any "home made" solutions using sys.indexes etc., and the output by sp_spaceused, when applied to a table with XML data and indices.

RE: Automated Monitoring Database Size Using sp_spaceused

David.Poole — Fri, 05 Jan 2007 14:19:00 GMT

If anyone is interested the SQL2000 equivalent is

USE MasterGOCREATE PROC dbo.sp_DBA_spaceused_AllTables2	@updateusage varchar(5)='false'ASSET NOCOUNT ONIF @updateusage IS NOT NULL	BEGIN		-- Allow for case sensitivity		SET @updateusage=LOWER(@updateusage)		IF @updateusage NOT IN ('true','false')			BEGIN				RAISERROR(15143,-1,-1,@updateusage)				RETURN(1)			END	END-- Retrieve the current page size for the OSDECLARE @KB DEC(15)SELECT @KB = low/1024.0FROM master.dbo.spt_valuesWHERE number=1AND type='E'SELECT	MAX(DB_NAME()) AS DatabaseName ,	MAX(GETDATE()) AS DateSampled ,	U.Name+'.'+O.Name AS TableName ,	MAX(CASE WHEN I.Indid<2 THEN I.rows ELSE 0 END) AS rows,	SUM(CASE WHEN I.Indid IN(0,1,255) THEN I.reserved ELSE 0 END) * @KB AS reserved ,	SUM(CASE WHEN I.indid<2 THEN I.dpages ELSE 0 END	+ CASE WHEN I.indid=255 THEN used ELSE 0 END) * @KB AS data ,	SUM(CASE WHEN I.indid<2 THEN used-dpages ELSE 0 END) * @KB AS index_size ,	SUM(CASE WHEN I.indid IN(0,1,255) THEN I.reserved - I.used ELSE 0 END) *@KB AS UnusedFROM	dbo.sysobjects AS O	INNER JOIN dbo.sysindexes AS I ON O.id = I.id	INNER JOIN dbo.sysusers AS U ON O.uid = U.uidGROUP BY U.Name+'.'+O.Name GO

RE: Automated Monitoring Database Size Using sp_spaceused

David.Poole — Fri, 29 Dec 2006 10:30:00 GMT

I seem to remember that there is a non-documented stored procedure that runs the resultset of a query.In some situations I do build up a string of commands and then execute that string but I am always mindful of the limited string processing available within SQL Server.In SQL2000 sp_executeSQL required an NVARCHAR argument which meant that any statement had to be under 4,000 characters long.EXEC required a VARCHAR which meant a limit of 8,000 characters.Of course, now in SQL2005 we have VARCHAR(MAX) and NVARCHAR(MAX) to play with.

RE: Automated Monitoring Database Size Using sp_spaceused

katesl — Thu, 28 Dec 2006 10:53:00 GMT

This trick eliminates loop as well as cursor.

1) generate a script

select 'insert into #t exec sp_dba_spaceused ' + name from sysobjects where type = 'u'order by 1

2) run the script

When writing an automated process, I usually use the loop rather than this "select literal with select set to generate a script" trick, but it is possible to automate running the script that the select statement generates.

RE: Automated Monitoring Database Size Using sp_spaceused

Sam Greene — Thu, 28 Dec 2006 10:32:00 GMT

This cursorless loop was a new trick for me, and very appreciated.

WHILE @TableName IS NOT NULL BEGIN SELECT @TableName=MIN(TableName) FROM @UserTables

WHERE TableName>@TableName IF @TableName IS NOT NULL BEGIN INSERT INTO #T exec sp_dba_spaceused @TableName END END

RE: Automated Monitoring Database Size Using sp_spaceused

Carlos Urbina — Thu, 28 Dec 2006 08:47:00 GMT

Great, thoughtful article. will deploy asap.

RE: Automated Monitoring Database Size Using sp_spaceused

katesl — Thu, 28 Dec 2006 01:36:00 GMT

Thanks for including discussion of your methodical development and validation of this DBA utility you present. It's my opinion that writing and using scripts built on sp_msforeachdb is the mark of a true DBA. And thanks for mentioning that "allow updates to system tables" can be set to 1 in SQL Server 2005-- but to no effect. As a **database** programmer, I want info about the system tables and procedures, not "developer" junk (CLR) that encourages application programmers to regard the database as just a box that holds data.

Automated Monitoring Database Size Using sp_spaceused

David.Poole — Tue, 19 Dec 2006 19:04:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/2771.asp