January 7, 2012 at 4:14 am
Hi,
In sql server 2000, this procedure was working fine there is no issues. as per database configuration recovery model was SIMPLE.
ALTER procedure [invt].[invt_DELETION]
AS
delete from Invt.Invt_MODEMS where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt.Invt_INTERFACE where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_Alarms where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_chassis_status where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_PUMPING_LEVEL where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_DHCP where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_INIT where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_OFFLINE where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_IPADD where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_IPADD_D where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
delete from Invt_TRAFFIC_AN where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
after migrated SQL server 2008, procedure is failed due to as blow error.
Message
Executed as user: invt. The transaction log for database 'inventory' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed.
I taken TLog backup with 2 times, again execute this, then same error.
could anyone help me out this error..
NOTHING - log_reuse_wait_desc column in sys.databases
thanks
ananda
January 7, 2012 at 4:43 am
How big is your database (data & log files)? What’s the growth pattern?
January 7, 2012 at 6:44 am
If the recovery model is Simple, then add a checkpoint between each delete.
Are you sure that the recovery model is simple though? Because if it is, you wouldn't have been able to take log backups (and you said that you took two). Please double-check the recovery model and make sure it is what it's supposed to be.
Edit: That the log wait is nothing now is not significant. There's nothing now (after the procedure failed and after you took the log backups) holding the log active, it doesn't mean there wasn't something holding the log active at the point that the procedure failed.
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
January 8, 2012 at 9:09 pm
Database setting is recovery model 'SIMPLE', then executed this procedure that failed due to Tlog full.. after that I changed recovery model to 'FULL' then taken two times for TLog backup.. then Log Reuse was 'NOTHING' after taken TLog backups...
If database recovery model in 'SIMPLE', how can add the 'CHECKPOINT' this procedure, Is it correct as below example.?
delete from Invt.Invt_MODEMS where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
go
checkpoint
go
delete from Invt.Invt_INTERFACE where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
go
checkpoint
go
delete from Invt_Alarms where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
go
checkpoint
thanks
ananda
January 9, 2012 at 2:18 am
ananda.murugesan (1/8/2012)
Database setting is recovery model 'SIMPLE', then executed this procedure that failed due to Tlog full.. after that I changed recovery model to 'FULL' then taken two times for TLog backup..
Err, why would you do that? Makes no sense to switch to full recovery to clear the log when it's automatically cleared on a regular basis in simple recovery.
delete from Invt.Invt_MODEMS where entry_dt <= (SELECT DATEADD(month, -1, getdate()))
go
checkpoint
You don't need all the GOs, otherwise yes.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply