How to find Data Space Used?

This feels like a stupid question as it seems very basic, but I have
been unable to find an answer to it!
Basically all I want is a SQL command to return the % data space used
in a particular database. I can find it for the log space with the
following command,
DBCC perflog
but not one that returns the % of the data space used. Does one
exist? I am using SQL 2000 BTW, but would be interested in all
versions of SQL...
Many thanks!sp_spaceused '
<nielsonj1976@.yahoo.co.uk> wrote in message
news:1188894429.748919.35510@.d55g2000hsg.googlegroups.com...
> This feels like a stupid question as it seems very basic, but I have
> been unable to find an answer to it!
> Basically all I want is a SQL command to return the % data space used
> in a particular database. I can find it for the log space with the
> following command,
> DBCC perflog
> but not one that returns the % of the data space used. Does one
> exist? I am using SQL 2000 BTW, but would be interested in all
> versions of SQL...
> Many thanks!
>|||sp_spaceused is unreliable in 2000 and doesn't account for all pages in
2005.
This code is a little sloppy, but it works. Just note, it is counting the
64KB extents allocated/used.
Oh, and though I wrote it, this is really as much Tibor's code as it is mine
:)
DECLARE @.DB sysname
DECLARE @.SQL nvarchar(255)
if exists ( select * from tempdb..sysobjects where name LIKE
'#FileStats__%' ) drop table
#FileStats
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE @.FileStats TABLE (
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE cDatabases CURSOR FOR
SELECT sdb.name
FROM master.dbo.sysdatabases sdb
WHERE status & 32 != 32
AND status & 64 != 64
AND status & 128 != 128
AND status & 256 != 256
AND status & 512 != 512
AND status & 1024 != 1024
AND status & 4096 != 4096
AND status & 32768 !=32768
OPEN cDatabases
FETCH FROM cDatabases INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS'')'
EXEC (@.SQL)
UPDATE #FileStats SET name = @.DB
INSERT INTO @.FileStats SELECT * FROM #FileStats
FETCH FROM cDatabases INTO @.DB
END
CLOSE cDatabases
DEALLOCATE cDatabases
SELECT
[Name]
,[TotalExtents]*64/1024. AS TotalExtInMB
,[UsedExtents]*64/1024. AS UsedExtInMB
,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 / 1024. AS
UnAllocExtInMB
FROM @.FileStats
--exec sp_spaceused
DBCC sqlperf(logspace)
<nielsonj1976@.yahoo.co.uk> wrote in message
news:1188894429.748919.35510@.d55g2000hsg.googlegroups.com...
> This feels like a stupid question as it seems very basic, but I have
> been unable to find an answer to it!
> Basically all I want is a SQL command to return the % data space used
> in a particular database. I can find it for the log space with the
> following command,
> DBCC perflog
> but not one that returns the % of the data space used. Does one
> exist? I am using SQL 2000 BTW, but would be interested in all
> versions of SQL...
> Many thanks!
>|||I like the output, Jay.
Anything you like to share with the public? If you have a blog or a page somewhere, I can link to it
from my blog...
(
How about adding something like below to the first SELECT column list?
...
,CASE WHEN CAST([UsedExtents] AS decimal(12,2))/TotalExtents > 0.7 THEN 1 ELSE 0 END AS "Almost
Full"
FROM @.FileStats
)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OMN8UQ77HHA.5796@.TK2MSFTNGP05.phx.gbl...
> sp_spaceused is unreliable in 2000 and doesn't account for all pages in 2005.
> This code is a little sloppy, but it works. Just note, it is counting the 64KB extents
> allocated/used.
> Oh, and though I wrote it, this is really as much Tibor's code as it is mine :)
> DECLARE @.DB sysname
> DECLARE @.SQL nvarchar(255)
> if exists ( select * from tempdb..sysobjects where name LIKE '#FileStats__%' ) drop table
> #FileStats
> CREATE TABLE #FileStats(
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE @.FileStats TABLE (
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE cDatabases CURSOR FOR
> SELECT sdb.name
> FROM master.dbo.sysdatabases sdb
> WHERE status & 32 != 32
> AND status & 64 != 64
> AND status & 128 != 128
> AND status & 256 != 256
> AND status & 512 != 512
> AND status & 1024 != 1024
> AND status & 4096 != 4096
> AND status & 32768 !=32768
> OPEN cDatabases
> FETCH FROM cDatabases INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> DELETE FROM #FileStats
> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC SHOWFILESTATS'')'
> EXEC (@.SQL)
> UPDATE #FileStats SET name = @.DB
> INSERT INTO @.FileStats SELECT * FROM #FileStats
> FETCH FROM cDatabases INTO @.DB
> END
> CLOSE cDatabases
> DEALLOCATE cDatabases
> SELECT
> [Name]
> ,[TotalExtents]*64/1024. AS TotalExtInMB
> ,[UsedExtents]*64/1024. AS UsedExtInMB
> ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 / 1024. AS UnAllocExtInMB
> FROM @.FileStats
> --exec sp_spaceused
> DBCC sqlperf(logspace)
> <nielsonj1976@.yahoo.co.uk> wrote in message
> news:1188894429.748919.35510@.d55g2000hsg.googlegroups.com...
>> This feels like a stupid question as it seems very basic, but I have
>> been unable to find an answer to it!
>> Basically all I want is a SQL command to return the % data space used
>> in a particular database. I can find it for the log space with the
>> following command,
>> DBCC perflog
>> but not one that returns the % of the data space used. Does one
>> exist? I am using SQL 2000 BTW, but would be interested in all
>> versions of SQL...
>> Many thanks!
>|||I don't have a blog, but I've been toying with creating one of those fee web
pages, like geocities, or something as I have a few scripts I would like to
share.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O1vWA%2377HHA.1188@.TK2MSFTNGP04.phx.gbl...
>I like the output, Jay.
> Anything you like to share with the public? If you have a blog or a page
> somewhere, I can link to it from my blog...
> (
> How about adding something like below to the first SELECT column list?
> ...
> ,CASE WHEN CAST([UsedExtents] AS decimal(12,2))/TotalExtents > 0.7 THEN 1
> ELSE 0 END AS "Almost Full"
> FROM @.FileStats
> )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OMN8UQ77HHA.5796@.TK2MSFTNGP05.phx.gbl...
>> sp_spaceused is unreliable in 2000 and doesn't account for all pages in
>> 2005.
>> This code is a little sloppy, but it works. Just note, it is counting the
>> 64KB extents allocated/used.
>> Oh, and though I wrote it, this is really as much Tibor's code as it is
>> mine :)
>> DECLARE @.DB sysname
>> DECLARE @.SQL nvarchar(255)
>> if exists ( select * from tempdb..sysobjects where name LIKE
>> '#FileStats__%' ) drop table
>> #FileStats
>> CREATE TABLE #FileStats(
>> [FileId] INT,
>> [FileGroup] INT,
>> [TotalExtents] INT,
>> [UsedExtents] INT,
>> [Name] sysname,
>> [Filename] varchar(255)
>> )
>> DECLARE @.FileStats TABLE (
>> [FileId] INT,
>> [FileGroup] INT,
>> [TotalExtents] INT,
>> [UsedExtents] INT,
>> [Name] sysname,
>> [Filename] varchar(255)
>> )
>> DECLARE cDatabases CURSOR FOR
>> SELECT sdb.name
>> FROM master.dbo.sysdatabases sdb
>> WHERE status & 32 != 32
>> AND status & 64 != 64
>> AND status & 128 != 128
>> AND status & 256 != 256
>> AND status & 512 != 512
>> AND status & 1024 != 1024
>> AND status & 4096 != 4096
>> AND status & 32768 !=32768
>> OPEN cDatabases
>> FETCH FROM cDatabases INTO @.DB
>> WHILE (@.@.FETCH_STATUS = 0)
>> BEGIN
>> DELETE FROM #FileStats
>> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
>> SHOWFILESTATS'')'
>> EXEC (@.SQL)
>> UPDATE #FileStats SET name = @.DB
>> INSERT INTO @.FileStats SELECT * FROM #FileStats
>> FETCH FROM cDatabases INTO @.DB
>> END
>> CLOSE cDatabases
>> DEALLOCATE cDatabases
>> SELECT
>> [Name]
>> ,[TotalExtents]*64/1024. AS TotalExtInMB
>> ,[UsedExtents]*64/1024. AS UsedExtInMB
>> ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 / 1024.
>> AS UnAllocExtInMB
>> FROM @.FileStats
>> --exec sp_spaceused
>> DBCC sqlperf(logspace)
>> <nielsonj1976@.yahoo.co.uk> wrote in message
>> news:1188894429.748919.35510@.d55g2000hsg.googlegroups.com...
>> This feels like a stupid question as it seems very basic, but I have
>> been unable to find an answer to it!
>> Basically all I want is a SQL command to return the % data space used
>> in a particular database. I can find it for the log space with the
>> following command,
>> DBCC perflog
>> but not one that returns the % of the data space used. Does one
>> exist? I am using SQL 2000 BTW, but would be interested in all
>> versions of SQL...
>> Many thanks!
>>
>