Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Automated Monitoring Database Size Using sp_spaceused Expand / Collapse
Author
Message
Posted Tuesday, December 19, 2006 7:04 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
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
Post #331684
Posted Thursday, December 28, 2006 1:36 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:20 PM
Points: 63, Visits: 470
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.."
Post #333109
Posted Thursday, December 28, 2006 8:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 31, 2007 8:22 AM
Points: 92, Visits: 1
Great, thoughtful article. will deploy asap.
Post #333165
Posted Thursday, December 28, 2006 10:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:43 PM
Points: 253, Visits: 544

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


Post #333218
Posted Thursday, December 28, 2006 10:53 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:20 PM
Points: 63, Visits: 470

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.."
Post #333224
Posted Friday, December 29, 2006 10:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
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
Post #333494
Posted Friday, January 5, 2007 2:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
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
Post #334810
Posted Tuesday, May 29, 2007 1:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:42 PM
Points: 3, Visits: 120
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.
Post #369408
Posted Wednesday, June 6, 2007 12:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
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
Post #371776
Posted Wednesday, June 6, 2007 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:42 PM
Points: 3, Visits: 120

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

Post #371796
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse