SQL Log and ACTIVE TRANSACTIONS

Question

I have a Web Server with SQL 2008 running a simulated SQL 2005 db, and I have a local SQL 2005 db for testing environment.

This causes me to use scripts for backup/restoring data for testing as 2008 server backups do not restore to a 2005 server.

When I run this SQL Query to reduce the size of a Table on my production Web SQL Server (2008)

 DELETE FROM TickersDay
 WHERE (DATEDIFF(day, TickersDay.[date], GETDATE()) >= 8)
 GO

I get this message:

 Msg 9002, Level 17, State 4, Line 3
 The transaction log for database 'VTNET' is full. To find out why space in the log
 cannot be reused, see the log_reuse_wait_desc column in sys.databases

It comes up when I publish scripts also at times.

When I run this SQL Command I get the following Result:

 SELECT [name], recovery_model_desc, log_reuse_wait_desc
 FROM sys.databases

RESULT:

 [name]      recovery_model_desc       log_reuse_wait_desc

 VTNET  SIMPLE                     ACTIVE_TRANSACTION

Here are my questions and issues:

1. I get it.. I have a transaction statement that needs a Rollback Command

< if @@Trancount > 0 Rollback > .. but I have 100 stored procedures so before I do that....

1. IN THE MEANTIME... how can I eradicate this issue?? I have tried SHRINKING and I have tried backuping up the Db...

2. As you can see, it is in SIMPLE mode... I do not have any idea how to backup a LOG ONLY file... (have not found how to do that)...

Answer 1

You might be able to get around this issue simply by getting SQL NOT to process the entire table by use the index on only the dates required to be deleted. Rephrase it to be index friendly

DELETE FROM TickersDay
WHERE TickersDay.[date] <= DATEADD(day, -8, GETDATE())
GO

If you run this frequently enough (at least daily) then it only has to process 1/9th or less via an index on TickersDay([Date]) instead of having to go through the entire table if you use DATEDIFF on the field.

If that still causes this:

The transaction log for database 'VTNET' is full

You really need to increase the Log size because I suspect it is not set to autogrow and is not big enough for this operation. Either that or start looking at batching the deletes (again assuming you have an index on date, so this efficiently targets only the 100 rows), e.g.

DELETE TOP (100) FROM TickersDay
WHERE TickersDay.[date] <= DATEADD(day, -8, GETDATE())
GO

You can either loop it (while @@rowcount > 0) or just schedule it more frequently as a trickling background delete.