SQL Clone
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
Dave Poole
Dave Poole
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

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

LinkedIn Profile
www.simple-talk.com
katesl
katesl
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 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
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 1
Great, thoughtful article. will deploy asap.
Sam Greene
Sam Greene
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1265 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-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 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.."
Dave Poole
Dave Poole
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17166 Visits: 3403
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
www.simple-talk.com
Dave Poole
Dave Poole
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17166 Visits: 3403
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
www.simple-talk.com
Marc Scheuner
Marc Scheuner
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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.
Dave Poole
Dave Poole
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17166 Visits: 3403
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
www.simple-talk.com
Marc Scheuner
Marc Scheuner
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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