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.

more ▼

asked Jun 23, 2010 at 08:39 AM in Default

Mandar Alawani gravatar image

Mandar Alawani
376 33 39 44

Two questions - how many rows are affected and how much does your Archive dB grow by?
Jun 23, 2010 at 09:36 AM Blackhawk-17
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.
Jun 24, 2010 at 02:55 AM Mandar Alawani
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Jun 23, 2010 at 08:43 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 23, 2010 at 08:45 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

We are running log backups 4 times a day, one of those is after deletion.
Jun 23, 2010 at 08:59 AM Mandar Alawani
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.
Jun 23, 2010 at 09:04 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 23, 2010 at 09:18 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 23, 2010 at 08:39 AM

Seen: 1723 times

Last Updated: Jun 23, 2010 at 08:39 AM