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.
Answer by ThomasRushton ·
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.
Answer by Grant Fritchey ·
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.