There are lots of ways to monitor data file and log file growth, but my favorite method is to set up a perpetual Counter Log in Windows Perfmon. The SQLServer:Databases object contains the following counters (among others):
- Data File(s) Size (KB)
- Log File(s) Size (KB)
- Log File(s) Used Size (KB)
- Log Growths
- Log Shrinks
- Percent Log Used
No idea why there isn’t a “Data File(s) Used Size (KB)”. You’ll have to use other methods for that.
I prefer to log this to a CSV file, so I can just whip it open in Excel and make a few graphs (ok, nice graphs take a long time, actually…). This lets you see what databases are responsible for using different amounts of log space over time, and helps you determine an ideal “permanent” log size for each database, so you don’t have 100gb set aside for a log that never uses more than 1gb.
Make sure to capture data more often than you run log backups (if you backup logs every hour, then capture this data every 15 minutes, for example). That way you can see how much log space is actually used between log backups, and possibly make some adjustments to your log backup schedule.
To make sure your perfmon counter restarts automatically when the server gets rebooted, set the START TIME to some time in the past, and set STOP AFTER to 9999 days.
Also, check out Paul Randal’s awesome video attached to his Technet article to see these counters in action!