June 18, 2009 at 2:16 pm
I'M NEW TO SQL SO PLEASE BE PATIENT IF I DON'T EXPLAIN CORRECTLY. THANK YOU IN ADVANCE
WHEN I GO INTO WINDOWS EXPLORER, PROGRAM FILES, 90 FOLDER, DATA FOLDER, AND VIEW THE .MDF AND .LDF FILES I SEE THAT SOME LOG FILES HAVE GROWN TO MORE THAT 20 GB'S. I HAVE A FULL BACKUP OF ALL DATABASES GOING 6 OUT OF 7 DAYS PER WEEK, WITH NO TRANSACTION LOG BACKUPS HOURLY. CAN I USE THE TRANSACTION LOG BACKUPS TO SHRINK THE LOG FILE TO A MORE RESPECTABLE SIZE? IF I CHOOSE AUTO SHRINK DATABASE, WILL THAT DAMAGE ANY FILES OR AFFECT THE END USER PORTION OF THE FILE IN ANY WAY? WHAT WOULD BE THE BEST APPROACH TO FREE UP SOME SPACE AND CONTROL THE SIZE OF THESE LOGS? THANK YOU FOR YOUR HELP AND PATIENCE.
June 18, 2009 at 2:22 pm
Rollie D Moe (6/18/2009)
I'M NEW TO SQL SO PLEASE BE PATIENT IF I DON'T EXPLAIN CORRECTLY. THANK YOU IN ADVANCEWHEN I GO INTO WINDOWS EXPLORER, PROGRAM FILES, 90 FOLDER, DATA FOLDER, AND VIEW THE .MDF AND .LDF FILES I SEE THAT SOME LOG FILES HAVE GROWN TO MORE THAT 20 GB'S. I HAVE A FULL BACKUP OF ALL DATABASES GOING 6 OUT OF 7 DAYS PER WEEK, WITH NO TRANSACTION LOG BACKUPS HOURLY. CAN I USE THE TRANSACTION LOG BACKUPS TO SHRINK THE LOG FILE TO A MORE RESPECTABLE SIZE? IF I CHOOSE AUTO SHRINK DATABASE, WILL THAT DAMAGE ANY FILES OR AFFECT THE END USER PORTION OF THE FILE IN ANY WAY? WHAT WOULD BE THE BEST APPROACH TO FREE UP SOME SPACE AND CONTROL THE SIZE OF THESE LOGS? THANK YOU FOR YOUR HELP AND PATIENCE.
Take transaction log backup at frequent interval to prevent your log file from growing heavily. Backup up tran log reclaims space used by committed transaction and the reclaimed space can be reused later.
Backing up tran log doesnt reduce its size physically. Internally there will be free space as space occupied by committed transactions will be marked for reuse.
U have to use dbcc shrinkfile to physically reduce the size of tran log file which is generally not adviced to be run quite often.
To control the tran log size, back tran log more frequently.
June 18, 2009 at 2:30 pm
I'LL SCHEDULE SOME TRANS BACKUPS AND SEE WHAT HAPPENS, THANK YOU...
June 18, 2009 at 2:34 pm
as stated earlier, dont expect the log size to reduce physically. If the frequency of the log backup is good enough, you can expect it not to grow further.
June 18, 2009 at 2:45 pm
IS IT IMPOSSIBLE TO REDUCE THE SIZE OF THAT LARGE LOG FILE?
June 18, 2009 at 2:49 pm
pls read my first post again. It is possible to physically reduce the log size using dbcc shrinkfile. U can use that for one off time but is not advisable to keep running often.
for more information visit http://msdn.microsoft.com/en-us/library/ms189493.aspx%5B/url%5D
June 18, 2009 at 2:50 pm
Rollie D Moe (6/18/2009)
IS IT IMPOSSIBLE TO REDUCE THE SIZE OF THAT LARGE LOG FILE?
Please do not use all CAPS in your posts.
🙂
June 18, 2009 at 5:15 pm
Don't turn on auto-shrink, it's a bad idea.
If this is a server, then you probably want regular log backups to control the size of the log, and give you recovery to a point in time. The regular log backups will allow space in the log file to be re-used.
As far as a one-time shrink, you will probably want to do that, but it's hard to know how far to shrink it. I'd recommend hourly log backups and see how large they are. Then set the log size to be a little larger than the largest log backup.
June 19, 2009 at 3:20 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply