Question

  Asked: Apr 18 2008   1:11 PM GMT
  Asked by: SQL Server Ask the Experts


Looking for a stored procedure to monitor size of Transaction Log file


SQL Server transaction logs, SQL Server stored procedures, SQL Server availability

Is there any way I can create a stored procedure which will check for the Transaction Log file size - where we have a specified limit to grow - and if the Log file reached 80 percent of total size, email alerts can be sent to DBAs?

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0



Yes you can monitor this by looking at the size column in the sysfiles table (SQL 7/2000) or the sys.database_files DMV (SQL 2005+). This column is in pages, so take this number and multiply by 8 to get the file size in Kb. Then divide by 1024 to get Megs. Then if the value is over the preset value use xp_Sendmail of sp_send_dbmail to email the alert.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on SQL Server and SQL.

Looking for relevant SQL Server Whitepapers? Visit the SearchSQLServer.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Apr 18 2008  6:23PM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.