Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automated Monitoring Database Size Using sp_spaceused


Automated Monitoring Database Size Using sp_spaceused

Author
Message
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3121
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/2771.asp

LinkedIn Profile

Newbie on www.simple-talk.com
katesl
katesl
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 473
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.

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Carlos Urbina
Carlos Urbina
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 1
Great, thoughtful article. will deploy asap.
Sam Greene
Sam Greene
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 584

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



katesl
katesl
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 473

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.



_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3121
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.

LinkedIn Profile

Newbie on www.simple-talk.com
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3121
If anyone is interested the SQL2000 equivalent is

USE Master
GO
CREATE PROC dbo.sp_DBA_spaceused_AllTables2
@updateusage varchar(5)='false'
AS
SET NOCOUNT ON

IF @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 OS
DECLARE @KB DEC(15)
SELECT @KB = low/1024.0
FROM master.dbo.spt_values
WHERE number=1
AND 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 Unused

FROM 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.uid
GROUP BY U.Name+'.'+O.Name
GO


LinkedIn Profile

Newbie on www.simple-talk.com
Marc Scheuner
Marc Scheuner
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 132
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.
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3121
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.

LinkedIn Profile

Newbie on www.simple-talk.com
Marc Scheuner
Marc Scheuner
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 132

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search