Database Size Report
Tuesday, October 4th, 2005I needed a way to get at the information in a few of my databases to identify potential hard disk hoggers. These are files that have a large amount of space allocated, but they only take up a small fraction of it with actual data. I found a script over at the google groups for a procedure called sp_dbspaceall. It had some bugs but I quickly fixed it up. I also changed it so that I could paste the code in my query analyzer without creating a perminent stored procedure in the master database.
After I was done, I was able to paste the data into an excel document. I setup some conditional formatting with the following rules:
- If the total size is between 500 and 1,000 MB then set the cell to a light green background.
- If the total size is greater than 1,000 MB then set the cell to a dark green background.
- If the free space is greater than 300 MB then set the cell to a dark red background.
- If the free space is greater than 100 MB then set the cell to a pink background.
- If the percentage used is between 10 and 50% then set the cell to a pink background.
- If the percentage used is between 0 and 10% then set the cell to a red background.
I had a nice little report in no time that brought my attention to the databases that needed the most attention. I manage many database servers with many databases. Sometimes the simplist answer is to shrink the databases or change the recovery model.
I’ve been running into a difficult problem with some databases. It appears that the smallest file size that the database was initially setup with was very large. I can not find a way to instruct the database to take less space. The only solution that I have been able to figure out is to rebuild the database from scratch and copy the data over. This is an extream measure and time consuming. I have about 50 databases that have this problem.
For anyone who is interested, here is the handy little SQL script that I had modified from Jasper Smith’s original version. I also found another variation of the script on the Database Journal with some handy information.
2 declare @Database varchar(128)
3 declare @Stats table
4 (
5 [Database] sysname,
6 [TotalMB] numeric(9,2),
7 [UsedMB] numeric(9,2)
8 )
9 create table #FileStats
10 (
11 [Fileid] int,
12 [FileGroup] int,
13 [TotalExtents] int,
14 [UsedExtents] int,
15 [Name] sysname,
16 [FileName] varchar(300)
17 )
18 create table #LogSpace
19 (
20 [Database] sysname,
21 [LogSize] numeric(15,7),
22 [LogUsed] numeric(9,5),
23 [Status] int
24 )
25 declare Databases cursor local fast_forward for
26 select catalog_name
27 from information_schema.schemata
28 open Databases
29 fetch next from Databases into @Database
30 while @@fetch_status = 0
31 begin
32 set @Command = ‘use [’ + @Database + ‘] dbcc showfilestats’
33 insert #FileStats execute(@Command)
34 insert @Stats
35 select
36 @Database,
37 cast(((TotalExtents*64)/1024.00) as numeric(9,2)),
38 cast(((UsedExtents*64)/1024.00) as numeric(9,2))
39 from #FileStats
40 delete #FileStats
41 fetch next from Databases into @Database
42 end
43 close Databases
44 deallocate Databases
45 insert #LogSpace exec(‘dbcc sqlperf(logspace)’)
46 update @Stats
47 set
48 TotalMB = TotalMB + LogSize,
49 UsedMB = UsedMB + ((LogUsed/100)*LogSize)
50 from
51 @Stats s
52 inner join #LogSpace l on s.[Database] = l.[Database]
53 drop table #FileStats
54 drop table #LogSpace
55 select
56 [Database],
57 TotalMB,
58 UsedMB,
59 [TotalMB] - [UsedMB] [FreeMB],
60 [UsedMB]/[TotalMB] [Used%]
61 from
62 @Stats
63 order by
64 [Database] asc
