April 30, 2015 at 5:33 am
Help appreciated here.
I'm emptying a SQL server back end with ~15 tables, one of them is an Audit log that holds every single operation performed in other tables of the DB. The idea is for the DB to be reset to a new life cycle.
To delete records in this table, I've disabled the Del trigger in it, then (tried to) run DELETE FROM tblAuditLog.
This throws a transaction log error 9002 ('Transaction log full'), so I cannot complete the DELETE. That's understandable, as this table made ~90% of the total DB space.
I've also tried TRUNCATE, but no success. I can't even re-Enable the trigger: the TLOG is full.
I don't have access to the T.LOG file myself (it does). Still, what's the best way to get out of this?
Once that table is deleted, we don't need the info in the DB any more? Can we simply delete the TLOG? Change the backup policy?
Thanks in advance, p.
April 30, 2015 at 9:55 am
This basically means that you've filled your transaction log file and it cannot grow, either because autogrowth is disabled or it's reached its maxsize.
There are a few things I'd look into.
1) Prior to running anything, check the recovery model of the DB, why it's waiting on reusing the log, and the autogrowth/maxsize settings of the log file.
You could check those with the following queries:
SELECT
size,
max_size,
growth
FROM master.sys.master_files
WHERE type=1 AND DB_NAME(database_id)='Your database name'
SELECT
recovery_model_desc,
log_reuse_wait_desc
FROM master.sys.databases
WHERE name='Your database name'
If it's in FULL recovery and hasn't had a transaction log backup (a common cause of unwanted log growth), then I'd get a log backup process in place ASAP.
2) Instead of deleting everything in the table at once, look at batching your delete process.
http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes is a nice reference for that.
The fact that even very small operations are giving you that error suggests to me that you either have a DB in full recovery with no backups and autogrowth disabled/maxsize reached, or you have an open transaction preventing the log from truncating.
If it's the latter, that will show up as the log_reuse_wait_desc in the second query above. You can figure out what it is by using DBCC OPENTRAN(Your Database Name).
I hope this helps!
May 1, 2015 at 3:12 am
Thanks Jacob, that's really useful.
Some feedback for other people in the same situation:
-The issue was caused by lack of space in the disk. I couldn't do much about that (as usual that's IT dept. terrain).
-IT has shrunk the TLOG; I've now been able to carry on a TRUNCATE TABLE operation w/o any issues.
-The DB model was in Full recovery mode, log_reuse_wait_desc: LOG_BACKUP (I take this means the TLOG has been backed up? and not that is waiting for it. That possibly could have solved the problem in itself).
And the size-growth command gives me some info, although it hasn't told me much on whether that could fix the problem.
Thanks!
May 1, 2015 at 7:11 am
I'm glad you got it figured out!
The LOG_BACKUP reuse wait description indicates that space in the log could not be reused because a log backup had not occurred since the space was used.
When a database is in the full recovery model, you get the ability to take log backups and restore to particular points in time. However, that doesn't come for free. That means that unlike when the DB is in simple recovery, the log can't just be truncated when checkpoints occur, since in full recovery we need to keep that log around until we've backed it up. Otherwise we couldn't restore to points in time.
So, in full recovery, if you fill up the transaction log, SQL Server can't mark any of the space as reusable until you've backed it up. In that situation, LOG_BACKUP will be the wait description.
A good read on managing transaction logs can be found here http://www.red-gate.com/community/books/sql-server-transaction-log-management
I hope this helps!
May 1, 2015 at 3:44 pm
a_ud (5/1/2015)
-The DB model was in Full recovery mode, log_reuse_wait_desc: LOG_BACKUP (I take this means the TLOG has been backed up? and not that is waiting for it.
No, that means that the DB needs a log backup before the log can be reused.
Please get and read the book Jacob recommended.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2015 at 1:26 am
This was removed by the editor as SPAM
December 17, 2015 at 2:02 am
jacksonandrew321 (12/17/2015)
Try to run the below Statement:DBCC SHRINKFILE(yourdbname_log, 1)
BACKUP LOG yourdbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(yourdbname_log, 1)
GO
Beside being appallingly bad advice (shrinking a log to 0 just forces SQL to spend a lot of time and resources growing it back to a useful size, probably creating way too many VLFs in the process), it doesn't work.
Msg 155, Level 15, State 1, Line 2
'TRUNCATE_ONLY' is not a recognized BACKUP option.
Please, don't do this ever.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply