Question :
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!
Answer :
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.
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).