By: Bernard Black | Comments (16) | Related: > Monitoring
Problem
You want to collect growth statistics on your databases and report on them. Management would like to see how fast databases are growing. Users would like to know how big their main tables are becoming. You have over a hundred servers with thousands of databases and you don't have $500 per server to spend. You'd like to do it yourself. But how?
Solution
Use a small PowerShell script to collect these statistics into two tables. In Management Studio schedule several T-SQL queries in SQL Server Agent Jobs to produce simple text reports. Setup another job to use SQL Server Database Mail to email the reports to all who want them.
As a DBA, everyone likes to look at growth reports. Databases grow, tables grow, indexes grow. I'm even still growing, but at age 55, it's not in a positive way. This year as we were moving our monitoring stuff from SQL Server 2000 to SQL Server 2008 (yes we're that far behind) and our JavaScript programs wouldn't work on a Windows 2008 box because SQL DMO did not come along. Oh there was some kind of backward compatibility thing you could install, but I wanted to get within 5 years of being current so I decided to get going with PowerShell and see if I couldn't replace the JavaScript stuff with that. Well, I got the Windows PowerShell Bible (Lee et al. Wiley Press) and between that and sites like MSSQLTips.com, I threw together something that works.
STEP 1: Create Tables
You need someplace to hold the data so create two tables to store the information. The first, is_sql_database, will hold data on databases, size, index size, and space available. The other, is_sql_tables, will hold data on tables within each database such as # rows or index size. These tables were created on a 2008 R2 instance. I don't think the date datatype will work on SQL Server 2005 or earlier editions, so you could change to a datetime datatype if your running this on a SQL Server 2005 or earlier instance. Here are the CREATE statements:
CREATE TABLE [dbo].[is_sql_databases](
[is_identity] [int] IDENTITY(1,1) NOT NULL,
[is_sqlserver] [varchar](60) NOT NULL,
[is_name] [varchar](60) NOT NULL,
[is_date_stamp] [date] NOT NULL,
[is_size] [real] NULL,
[is_available] [real] NULL,
[is_indexsize] [real] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_is_sql_databases] ON [dbo].[is_sql_databases]
(
[is_date_stamp] ASC,
[is_identity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[is_sql_tables](
[is_identity] [int] IDENTITY(1,1) NOT NULL,
[is_sqlserver] [varchar](60) NOT NULL,
[is_database_name] [varchar](60) NOT NULL,
[is_name] [varchar](60) NOT NULL,
[is_date_stamp] [date] NOT NULL,
[is_rows] [decimal](15, 0) NULL,
[is_data_space_used] [decimal](15, 0) NULL,
[is_index_space_used] [decimal](15, 0) NULL
CREATE CLUSTERED INDEX [IX_is_sql_tables] ON [dbo].[is_sql_tables]
(
[is_date_stamp] ASC,
[is_identity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Notice I created each table with clustered indexes based on the collection date and an identity field. This goes on the theory that all your reporting will be searching based on dates.
STEP 2: The PowerShell Script
These tables are populated by a PowerShell script that is driven by a text file that contains the names of the servers you want to track. Note that this script needs a text file called SQLSERVERLIST.TXT which is a simple list of the servers to connect to. The server name is STATS_2008R2, and the database is is_dba. This script runs on the same server where the two tables were created. Here is the PowerShell script:
$today = Get-Date
$srvlist = @(get-content "c:\sql_scripts\DB_Stats\SQLSERVERLIST.TXT")
ForEach($server in $srvlist){
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
if ($srv.Status -EQ "Offline" )
{
$dbs = $srv.Databases
ForEach ($db in $dbs)
{
if ( $db.IsAccessible ) {
$name1 =$db.name
$size1 =$db.size
$dbspace1 = $db.DataSpaceUsage/1KB
$dbindexsp1 = $db.IndexSpaceUsage/1KB
$dbspaceavail1 = $db.SpaceAvailable/1KB
####### Write-host "dbname=" $name1 $size1 $dbspace1 $dbindexsp1 $dbspaceavail1 $db.IsAccessible
switch ($name1 ) {
'master' {}
'model' {}
'Northwind' {}
'tempdb' {}
Default {
Invoke-SQLcmd -ServerInstance "STATS_2008R2" -Database "is_dba" -Query "INSERT INTO is_sql_databases VALUES ('$server','$name1','$today',$dbspace1,$dbspaceavail1,$dbindexsp1) "
ForEach ( $tbl in $db.tables)
{
$tname1 =$tbl.Name
$tindexsp1 =$tbl.IndexSpaceUsed/1KB
$trows1 =$tbl.RowCount
$tspace1 =$tbl.DataSpaceUsed/1KB
####### Write-host "tbl name=" $tname1 $tindexsp1 $trows $tspace1
if ($trows1 -gt 999 ) {
Invoke-SQLcmd -ServerInstance "STATS_2008R2" -Database "is_dba" -Query "INSERT INTO is_sql_tables VALUES ('$server','$name1','$tname1','$today',$trows1,$tspace1,$tindexsp1) "
} ####end RowCount > 0
} #####end Row loop
} ###### end Default
} #####end switch
} #####end IsAccessible
} #####end database loop
} ########end Status Offline check
} ###end server loop
Use SQL Server Management Studio to create a SQL Server Agent Job that simply has the script pasted in the script area and runs under type=PowerShell. The job needs to run under a dba account that has rights on each of the servers it connects to. Notice there are three loops going on. The server loop is on top and is driven by each entry in SQLSERVERLIST.TXT. The second loop is by each database on the server and omits most system databases (master, model, etc.). An 'Invoke-SQLcmd' populates database stats into the is_sql_databases table. The innermost loop is the table loop which puts table information into the is_sql_tables table. The Write-Host statements are commented out, but are there for debugging. Note the condition where it tests if the database is OFFLINE. Yeah that line took two hours of my life because I think its a Microsoft bug. When it says offline, it actually means its online. Without this check, the script will hang on OFFLINE databases. I should send Microsoft a bill for that misnomer. I'd schedule the job to run late. My run starts at 3:00am ish and takes over an hour to go thru over 100 servers and thousands of databases. I only save table data on tables with 500 or more rows which you may want to change. I put about 15,000 records a day into is_sql_tables. The server I run it on is a SQL 2008 R2 standard. It gets the data from SQL 2000, 2005, and 2008 servers.
STEP 3: Queries and Reports
Some time after this job finishes (or the next step) I run several queries in a job and simply point the results to a textfile. These queries give daily and weekly increases (note the minimums in WHERE clause). You could use these queries in a fancier SQL Server Reporting Services or Crystal Reports, but at my age I like plain text. (Watching someone on an iPhone makes me cringe). Again, use Management Studio and create a job that runs a T-SQL script. On the Advanced Tab, send the output to a textfile. In a later step email the file to whoever wants to see it. When you really want to impress someone, take out the growth restraints (5MB or 500 Rows) from the queries and send that report. In my case that would be a 15,000 line report. And all in beautiful text. Here is the query to show which databases on your servers have grown 5MB or more since yesterday.
------Query to get DB growth for today, yesterday, and a week ago
select LEFT(is_sqlserver,25) as 'SERVERNAME'
,LEFT(is_name,25) AS 'DATABASE'
,MAX( CASE DateDiff(Day,is_date_stamp, getdate() )
WHEN 0 THEN is_date_stamp
Else '02/02/1947'
END ) as 'TODAY'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN cast(is_size as decimal(12,1) )
Else 0
END ) ,12) as 'SIZE TODAY'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN cast(is_size as decimal(12,1) )
Else 0
END ) ,12) as 'SIZE YESTERDAY'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN cast(is_size as decimal(12,1) )
Else 0 END ) -
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN cast(is_size as decimal(12,1) )
Else 0
END ) ,12) as 'DAILY INCREASE mb'
,LEFT( MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 7 THEN cast(is_size as decimal(12,1) )
Else 0
END ) ,12 ) as 'SIZE WEEK-AGO'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN cast(is_size as decimal(12,1) )
Else 0 END ) -
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 7 THEN cast(is_size as decimal(12,1) )
Else 0
END ) ,12) as 'WEEKLY INCREASE mb'
from is_sql_databases
where DateDiff(Day, is_date_stamp, getdate() ) < 8
group by is_sqlserver, is_name
having
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN is_size
Else 0
END ) -
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN is_size
Else 0
END ) > 5
ORDER BY is_sqlserver, is_name
Here is the output that goes to a good old text file. The SERVERNAME has been changed to protect the innocent.
Job 'SQL_Reports_RUN' : Step 6, 'Run DB Growth Report' : Began Executing 2014-02-12 04:25:48 SERVERNAME DATABASE TODAY SIZE TODAY SIZE YESTERDAY DAILY INCREASE mb SIZE WEEK-AGO WEEKLY INCREASE mb ------------------------- ------------------------- ---------- ------------ -------------- ----------------- ------------- ------------------ xxxxxxxxx AcuityProd 2014-02-12 2217.1 2210.2 6.9 2179.4 37.7 apixx APIHealthcare_Live 2014-02-12 8933.6 8910.5 23.1 8917.1 16.5 astafxxxxxx distribution 2014-02-12 471.9 438.8 33.1 413.1 58.8 astafxxxxxx LaborWorkx_History_Live 2014-02-12 27456.1 26996.6 459.5 26996.6 459.5 astafxxxxxx LaborWorkx_Live 2014-02-12 9392.4 9292.7 99.7 9022.1 370.3 astafxxxxxx LaborWorkx_Storage_Live 2014-02-12 5895.2 5834.6 60.6 5834.6 60.6 capxxxx CAPROD 2014-02-12 15190.7 15183.2 7.5 15121.6 69.1 CVCxxxxxxx CISChartingDB8 2014-02-12 9541.9 9513.4 28.5 9322.3 219.6 CVCxxxxxxx CISChartingDB9 2014-02-12 331.5 297.7 33.8 159.4 172.1 echoxxxxxx mcf 2014-02-12 9975.5 9923.2 52.3 9680.1 295.4 endpxxxxxx BFEnterprise 2014-02-12 708.0 702.9 5.1 708.1 -0.1 fastrxxxxxxx fstrk_img 2014-02-12 10329.3 9988.1 341.2 9057.5 1271.8 fastrxxxxxxx fstrkdb_1 2014-02-12 32549.0 32529.5 19.5 31308.0 1241.0 fpxxxxxxxxxxx EMR 2014-02-12 48885.4 48807.5 77.9 48621.7 263.7 hbixxxxxxxx WebData 2014-02-12 62188.1 62145.7 42.4 61581.9 606.2 mmgxxxxxxxxx EMR 2014-02-12 340134.3 339594.0 540.3 337432.8 2701.5 mmgxxxxxxxxx Ntier_57209 2014-02-12 23838.1 23367.6 470.5 23444.1 394.0 mmgxxxxxxxxx TouchChart 2014-02-12 2960.3 2953.9 6.4 2927.2 33.1 momdbxxxxxxxx OnePoint 2014-02-12 8671.3 8617.1 54.2 8292.4 378.9 passxxxxxxxxxx hl7_live 2014-02-12 39953.6 39894.9 58.7 39699.4 254.2 passxxxxxxxxxx hne_live 2014-02-12 8866.9 8857.9 9.0 8819.2 47.7 paydatxxxxxxxxx api_en_mhsi118_live 2014-02-12 37.8 31.1 6.7 25.3 12.5 paydatxxxxxxxxx api_pm_mhsi118_live 2014-02-12 6046.9 6013.7 33.2 6021.3 25.6 pcoxxxxxxxxxxxx payercm 2014-02-12 8099.7 8091.8 7.9 8050.0 49.7 sharxxxxxxxxxxxx SharedServices1_Search_DB 2014-02-12 404.9 381.5 23.4 357.7 47.2 sharxxxxxxxxxxxx WSS_Content 2014-02-12 22952.7 22936.7 16.0 22834.9 117.8 smsxxxxxxxx sms_mhs 2014-02-12 2074.3 2065.6 8.7 2079.7 -5.4 vmsxxxxxxxxxx VMwareVirtualCenter 2014-02-12 1092.4 1083.3 9.1 1125.2 -32.8 VMSQLxxxxxx VMwareVirtualCenterDR 2014-02-12 6502.4 6495.3 7.1 6453.8 48.6
Here is a query to list tables that have grown 500 rows since yesterday. I created another job in management studio and on the Advanced tab sent the output to a text file.
-------------Query that shows tables that have grown by 500 rows since yesterday
select LEFT(is_sqlserver,25) as 'SERVERNAME'
, LEFT(is_database_name,25) AS 'DATABASE'
, LEFT(is_name, 20) as 'TABLE'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN cast(is_rows as decimal(10,0) )
Else 0
END ) ,10) as '#ROWS TODAY'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN cast(is_rows as decimal(10,0) )
Else 0
END ) ,10) as '#ROWS YESTER'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN cast(is_rows as decimal(10,0) )
Else 0 END ) -
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN cast(is_rows as decimal(10,0) )
Else 0
END ) ,10) as 'DAY INCR'
,LEFT(
CAST(100*((MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN cast(is_rows as decimal(10,0) )
Else 0 END ) -
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN cast(is_rows as decimal(10,0) )
Else 0
END ))/
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN cast(is_rows as decimal(10,0) )
Else 0
END ) )
AS decimal(6,2) )
,7) as 'DAY INCR %'
,LEFT( MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 7 THEN cast(is_rows as decimal(10,0) )
Else 0
END ) ,10 ) as '#ROWS WEEK'
,LEFT(MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN cast(is_rows as decimal(10,0) )
Else 0 END ) -
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 7 THEN cast(is_rows as decimal(10,0) )
Else 0
END ) ,10) as 'WEEK INCR'
from is_sql_tables
where DateDiff(Day, is_date_stamp, getdate() ) < 8
group by is_sqlserver, is_database_name , is_name
having
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 0 THEN is_rows
Else 0
END ) -
MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN is_rows
Else 0
END ) > 500
AND MAX( CASE DateDiff(Day, is_date_stamp, getdate() )
WHEN 1 THEN cast(is_rows as decimal(10,0) )
Else 0
END ) > 0
ORDER BY
is_sqlserver
,is_database_name
,is_name
Here is a sampling of the output that shows what tables have grown by 500 rows since yesterday.
Job 'SQL_Reports_RUN' : Step 7, 'Run Table Growth Report - 500 row increase' : Began Executing 2014-02-11 04:25:21 SERVERNAME DATABASE TABLE #ROWS TODAY #ROWS YESTER DAY INCR DAY INCR % #ROWS WEEK WEEK INCR ------------------------- ------------------------- -------------------- ----------- ------------ ---------- ---------- ---------- ---------- 3mdbyyyyyyyy MMM360DB InboundInterfaceLogs 5293 4745 548 11.55 3932 1361 accesyyyyyyyyy AcsLog EvnLog 12374743 12363390 11353 0.09 12306465 68278 acuiyyyyyyyy AcuityProd hipaa_audit_history 397972 396662 1310 0.33 391981 5991 acuiyyyyyyyy AcuityProd person_tree_queue_in 106819 106312 507 0.48 105028 1791 apiyyyyyyyyyyy APIHealthcare_Live EmployeeSummary 2946 2256 690 30.59 3668 -722 apiyyyyyyyyyyy APIHealthcare_Live PatientCensusFactorA 196924 196392 532 0.27 196924 0 apiyyyyyyyyyyy APIHealthcare_Live StaffingPlanAuditDet 174249 173728 521 0.30 173450 799 astafyyyyyyyyyy distribution MSrepl_commands 395076 391284 3792 0.97 383635 11441 astafyyyyyyyyyy LaborWorkx_Live AuthDataAccessAuditD 82524 81771 753 0.92 84915 -2391 astafyyyyyyyyyy LaborWorkx_Live AuthenticationHistor 700679 696550 4129 0.59 697533 3146 astafyyyyyyyyyy LaborWorkx_Live BenefitBalanceHistor 1616082 1613438 2644 0.16 1600448 15634 astafyyyyyyyyyy LaborWorkx_Live EmployeeAdjustment 220196 219351 845 0.39 216057 4139 astafyyyyyyyyyy LaborWorkx_Live EmployeeAuditDetail 226536 225961 575 0.25 227661 -1125 astafyyyyyyyyyy LaborWorkx_Live EmployeeCalendar 2926015 2922160 3855 0.13 2908168 17847
STEP 4: Getting the Report
If you want the reports emailed, create another job or another step and run this dbmail script. It's just another T-SQL script, and it assumes you've turned on dbmail. It grabs a text file as an attachment and sends it out. You could also just make a shortcut to the report on your desktop and double click on it each day you want to view it. Since its extension is a txt file, NOTEPAD should open and display the report. Here's the dbmail script for printing one of the reports I called'Table_Growth.txt':
EXEC sp_send_dbmail @profile_name='Edward P. Dowds', @recipients='[email protected];[email protected]', @subject='DB Growth Report', @body='Attached shows SQL Databases that have grown since yesterday', @file_attachments= 'g:\sql_scripts\DB_Stats\DB_Growth.txt;g:\sql_scripts\DB_Stats\Table_Growth.txt'
Next Steps
- Create some alerts based on DB or table growth
- Remember to create a job to trim the tables periodically.
- Do the same with Performance Counters
- Another way to Collect data on Tables
About the author
Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
