Space crunch due to growing T-Log file

Question

question

Mandar Alawani asked Jun 23, '10

Space crunch due to growing T-Log file

Hi All,

I have 2 DBs, PRIMARY and ARCHIVE.

Daily, data older than 15 days is moved from PRIMARY to ARCHIVE DB. After movement, same data is deleted from PRIMARY DB. During this deletion process, the T-Log file of PRIMARY DB increases to almost 25-30GB. The data file of PRIMARY DB is 50GB approx.

I have scheduled T-Log backup after the deletion process. Hence, T_log backup size is also almost of same size as T-Log file i.e. 25-30GB.

I am facing severe space cruch as the drive has to now have space for PRIMARY DB Data file + PRIMARY DB T-Log file + PRIMARY DB T-Log backup file.

Is there anyway I can not allow the T-log file to increase or release space immediately after the deletion process? Will changing the recovery model to SIMPLE/BULK-LOGGED help? However, I do not want it to affect the point-in-time recovery.

Any help will be appreciated!!..URGENT help needed.

transaction-log delete space

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 commented · Jun 23, 2010 at 09:36 AM

Two questions - how many rows are affected and how much does your Archive dB grow by?

0 ·

Mandar Alawani commented · Jun 24, 2010 at 02:55 AM

Archive DB increases by 600MB-1GB every week.But that is not an issue as it is placed on a different drive and data from Archive DB is purged every week.

0 ·

Answer 1 · 2010-06-23T08:43:22Z

Magnus Ahlkvist answered Jun 23, '10

If you still want the ability to restore to point in time, you don't have the option to set database to simple recovery mode to reduce space.

Buy more disk, or backup to tape directly are my two advices.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-06-23T08:45:56Z

Grant Fritchey answered Jun 23, '10

If you want to keep point in time recovery, then you must maintain the database in full recovery mode and run transaction log backups. First thing that comes to mind, based on the information provided, you're only running transaction log backups after the delete. Run them all day long. We run ours, depending on the system, somewhere between once every 10 minutes and once every 30.

With that in place, you need to work on the size of your transactions. Break down the delete statement so that it only does 10000 rows at a time or something like that. That will reduce the size of the transaction, allow the transaction to complete, which means it will be marked for clean-up at the next checkpoint, which means it will get cleaned out of the log at the next backup.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mandar Alawani commented · Jun 23, 2010 at 08:59 AM

We are running log backups 4 times a day, one of those is after deletion.

0 ·

Grant Fritchey ♦♦ commented · Jun 23, 2010 at 09:04 AM

It's just my opinion of course, but I'd say that four times a day is light. Regardless though, if you want full recovery, you have to deal with that fact. That means reducing the size or duration of transactions.

0 ·

Answer 3 · 2010-06-23T09:18:04Z

ThomasRushton answered Jun 23, '10

Another option, as well as "buy bigger disks" / "decrease transaction size" / "increase frequency of tlog backups" is, assuming you've got the right version of SQL Server, to investigate compression.

Backup compression: http://msdn.microsoft.com/en-us/library/bb964719.aspx

Requires: SQL Server 2008 Enterprise, or SQL Server 2008 R2 Standard, or better.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question