question

Mandar Alawani avatar image
Mandar Alawani asked

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-logdeletespace
2 comments
10 |1200 characters needed characters left characters exceeded

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

Two questions - how many rows are affected and how much does your Archive dB grow by?
0 Likes 0 ·
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 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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 characters needed characters left characters exceeded

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

Grant Fritchey avatar image
Grant Fritchey answered

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 comments
10 |1200 characters needed characters left characters exceeded

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

We are running log backups 4 times a day, one of those is after deletion.
0 Likes 0 ·
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 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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