| Author |
Topic |
|
hismightiness
Posting Yak Master
164 Posts |
 Posted - 2005-08-29 : 15:41:33
|
| Is there anything I need to do or can do about this alert?DESCRIPTION: The SQL Server performance counter 'Percent Log Used' (instance '_Total') of object 'SQLServer:Databases' is now above the threshold of 88.00 (the current value is 90.00).The alert itself is as follows:Type: SQL Server performance condition alertObject: SQLServerDatabasesCounter: Percent Log UsedInstance: _TotalAlert if counter: rises aboveValue: 88This alert occurs several times throughout the days and evenings. I can understand it happening in the evenings with some of the jobs I have running, but not so much during the day. Can I or should I reduce the log file size, and how?- - - -- Will -- - - -http://www.servicerank.com/ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-29 : 15:44:07
|
| You should expand the file as it appears it needs it anyway. You should never shrink the file unless you know for certain that it no longer needs it. Apparently, yours need it. So expand it to avoid this alert!How often are you running transaction log backups? We backup ours every 15 minutes. Tara |
 |
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2005-08-29 : 16:08:56
|
| My transaction log files all have the following settings...Automatically grow file: checkedFile growth: By percent (10)Maximum file size: Unrestricted file growthWhat more can be done? I do not have an option to back-up the transaction logs. Many of my databases are backed-up daily, but some are more or less often depending the on the nature of the database. My databases are all in simple mode because my LAN Admin says they need to be for his back-up solution to update the database files properly.- - - -- Will -- - - -http://www.servicerank.com/ |
|
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2005-08-29 : 16:12:51
|
| I should be more specific. His back-up solution is not just for the files on the system. It is also to restore the system to any period of time in the event there is a total meltdown or something. Basically, the OS can go kaput and all the system files disappear, and we can still have the server back within the hour. (Soon, there will be redundant servers that will automatically kick-on should this occur, but that is not really on topic - although it is exciting!)- - - -- Will -- - - -http://www.servicerank.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-29 : 16:13:09
|
| Why is the LAN admin performing DBA maintenance? Aren't you the DBA? A common mistake is to let them use their tape backup software. Backups should be handled inside SQL Server using BACKUP command. The LAN admin can then sweep these files to tape using his/her tape software.Using SIMPLE, BULK-LOGGED or FULL recovery models should be based upon the business needs and not what a LAN admin says. Manually expand the file by typing in a larger number in the properties of the database in Enterprise Manager. Sometimes it is unable to grow fast enough.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-29 : 16:15:42
|
quote:
Originally posted by hismightiness
I should be more specific. His back-up solution is not just for the files on the system. It is also to restore the system to any period of time in the event there is a total meltdown or something. Basically, the OS can go kaput and all the system files disappear, and we can still have the server back within the hour. (Soon, there will be redundant servers that will automatically kick-on should this occur, but that is not really on topic - although it is exciting!)- - - -- Will -- - - -http://www.servicerank.com/
He won't be able to do point in time recovery whereas with FULL recovery model and BACKUP LOG you could. Your system must not be mission critical.Tara |
|
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2005-08-31 : 10:12:25
|
quote:
Why is the LAN admin performing DBA maintenance? Aren't you the DBA? A common mistake is to let them use their tape backup software. Backups should be handled inside SQL Server using BACKUP command. The LAN admin can then sweep these files to tape using his/her tape software.
He is using the b/u software to do this. I have my back-up and log files going to a directory specifically created for the purpose of backing up using his back-up solution. However, the other part of the back-up software is to keep all system files backed-up in the event of a total system failure, this way if the server either ceases to function, or it suffers a failure to where the entire OS and installed software needs to be restored from a previous date/time, it can be done fairly quickly.I will try increasing the file growth and see how that goes. Thanks for the help.By the way, I am the DBA and web developer, and software architect, and graphic designer, and... You get the idea. However, I have no control of the actual LAN/WAN back-up and recovery model, only the SQL Server DBs.- - - -- Will -- - - -http://www.servicerank.com/ |
|
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2005-09-02 : 17:01:56
|
| Do you have any more ideas? I increased the percentage of automatic growth on the two databases which do grow pretty rapidly, and this is still happening.- - - -- Will -- - - -http://www.strohlsitedesign.comhttp://www.servicerank.com/ |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-04 : 22:33:06
|
I personally use alerts (actual size of the used log file, not percentage threshold) because log files have been restricted and maintained at a certain size.If you're worried about the log file getting full and since you're on simple recovery mode, set the log to unlimited autogrow.Also, tell your LAN admin to just backup the backup files not the database files  , or skip the database files when making the system file backup  --------------------keeping it simple... |
|
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2005-09-06 : 08:51:00
|
| Thanks jen...I already have those settings in place. As far as the LAN/Back-Up thing. I am working on it.- - - -- Will -- - - -http://www.strohlsitedesign.comhttp://www.servicerank.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-06 : 12:36:50
|
| Manually expand the file to what you think it needs. Do you have the auto shrink option enabled? Or do you have a job that runs the shrink? If so, then you'll want to stop doing this as this is what is causing your problem. It also causes performance problems while it is running.Tara |
|
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2005-09-09 : 09:29:15
|
| AutoShrink was enabled. Nice catch - thanks! However, I wonder why this normally only happens in the afternoons and evenings. Hmmm... Most of my intensive jobs run during the wee morning hours.- - - -- Will -- - - -http://www.strohlsitedesign.comhttp://www.servicerank.com/ |
|
|
|
|
Alert: Percent Log Used