I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I know that one can query this data from sys.database_files or use DBCC SQLPERF(logspace) to get data from log file usage. Is there any way to automate this monitoring and get reports from this data? Any ideas and code samples are helpful!

  • I was thinking that if someone has been doing this using some sophisticated code (like Ola Hallengren's maintenance scripts) so I don't have to reinvent the wheel...
    – tarsiger
    Feb 16, 2012 at 19:46
  • 1
    I think several of the tools do this (including ours - I work for SQL Sentry) but I don't know of any free tools or scripts that wrap this up for you, though a proper search might find some that meet your requirements. I know if you just want to monitor autogrow events and not internal log specifics, for example, you can just schedule a query against the default trace (see sqlblog.org/2007/01/11/…). Feb 16, 2012 at 19:57

2 Answers 2


If you want to use PowerShell you can easily create task scheduler jobs in Windows to run the PowerShell scripts which would dump the output into a table or log file.

If you want to use SQL you can easily create SQL Server Agent jobs to pull the data from the DMVs or DBCC and dump them in a table.

It is difficult to understand exactly what you would want any reports against the stored data to look like, or which part is challenging.

  • You can also run those powershell jobs from within Agent. That tends to be more reliable but has the downside of dependency on the SQL Services.
    – JNK
    Feb 16, 2012 at 19:47
  • @JNK Yes, true. But if I'm doing something at the OS level with PowerShell I might not want it to originate from the SQL Server(s) and I'm probably going to do it from some utility box that doesn't have SQL Server (or at least isn't running SQL Agent itself). Feb 16, 2012 at 19:59
  • I like the idea of PowerShell for this too, but I'd probably go with remoting so you can run it on lots of servers all at the same time. It'll be much faster that way. You'd have to output to a table, but you could write a report then. Feb 16, 2012 at 21:45

For SQL server 2008 you can use the new Data Collector feature. More on this at this link.

You may need the Disk usage collection set. Nice graphical reports can be created if you use the feature that is already built in and free.

To see how to create reports on Disk usage collection set, check out this link.

You asked and Microsoft provided (joke).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.