Shrink Log File on MS sql 2005

[former_member578055]

Hi all,

My DB has a huge logfile, with more than 100gb.

The idea is to shrink it, but the good way.

I was trying this way:

use P01

go

backup log P01 TO DISK = 'D:\P01LOG1\P01LOG1.bak'

go

dbcc shrinkfile (P01LOG1,250) with no_infomsgs

go

The problem is that the backup file is getting bigger and bigger each backup.

So, my question is, how to shrink the logfile, correctly, with backup, but that backup should not increase but stay at the same level, overwriting the backups.

I have full dayly backup with data protector from HP, but it doesn't clean the log, and it isn't possible to shrink it.

[Former Member · Accepted solution]

Are you check the

Note 625546 - Size of transaction log file is too big

Note 1166117 - Steps to Shrink a Log File for the Database (SQL 2005)

Regards.

[former_member578055]

Used a script to shrink it.

Using a daily backup.

[Former Member]

Hello Pedro,

if you can not get it smaller at all after you have done your changes and backups etc you can use a script that I can email you (tried pasting it here but comes out as garble garble), it has not yet let me down. make sure you change the commented variables.

Regards,

Andi

[Former Member]

Just a thought ... if you upgrade to MS SQL 2008 and export / import the DB then this decreases by 30% ... one of the advantages of upgrading to 2008 (apart from performance!).

[Former Member]

Hi,

Select the option "overwrite" while backing up the logs. You also can compress the backups if needed.

Regards,

Varadharajan M

[Former Member]

What you want to do with the log backups depends on how you are going to recover the database in case the system/database loss and your backup schedule.

1. If you are not going to do point in time recovery then there is no point in taking a tran log backup to a backup file. You can change the recovery model of the database to "simple". If your recovery model is "simple" you don't have to take transaction log backups at all. The inactive transactions are flushed from the log automatically. You should still be taking full and differential backups so that you can atleast recover your database to last full backup and apply the latest differential backup.

2. If this is a production system then you should definitly be on "full" recovery mode and should be taking regular transaction log backups and storing them in a safe place so that you can use them to recover your system to any point in time. Storing the transaction log backup on the same server kind of defeats the purpose because if you lost the server and disks you will not have the backups either.

3. If you are in full recovery mode and lets assume that you run your transaction log backups every 30 mins then you need your log file to be of the size that can handle the transactions that happen in any given 30 to 60 mins.

There shouldn't be a need to constantly shrink log files if you configure things right.

Edited by: Neeraj Nagpal on Aug 20, 2010 2:48 AM