SQL Server E-mail alerts
- Thread starter Dave
- Start date
Dave
Perch
Hi Yash,
Thanks for the reply. I think that it is necessary to create a Job to get SQL Server to e-mail me when the log gets too big, but I don't have access to set one up. My log file justs grew to its new limit after I increased it after my previous log file episode. I don't think the database was backed up as usual, causing it to continue to grow. To recap the problem I had before, the Log was always around 4 to 6 megs (for quite a few months) with a 10 meg limit. Then it suddenly doubled and brought the site down. So I upped it to 18 megs, and after reporting the problem a few days later after it continued to get larger and larger, it suddenly dropped to about 6 megs again, until today when it hit 18 megs. It may have grown over the last few days as I stopped monitoring it every day as I thought it was under control again.
Does the change to the new backup server have anything to do with this? It seems to me the log should have truncated every night.
How do I get a webscript (I'm presuming you mean a .vbs file) to run automatically to monitor the situation? I can't always manually check it every day of the week (nor do I think I should have to watch it that closely).
When I know how that works, maybe I should also run a script to automatically delete the WSTAT archive file that grows by leaps and bounds unless I delete it every couple of days, or fills up my space and shuts down my site. I was promised a resolve for that issue several times, but have not seen one yet.
Thanks!
Thanks for the reply. I think that it is necessary to create a Job to get SQL Server to e-mail me when the log gets too big, but I don't have access to set one up. My log file justs grew to its new limit after I increased it after my previous log file episode. I don't think the database was backed up as usual, causing it to continue to grow. To recap the problem I had before, the Log was always around 4 to 6 megs (for quite a few months) with a 10 meg limit. Then it suddenly doubled and brought the site down. So I upped it to 18 megs, and after reporting the problem a few days later after it continued to get larger and larger, it suddenly dropped to about 6 megs again, until today when it hit 18 megs. It may have grown over the last few days as I stopped monitoring it every day as I thought it was under control again.
Does the change to the new backup server have anything to do with this? It seems to me the log should have truncated every night.
How do I get a webscript (I'm presuming you mean a .vbs file) to run automatically to monitor the situation? I can't always manually check it every day of the week (nor do I think I should have to watch it that closely).
When I know how that works, maybe I should also run a script to automatically delete the WSTAT archive file that grows by leaps and bounds unless I delete it every couple of days, or fills up my space and shuts down my site. I was promised a resolve for that issue several times, but have not seen one yet.
Thanks!
Dave
Perch
Hi Stephen,
I think you're referring to the recovery model. It is currently set to Full recovery (so that it can be recovered from the point of failure, rather than to the point of the last backup, which is what is available under Simple recovery). I do have auto shrink on, which should truncate the log at the time of the backup. About 80 to 90% (possibly more) of the activity on my site is read-only. I believe only write or update activity constitutes a transaction, so the log should stay pretty small after each day's activities and the truncating of the log after backup.
My understanding of how these things work could be incorrect, but I have found this to be the case on my own SQL Server websites that I maintain at work. I suspect backups were not done, thus increasing the size of the log each day, unless of course, as Yash has suggested, activity on my site has increased considerably (write or update activities). I don't believe that to be the case (at least not enough to triple or quadruple the transaction log size. (The log is currently 20 MB -- up from a normal 4 to 6 MB).
As I think I mentioned earlier, I ran for quite a few months with a 10 MB limit, now it is 24 MB (60% ratio). With so little room to grow, percentage wise, I think I'll have to make the ratio 50% so my site doesn't stop functioning again, unless the "apparent" problem is corrected. My database is only 34 MB, with possibly 200 insert transactions a day.
I think you're referring to the recovery model. It is currently set to Full recovery (so that it can be recovered from the point of failure, rather than to the point of the last backup, which is what is available under Simple recovery). I do have auto shrink on, which should truncate the log at the time of the backup. About 80 to 90% (possibly more) of the activity on my site is read-only. I believe only write or update activity constitutes a transaction, so the log should stay pretty small after each day's activities and the truncating of the log after backup.
My understanding of how these things work could be incorrect, but I have found this to be the case on my own SQL Server websites that I maintain at work. I suspect backups were not done, thus increasing the size of the log each day, unless of course, as Yash has suggested, activity on my site has increased considerably (write or update activities). I don't believe that to be the case (at least not enough to triple or quadruple the transaction log size. (The log is currently 20 MB -- up from a normal 4 to 6 MB).
As I think I mentioned earlier, I ran for quite a few months with a 10 MB limit, now it is 24 MB (60% ratio). With so little room to grow, percentage wise, I think I'll have to make the ratio 50% so my site doesn't stop functioning again, unless the "apparent" problem is corrected. My database is only 34 MB, with possibly 200 insert transactions a day.
Dave
Perch
Thanks, Stephen. I hope there's a resolve soon. Just an update... overnight (my slowest period) the log grew to 23 MB so I had to increase the database quota to 70 MB so my log would have room to grow today. Hopefully a 28 MB limit will get me through the day. That may not seem like a big amount, but when it's quadruple the normal operating size I think it indicates a problem.