Francisco Tapia
fhtapia at gmail.com
Sun Sep 5 11:13:26 CDT 2004
I used to do that before, and is a good plan to keep, I currently use the following in a production db, back up the transaction logs when they reach 60% of their total size. here are the steps to do that: IN EM: Under the Management folder and under the SQL Server Agent icon click on Alerts and create a new Alert. Give your alert a meaningful name In the General tab: Type choose: Sql Server Performance condition alert (enabled) Object: SqlServer:Databases Counter: Percent Log Used Instance: MyDb Alert If Counter: rises above Value: 60 In the Response Tab (Check Execute Job) and create a job (the three ... dots) your job should have the following TSQL job for backup: BACKUP LOG [MyDB] TO [LogBackupDeviceName] WITH INIT On Sun, 5 Sep 2004 09:00:17 -0700, Eric Barro <ebarro at afsweb.com> wrote: > John, > > You're not the only one who thinks that SQL server is not particularly friendly. Perhaps there's a reason behind that design... > > Anyway you've come across something that you'd think that SQL server should handle gracefully -- truncating log files. > > Here's how I dealt with a similar issue... > > Overview: You will need to restrict the growth of the transaction log file and then set up a backup schedule for the log file followed by a shrink log file operation. > > 1. Right click on the database and then go into Properties. > 2. Click the Transaction Log tab > 3. Make sure to restrict file growth to a manageable size where your drive won't run out disk space > 4. Click Options tab > 5. Check the Auto shrink checkbox. This will allow SQL server to automatically shrink your database and log files to the minumum required. > 6. Click OK and apply all those changes. > 7. Go into the Management section of EM. > 8. Select Backup > 9. Create a backup set for your log files. Select the db, check Transaction Log radio button. Select destination and check the schedule box and specify when you want it to run. Depending on the amount of activity and the maximum amount you specified for the log file to grow you will need to specify a scheduled backup and shrink operation that will suit your needs. On our production environment I had it set to 1 hr intervals. > 10. Set up a scheduled shrink log file operation. I don't have the exact commands with me right now and I can't access the server from outside so I don't have the exact steps for you on this one. A little research might be good for the soul then... LOL! > > Hope this helps... > > --- > Eric Barro > Senior Systems Analyst > Advanced Field Services > (208) 772-7060 > http://www.afsweb.com > > > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of John W. > Colby > Sent: Sunday, September 05, 2004 8:23 AM > To: dba-sqlserver at databaseadvisors.com > Subject: [dba-SQLServer] Is it my imagination? > > Is it my imagination or is SQL Server just not particularly friendly? Yea, > I know, it is a BIG database and you are supposed to know what you are doing > but still... > > For example, I need to truncate my log files. I am doing this BCP and three > of the 4 BCPs I had running last night failed due to the log file running > our of room. SIGH! > > Now I can find a help page on Truncating the log file, but nowhere does it > actually say "do this". It seems that this would be something that needs to > be done manually once in awhile if for no other reason than (like this case) > to get back in operation quickly. So you would THINK there would be a > "truncate log file" menu item somewhere. > > Ah yes, research is sooooo good for the soul. I have a TON of real (paying) > work waiting to be done and I'm trying to figure out how to do a simple > thing like truncate the log file! > > John W. Colby > www.ColbyConsulting.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- -Francisco