Send a message
5 years ago
I have been looking at ways to use DBCC SQLPERF (LogSpace) as part of my Capacity Planning exercise and have come up with a uniquely simple if brute force way to achieve this for Log Drive Usage.
Firstly, when you run DBCC SQLPerf (LOGSPACE) in a new query window, you get results that look as follows :
DB Name Log Size (MB) Log Space% Status
------------- ------------- -----------------
master 3.99219 4.3469 0
msdb 1.99219 1.64216 0
model 1.0 12.7953 0
msdb 3.99219 17.0132 0
Adv Works 19.554688 17.748701 0
The output of this statements is generated in the results window and I have not been able to find a way to get the data into either a table or a file. There are some posts on other sites that outlines how to do this but I couldn't quite get the implementation to work. Gregory A. Larsen has a very detailed post on Database Journal.
However, having the output in the immediate window I decided to try using SQLCMD.
I saved the DBCC SQLPerf(logSpace) as an sql script in a file in a temporary folder on the Server. Then i used SQLCMD as follows:
sqlcmd -S <ServerName>\<InstanceName> -i c:\temp\dbcclogspace.sql -o c:\temp\dbccoutput.txt
This SQLCMD line executes the dbccLogSpace.sql file which was created in the step above (essentially it runs the DBCC SQLPerf(logspace) command) and then copies the output to the file dbccoutput.txt
Because the SQLCMD utility will always overwrite the output file, I then needed to capture the output before it got overwritten. The easiest way to do this is to use Database Mail.
I already have a database mail profile set up on my Server so I created a second job step to be run once the first step has been succeeded. The code for the second Job Step is as follows :
@body='SQLServer Database Log Space Usage is attached',
@subject ='My Email Subject',
@profile_name ='My Database Mail Profile',
This job step picks up the output file and e-mails it to email@example.com.
Now I have the log space consumption information in a text file that I can use for my trend analysis. Perhaps I would do the analysis in excel or simply pull out a calculator.
I hope this helps all those who are struggling to find a way to use the very valuable information that is generated by the most Un-Documented DBCC SQLPerf (LogSpace).