SQL Log and ACTIVE TRANSACTIONS
I have a Web Server with SQL 2008 running a simu开发者_开发知识库lated 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:
- 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....
IN THE MEANTIME... how can I eradicate this issue?? I have tried SHRINKING and I have tried backuping up the Db...
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)...
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.
精彩评论