Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
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 '12 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.com/blogs/aaron_bertrand/archive/2007/01/11/…). –  Aaron Bertrand Feb 16 '12 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.

share|improve this answer
    
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 '12 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). –  Aaron Bertrand Feb 16 '12 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. –  Grant Fritchey Feb 16 '12 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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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