Written by Rob Risetto
SQL Server Database Space Monitoring and Forecasting
You know your database is growing but you are not sure over the last 6 – 12 months how fast?
Will you run out disk space capacity for your databases or/and backups in the near future?
These questions are easy to answer if you have a SQL monitoring system in place or if you use DBInsight’s SQL monitoring service (SQLRDS).
But what if you don’t have the monitoring tools in place or utilise a service like DBInsight’s monitoring service (SQLRDS)?
Well, all is not lost if you have the SQL Server Error Log files that cover at least a few weeks to a few months. Here’s how to measure your database growth using your SQL Server Error logs.
Note that every time a full database backup is created a message is written to the SQL Server like below.
Database backed up. Database: XXXXX,
creation date(time): 2019/10/10(13:29:22),
pages dumped: 2159831
The relevant component of the message is the pages dumped value. So, if we can harvest the pages dump value over a number weeks or months then we have a ready made indicator of database growth.
The pages dumped can be converted to a MB or GB value – note 1 page = 8KB. The calculation for above is
2159831 pages x 8KB = 17278648 KB or 16873 MB or 16.4 GB
You can use a Powershell script to harvest all pages dumped values for each database in all of your SQL Server Error log files
Typically, I would copy the SQL Server error logs into a temporary folder and then point the Powershell script to that folder. It will produce a CSV file like below, showing the database name, date of full backup and pages dump.
Here’s a script that I use.
#Database backed up. Database: XXXXXXXX, creation date(time): 2019/08/14(17:11:47), pages dumped: 409252
$ErrorlogDir = “C:\temp \errorlogs”
# Get Errorlog files in $ErrorlogDir
$ErrorLogs = Get-ChildItem $ErrorlogDir | Where-Object {$_.name -like “*errorlog*” }
foreach ($File in $ErrorLogs)
{
$ErrFile = $File.fullname
Write-Host “–> Processing $ErrFile”
# Parse the error log file
get-content $ErrFile | foreach-object {if ($_ -like “*Database backed up. Database: *”) `
{$_.substring($_.indexof(“Database backed up. Database: “)+30, $_.indexof(“creation date(time): “)-$_.indexof(“Database backed up. Database: “)-32) + “,” `
+ $_.substring(0,10) + “,” `
+ $_.substring($_.indexof(“pages dumped: “)+14, $_.indexof(“first LSN:”)-$_.indexof(“pages dumped: “)-16) } } | add-content “C:\temp\backupinfo.csv”
}
You then open the CSV file in Excel and perform the Pages to MB or GB calculation, from there you can use Excel to predict the future database data file size over the desired period.
