Transactions logs not being removed

[BudT]

I have a maintenance plan which runs each night that backs up the database and the transaction file. Both are setup to remove the older files after 7 days. The BAK files are being removed, BUT the TRN files are not, they just keep accumulating. I have removed the maintenace plan and recreated it but it still doing the same thing. I checked the Microsoft web site but it had no mention of this problem. Has anyone seen this before and how can this be corrected?

 

[mrdenny]

Does the plan log say anything?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Are the transaction logs being created by the maintenance plan? Or are they created by another process?

Do they use the naming convention of MyDB_tlog_YYYYmmddHHMM.TRN? If they are named differently, that might be your problem. Or if the names got changed since the log backups were made. SQL is very literal. If the name is different than what it was expecting, it won't delete or clean up after itself.

What edition/service pack of SQL are you running?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[BudT]

No, there is nothing in the logs.

And no, the names have not been changed. They are the standard names.

They are being created and by the maintenance plan.

SQL 2000 SP 4 under Windows 2000 SP 4.

 

[Catadmin]

They haven't been moved from their original directory, have they?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[BudT]

No they are in the original directory where the maintenance plan created them.

 

[Catadmin]

Try running SQL Profiler when the maintenence job runs next. That way you can see what it's doing.

Also, the account that is running the job may not have FULL Control permissions on that drive share. Right click the parent folders and check the permissions to see if the domain account running the job is listed on the permissions/Security list. If not, you'll have to contact your network admin.

If your job is being run by the SQL Server Agent service and that service is using the local built-in Admin account, this won't be your problem. If the SSA service is using a domain user account, though, it might be.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[BudT]

Job is being run by the SQL Server Agent, so it is not a permissions issue. In fact the BAK and the TRN files are in the same directory and as I said before the BAK files are being removed correctly.

I have never run the SQL Profiler before and will try that once I figure it out. Problem is that the job runs at 2:00 in the morning.

 

[SQLBill]

Double check the maintenance plan....go to the Transaction Log backup tab...make sure THAT step is set to remove old backups. Sometimes people think that setting it on the Complete backup tab sets it for both. Lastly, make sure the plan is looking for the correct backup extension on the Transaction Log tab. I think it defaults to TRN.

-SQLBill

Posting advice: FAQ481-4875