This month’s T-SQL
Tuesday is hosted by Mike Fal (
Blog |
Twitter). The topic is
TrickShots and the purpose of this topic is to highlight a cool trick or process
and explained what you learned from this trick.
I encountered my
first log file issue in my early days as a DBA using SQL Server 2005. While I
was on vacation, I received a phone call from the company's system
administrator because he noticed the reduced free space on one of the drives of
the database server. When I logged on to the server I immediately noticed the large
log files that were not present a few days ago. After fixing the issue one of
the main questions that was on my mind was "How can I know the log file size
without logging on to the server to check the logs folder?".
I did some research
and came across the T-SQL Statement:
DBCC SQLPERF (LOGSPACE)
This provides
transaction logs usage statistics for all the databases on the instance (for
more information see
MSDN: DBCC SQLPERF)
I found the
information very interesting and thought it would be a good idea to store the
data everyday and look for some trend so that I can predict the size of the log
files. The following code was used to
store it in a table:
INSERT INTO Information(DatabaseName,LogSize,LogSpaceUsed,Status)
EXEC ('DBCC SQLPERF (LOGSPACE)')
Around the time I
was working on this issue, I was also now getting familiar with SQL Server 2005
Reporting Services and anything that was being stored in a table was suitable
test information for a report. This resulted in a report with the data from the
'Information' table but I had to run the script and the report manually.
Using the little
knowledge that I had of SQL Server 2005 as well as the excitement to try out
new things, I decided to use a stored procedure as well as the
subscription capability of Reporting Services to automate the process. The stored
procedure was created to execute the insert script and also returned the
inserted data every time the report was executed. The subscription capability
allowed me to schedule when the report would be executed, how it would be
delivered and the specific format of the report. The end result of this process
enabled me to get my log size information in an Excel file via email. A great
advantage of this was the fact that I could receive the email on my phone and
check the file even if I was out of office or away from my machine.
 |
| Subset of the Log File Information in the Excel File |
While this process
could have been done more easily back then and much more easily now, it was a
great way for me to learn and test the new features of SQL Server 2005. Another
benefit of this process was the fact that the information that I received from
the DBCC statement encouraged me to learn and understand more about log files.
