Eric Barro
ebarro at afsweb.com
Sun Sep 5 11:00:17 CDT 2004
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