Thursday, August 23, 2007

Sql Server 2000 & 2005 sql scripts for space usage

Script to print space usage of tables :

Works on both sql server 2000 & 2005
DBCC UPDATEUSAGE('db_name') --Run this command to update usage statistics. Be careful at very large dbs

create table #space_usage(
OBJ_NAME varchar(255),
OBJ_ROWS varchar(20),
OBJ_RESERVED varchar(20),
OBJ_DATA varchar(20),
OBJ_INDEX_SIZE varchar(20),
OBJ_UNUSED varchar(20))
--collect usage stats
declare @tname varchar(255)
declare c_tables cursor for select name from sysobjects where xtype='U'
open c_tables
fetch next from c_tables into @tname
while @@FETCH_STATUS = 0
BEGIN
insert into #space_usage execute sp_spaceused @tname
fetch next from c_tables into @tname
END
close c_tables
deallocate c_tables
--print usage stats
select sum(CAST(REPLACE(OBJ_RESERVED,'KB','') as int))/1024 Total_Size_in_MB from #space_usage --Log size is not included
select * from #space_usage order by cast(replace(OBJ_RESERVED,' KB','') as decimal(18,0)) desc
drop table #space_usage

Script to print space usage of tables :

Works only on sql server 2005, DBCC UPDATEUSAGE('db_name') is required to update stats.

WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows, fg_name )
AS (SELECT
s.Name
, o.Name
, p.used_page_count * 8
, p.reserved_page_count * 8
, p.row_count
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end
, ds.name
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o ON o.object_id = p.object_id
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.data_spaces as ds on i.data_space_id = ds.data_space_id
WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0)
SELECT t.schema_name
, t.table_name
, t.fg_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
,sum(t.tbl_rows) as rows
FROM table_space_usage as t
GROUP BY t.schema_name , t.table_name,t.fg_name
ORDER BY used_in_kb desc

Script to print space usage of filegroups & data files :

Works on both sql server 2000 and 2005.

declare @cmd nvarchar(1024)
set @cmd='dbcc showfilestats'
create table #tmpstats(
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
)
insert into #tmpstats execute (@cmd)
select fg.groupname TS_Name,
sum(s.totalextents)*64/1024 TS_Total_MB,
(sum(s.totalextents)-sum(s.usedextents))*64/1024 TS_Free_MB,
cast(cast(sum(s.usedextents) as real)/cast(sum(s.totalextents) as real)*100 as decimal(5,2))TS_Used_Perc
from #tmpstats s,sysfilegroups fg where s.filegroup = fg.groupid group by fg.groupname order by TS_Used_Perc
select fg.groupname TS_Name,s.name File_Log_Name,s.filename Filename,
s.fileid,s.totalextents*64/1024 File_Total_MB,
(s.totalextents-s.usedextents)*64/1024 File_Free_MB,
cast(cast(s.usedextents as real)/cast(s.totalextents as real)*100 as decimal(5,2))File_Used_Perc
from #tmpstats s,sysfilegroups fg where s.filegroup = fg.groupid order by File_Used_Perc
drop table #tmpstats

Another scriopt

create table #Data(
[Fileid] int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] varchar(max) NOT NULL,
[FileGroup] varchar(MAX) NULL
)
create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePATH nvarchar(MAX) NULL,
[Fileid] int null
)
create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] varchar(max) NULL
)
INSERT into #Data ([Fileid], [FileGroupId], TotalExtents ,
UsedExtents , [FileName] , [FilePath])
EXEC ('DBCC showfilestats')

update #Data
set #data.Filegroup = sysfilegroups.groupname
from #data, sysfilegroups
where #data.FilegroupId = sysfilegroups.groupid

INSERT INTO #Results (db ,[FileGroup], FileType , [FileName], TotalMB ,
UsedMB , PctUsed , FilePATH, [Fileid])
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
[Fileid]
FROM #Data
order BY 1,2

insert #Log (db,LogSize,LogUsed,Status )
exec('dbcc sqlperf(logspace)')

insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, [Fileid])
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
(s.Size/128. - FILEPROPERTY(s.name,'spaceused')/8.00 /16.00) UsedPct,
s.FileName FilePath,
s.[Fileid] [Fileid]
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.[Fileid]=f.[Fileid]
and l.db = DB_NAME()

SELECT r.*,
CASE WHEN s.maxsize = -1 THEN null
else CONVERT(decimal(18,2), s.maxsize /128.)
END MaxSizeMB,
CONVERT(decimal(18,2), s.growth /128.) GrowthMB
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.[Fileid] = s.[Fileid]
ORDER BY 1,2,3,4,5

DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log


Script to print db log space usage :

dbcc sqlperf (logspace)

No comments: