Sometimes when we are administering our SQL Server environments we need to quickly report on this environment indicating for all of our databases (data & tlog) in the Instance or for a subset of databases (data & tlog) in the Instance the :
- Current Size
- Max Size
- Growth Increments
- Free Space
- Percentage Used
There is no quick and easy way of achieving this. There are the default standard reports available in SQL Server Management Studio (SSMS) – Right Click on [DBName] | Reports | Standard Reports | Disk Usage which gives you a breakdown of the:
- Total Space Used
- Data Files Space Used
- Transaction Log Space Used
- Pie Graphs for Data and Tlog usage
This report is very nice and quickly and easily shows you the information that you need, but as I stated at the start, you need to get this information for all of your databases in your environment. If you have 20 or more Databases in your instance of SQL Server then this will take a little bit of time. If you have a Windows Server Fail-over Cluster (WSFC) running multiple instance of SQL Server, This task is going to take you a long time to get the information you need.
As such I have created a script which allows you to report on all DB’s in your instance and this can also be run from a central management group to report on all of your instances and the results can then be quickly and easily collated together into Excel to allow you to quickly and easily produce instance based graphs etc for you capacity planning meeting with your manager.
I use the below script:
Use master
Go
Create Table #tmp_DBNames (
DBName Varchar(200)
)
Create table #tmp_DBSpacing (
DBName varchar(200),
FileID Int,
database_size Decimal(35,2),
[MaxSize] Decimal(35,2),
Growth Decimal(35,2)
)
Create Table #DBFile_Info (
FileID int,
FileGroup int,
TotalExtents Dec(20,2),
UsedExtents Dec(20,2),
DB_FileName Varchar(200),
FilePathName Varchar(500)
)
Create Table #DB_Logfile_Info (
DBName Varchar(200),
LogSize_Reserved Dec(20,2),
LogSpace_Used_Percent Dec(20,2),
Status bit
)
Create Table #DBResults (
DBName Varchar(200),
FileID Int,
DB_Reserved_Space Dec(20,2),
DB_Used_Space Dec(20,2),
DB_Free_Space Dec(20,2),
DB_Free_Percent Dec(20,2),
DB_Max_Size Dec(35,2),
DB_Growth Dec(35,2),
Log_Reserved_Space Dec(20,2),
Log_Used_Percent Dec(20,2),
Log_Free_Percent Dec(20,2),
Log_Max_Size Dec(35,2),
Log_Growth Dec(35,2)
)
Create Table #DBFileUsage_Report (
DBName Varchar(200),
FileType Varchar(100),
DB_Size_GB dec(35,4),
Datafile_freespace_GB dec(35,4),
DB_Pct_Used dec(35,4),
DB_File_Max_Size_GB dec(35,4),
DB_File_Allowed_Growth_GB dec(35,4),
DB_File_Growth_Increment_GB dec(35,4),
TLog_Size_GB dec(35,4),
Logfile_Freespace_GB dec(35,4),
Log_Pct_Used dec(35,4),
Log_File_Max_Size_GB dec(35,4),
Log_File_Allowed_Growth_GB dec(35,4),
Log_File_Growth_Increment_GB dec(35,4)
)
Declare @DBName Varchar(200)
Declare @Cmd VarChar(4000)
Declare @nCmd NVarChar(4000)
Declare @RunCMD nVarchar(500)
Declare @TotalMaxLogSize Dec(20,2)
Declare @TotalLogGrowth Dec(20,2)
Insert Into #tmp_DBNames
Select Name
from sysdatabases
--Where Name not in ('master','msdb','model','tempdb')
Where Name not in ('master','msdb','model')
-- only need to run once as it gets all DB's for current instance
Insert Into #DB_Logfile_Info
exec ('DBCC SQLPERF(LOGSPACE)')
Declare DBNames_Cursor Cursor For
Select DBName
From #tmp_DBNames
Open DBNames_Cursor
Fetch Next From DBNames_Cursor
into @DBName
While @@Fetch_Status = 0
Begin
Set @Cmd = 'Select fileid,
convert(decimal(35,2),size) / convert( float, (1048576 /(select low from master.dbo.spt_values where number = 1 and type = ''E''))),
convert(decimal(35,2),maxsize) / convert( float, (1048576 /(select low from master.dbo.spt_values where number = 1 and type = ''E''))),
convert(decimal(35,2),growth) / convert( float, (1048576 /(select low from master.dbo.spt_values where number = 1 and type = ''E'')))
From [' + @DBName + '].dbo.sysfiles'
Set @nCmd = Cast(@Cmd As nVarchar(1000))
insert Into #tmp_DBSpacing (fileid, database_size,[MaxSize],growth)
Exec sp_Executesql @nCmd
Update #tmp_DBSpacing
Set DBName = @DBName
Where DBName is Null
Set @RunCMD = 'Use [' + @DBName + '] DBCC showfilestats'
Insert into #DBFile_Info
Exec sp_executesql @RunCMD
Insert Into #DBResults (DBName, FileID, DB_Reserved_Space, DB_Used_Space, DB_Free_Space, DB_Free_Percent, Log_Reserved_Space, Log_Used_Percent, Log_Free_Percent)
Select @DBName,
FileID,
(TotalExtents * 64 /1024),
(UsedExtents * 64 / 1024),
(TotalExtents * 64 /1024) - (UsedExtents * 64 / 1024),
(((TotalExtents * 64 /1024) - (UsedExtents * 64 / 1024)) / (TotalExtents * 64 /1024)) * 100,
LogSize_Reserved,
LogSpace_Used_Percent,
100 - LogSpace_Used_Percent
From #DBFile_Info dfi ,#DB_Logfile_Info dli
Where dli.DBName = @DBName
-- Update the newly populated DBResults with the MaxSize values for the data files
Update #DBResults
Set DB_Max_Size = #tmp_DBSpacing.[MaxSize],
DB_Growth = #tmp_DBSpacing.Growth
From #tmp_DBSpacing Inner Join #DBResults On #DBResults.DBName = #tmp_DBSpacing.DBName
And #DBResults.FileID = #tmp_DBSpacing.FileID
Where #DBResults.DBName = @DBName
-- Determine the maxsize for the tlog file(s)
Select @TotalMaxLogSize = Sum(#tmp_DBSpacing.[MaxSize]),
@TotalLogGrowth = Sum(#tmp_DBSpacing.Growth)
From #tmp_DBSpacing
Where #tmp_DBSpacing.FileId Not In (Select FileID
From #DBResults
Where DBName = #tmp_DBSpacing.DBName)
And #tmp_DBSpacing.DBName = @DBName
-- Update the max Tlog size based on the above calculation
Update #DBResults
Set Log_Max_Size = @TotalMaxLogSize,
Log_Growth = @TotalLogGrowth
Where DBName = @DBName
Truncate table #DBFile_Info
Fetch Next From DBNames_Cursor
into @DBName
End
Close DBNames_Cursor
Deallocate DBNames_Cursor
Insert Into #DBFileUsage_Report
Select DBName,
FileType = Case
When FileId = 1 Then 'Primary File'
Else 'Secondary File'
End,
(DB_Reserved_Space/1024) As 'DB File Size (GB)' ,
(DB_Free_Space/1024) As 'DB Free Space in File (GB)',
'DB % Used' = Case
When (DB_Reserved_Space/DB_Max_Size) * 100 < 0 Then 0
Else (DB_Reserved_Space/DB_Max_Size) * 100
End,
(DB_Max_Size/1024) As 'DB Max Size (GB)',
'Allowed DB Growth' = Case
When DB_Max_Size - DB_Reserved_Space < 0 Then 0
Else (DB_Max_Size - DB_Reserved_Space)/1024
End,
(DB_Growth/1024) As 'Growth Increment (GB)',
(Log_Reserved_Space/1024) As 'Logfile Size (GB)',
(((Log_Reserved_Space * Log_Free_Percent) /100)/1024) As 'Logfile Freespace (GB)',
--((Log_Reserved_Space/Log_Max_Size) * 100) As 'Log % Used'
'Log % Used' = Case
When Log_Max_Size > 0 Then ((Log_Reserved_Space/Log_Max_Size) * 100)
else 0
End,
(Log_Max_Size/1024) As 'Log Max Size (GB)',
--((Log_Max_Size - Log_Reserved_Space)/1024) As 'Allowed Log Growth (GB)'
'Allowed Log Growth (GB)' = Case
When Log_Max_Size > 0 Then ((Log_Max_Size - Log_Reserved_Space)/1024)
Else 0
End,
(Log_Growth/1024) As 'Growth Increment (GB)'
from #DBResults
Order By DBName Asc, DB_Reserved_Space Desc
Select * from #DBFileUsage_Report
--where DBName in ('dev0NextCCDB','TempDB')
Drop Table #tmp_DBNames
Drop table #tmp_DBSpacing
Drop Table #DBFile_Info
Drop Table #DBResults
Drop Table #DB_Logfile_Info
Drop Table #DBFileUsage_Report
I hope this helps reduce your workload for this small repetitive task.
Leave a Reply