alert log file to monitor disk space
I have a sql server which has some Biztalk databases which can grow like wildfire on a daily basis, where log files can grow to 20 gig in an hour. Is there a way to set an alert based upon a log file size growing to big that I can fire an sp on to shrink when needed. Or any other better approach?
Software/Hardware used:
Software/Hardware used:
ASKED:
April 4, 2008 7:35 PM
UPDATED:
August 24, 2011 6:54 PM
Answer Wiki:
You will want to schedule transaction log backups to occur on a regular schedule. This will keep the transaction logs from growing to large.
If you don't need to be able to do a point in time restore then you can change the database recovery option from FULL to SIMPLE.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
As everyone says that changing the recovery model from Full to Simple with resolve….but it is myth…let us assume that it is in simple recovery model still the log file will increase as there maybe a bulk insert/bcp or a begin/commit stuff……We should know how much size it increased to take precautionary steps….for that, we need to have a script to capture the size of the LDF………i have created a script long back (3 years ago) and i will place it soon on my blog is ready..cya soon.. Xavier K.
TCSTL,
Change the recovery model to SIMPLE and then make sure you keep an alert on the log file size growth of that database…….before that make sure u have a DBMail configured and a Operator configured for the same to receive alerts…..upon the checking the behavior n growth of the ldf you can create trigger a job to shrink the log file…
Hope this will help in taking precautionary steps.