June 5, 2015 at 8:46 am
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
So my question is could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.
For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.
Will this work?
June 5, 2015 at 9:35 am
comeraghsolutions (6/5/2015)
So my question is could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
Yep, if you don't need the disk space back just yet there's little point shrinking the log file just for it to grow again whilst you're carrying out your deletes.
Particularly as growing the log file is a slow operation & can impact performance if it happens at a busy time.
Transactions aren't discarded as such during a log backup, the section they're in is marked as reusable.
Also allow yourself some time when the deletes are complete to rebuild the indexes on the table(s) you're deleted from.
June 5, 2015 at 9:42 am
comeraghsolutions (6/5/2015)
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
So my question is could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.
For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.
Will this work?
Is your database set to Simple or Full recovery, if the latter do you have regularly scheduled transaction log backups running?
I recommend checking out this excellent write-up on the transaction log by Gail Shaw (http://www.sqlservercentral.com/articles/Administration/64582/)
To lessen the hit to the transaction log you could create a job that delete records from the logging tables in small batches. For example: BEGIN TRY
BEGIN TRANSACTION
/* Delete the data */
DELETE TOP (1000) FROM dbo.YourTable A
WHERE SomeCondition <= SomeCriteria
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
>> Send an email stating there was an error
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
>> Do something here
END CATCH
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply