Sybase ASE / MS SQL Server: Transaction that fills up the log
ASE, Databases, Microsoft, SQL Server, Sybase Add commentsSay you have a query that easily fills up the transaction log of the Sybase ASE server / Microsoft SQL Server:
How would you break up the transaction so it doesn’t fill up the log? There are several ways to do it, but I’ll just cover two of them:
- Break up the transaction based on the actual data.
DELETE FROM rep_queues_archive WHERE sample_date < DATEADD(wk, -52, GETDATE()DELETE FROM rep_queues_archive WHERE sample_date < DATEADD(wk, -51, GETDATE())
- Break up the transaction based on the number of rows affected.
DECLARE @dbName VARCHAR(50)
SELECT @dbName = DB_NAME()
SET rowcount 1000
SELECT @rows_affected = 1
WHILE @rows_affected > 0
BEGIN
DELETE FROM rep_queues_archive WHERE sample_date < DATEADD(wk, -1, GETDATE())
SELECT @rows_affected = @@ROWCOUNT
IF @@ERROR = 0
BEGIN
COMMIT TRAN
DUMP TRAN @dbName WITH TRUNCATE ONLY
END
ELSE
ROLLBACK TRAN
END
SET rowcount 0
Of course, if you are performing incremental backups, you will want to modify the dump tran @dbName with truncate only line to read something like dump tran @dbName to “/sybdumps/mydb_tran.dmp”. Modify to suit your naming standard.
Recent Comments