Truncation of log during execution of a script.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Truncation of log during execution of a script.

Author

Topic

SQLJunior
Starting Member

34 Posts

Posted - 2007-05-17 : 04:57:06

Hi,

I need to delete entries from a couple of tables in a database, because these tables are quite big, the transaction log is continuously growing and therefore fills up the hard disk. I need to truncate the transaction log at intervals. A sample of my script below:

USE Mediation

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
COMMIT
BEGIN TRANSACTION
DELETE tblAlarmLog
WHERE CreateDT <= dateadd(yy,-1,getdate())
GO

DELETE tblBackPlanes
WHERE CreateDT <= dateadd(yy,-1,getdate())
GO

DELETE tblCPU
WHERE CreateDT <= dateadd(yy,-1,getdate())
GO

Delete tblClients
WHERE pbxid IN (Select c.pbxid from tblClients AS c left join tblpbx AS p on c.pbxid = p.pbxid
WHERE p.LastDialupDT <= dateadd(yy,-1,getdate()))
GO
COMMIT TRANSACTION

BEGIN TRAN
BACKUP LOG Mediation
WITH TRUNCATE_ONLY
GO
COMMIT TRAN

BEGIN TRAN
DBCC SHRINKFILE (Mediation_Log, 100)
GO
COMMIT TRAN

This is just the first part of my script, more tables are queried after the COMMIT.
I am using the Simple database model so the log should be automatically truncated on checkpoint. Is a COMMIT like a checkpoint?

Anyway, I did some research and found the way to truncate the log is by backing up the LOG. And then using DBCC SHRINKFILE to shrink the physical file on disk.
I receive the errors below during execution:

Server: Msg 3021, Level 16, State 1, Line 4
Cannot perform a backup or restore operation within a transaction.
Server: Msg 3013, Level 16, State 1, Line 4
BACKUP LOG is terminating abnormally.
Server: Msg 8920, Level 16, State 2, Line 4
Cannot perform a ShrinkFile operation inside a user transaction. Terminate the transaction and reissue the statement.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is a long running query which will run over night, is there any

way to prevent the transaction log from growing too large?

Thanking you...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 05:51:06

try your deletes in batches.
this will be easier for you transaction log as it will need less space.
you will however need to change your logic to handle batches.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SQLJunior
Starting Member

34 Posts

Posted - 2007-05-17 : 08:27:00

Hi, thnx for the reply.
I am fairly new to this and i have not worked with sql batches before, by batches did you mean creating separate stored procedures?
I have done the following:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.SP_Backup ******/
CREATE PROCEDURE SP_Backup AS
BACKUP LOG Mediation
WITH TRUNCATE_ONLY
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

that's my backup stored procedure

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.SP_Delete ******/
CREATE PROCEDURE SP_Delete AS

DELETE tblAlarmLog
WHERE CreateDT <= dateadd(yy,-1,getdate())

DELETE tblBackPlanes
WHERE CreateDT <= dateadd(yy,-1,getdate())

DELETE tblCPU
WHERE CreateDT <= dateadd(yy,-1,getdate())

Delete tblClients
WHERE pbxid IN (Select c.pbxid from tblClients AS c left join tblpbx AS p on c.pbxid = p.pbxid
WHERE p.LastDialupDT >= dateadd(yy,-1,getdate()))
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

that's one of the deletes

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.SP_Delete_Stats AM ******/
CREATE PROCEDURE SP_Delete_Stats AS
DELETE tblStats
WHERE CreateDT <= dateadd(yy,-1,getdate())
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

and that's another.

Do i then just execute it as follows, with backups in between?:

Exec SP_Delete
GO

Exec SP_backup
GO

Exec SP_Delete_stats
GO

Exec SP_Backup
GO

I could of course parameterize the stored procedure but this is just for testing purposes.

The query is busy executing at the moment, the table it is busy with has 56 million entries, so needless to say, it is going to take a while. Could you plz in the meantime, maybe just let me know if i'm heading in the right direction?

your help would be appreciated, thanx!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 08:30:41

no i didn't mean separate stored procedures.
i meant something like this:


set rowcount 5000 -- or any other number you wish. this is the size of the batch
while exists(select * from tblBackPlanes WHERE CreateDT <= dateadd(yy,-1,getdate())
begin
    DELETE tblBackPlanes
    WHERE CreateDT <= dateadd(yy,-1,getdate())
end 

set rowcount 0 -- set the rowcount back to original setting of all rows.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SQLJunior
Starting Member

34 Posts

Posted - 2007-05-17 : 08:40:17

yeah ok... kewl thanx!

have a nice day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources