December 19, 2006 at 7:04 pm
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/2771.asp
December 28, 2006 at 1:36 am
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.."
December 28, 2006 at 8:47 am
Great, thoughtful article. will deploy asap.
December 28, 2006 at 10:32 am
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
December 28, 2006 at 10:53 am
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.."
December 29, 2006 at 10:30 am
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.
January 5, 2007 at 2:19 pm
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
FROMdbo.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
May 29, 2007 at 1:11 am
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.
June 6, 2007 at 12:46 pm
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.
June 6, 2007 at 1:39 pm
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
June 6, 2007 at 4:54 pm
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.
December 6, 2007 at 7:41 am
I'd like to give this a try on my 2000 engines. Can someone please post the SQL2000 equivalent for sp_DBA_spaceused?
December 6, 2007 at 3:16 pm
Why doesn't Microsoft just provide this information in a DMV and be done with it?
Sure would make a few DBAs happy...
January 2, 2008 at 9:41 am
What might it mean if I get negative numbers for the "Unused" column? Would having indexes on a separate filegroup affect these numbers?
June 17, 2009 at 9:57 am
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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply