Transaction log backups wont delete

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Transaction log backups wont delete

Author

Topic

Mmats
Starting Member

47 Posts

Posted - 2004-06-24 : 10:31:17

I made a maintenance plan and set up db/tl backups and set it to delete backups older than a week. The DB backups will delete but the TL wont. This is the query its using for the TL backup job:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID AE3447AA-C2B8-4F3B-A8EF-3429C5CAA2E6 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 1WEEKS -BkExt "TRN"'

Mmats
Starting Member

47 Posts

Posted - 2004-06-24 : 14:14:21

forgot to add im using sql server 2000 sp3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-24 : 14:19:08

Most people here do not use the maintenance plans. We use our own custom stored procedures. To see mine:

http://weblogs.sqlteam.com/tarad

They are called isp_Backup for full backups and isp_Backup_TLog for transaction log backups.

I've since modified mine to include a retention option. They currently only save 2 days of backups on disk. The new version allows you to input the number of days to retain. To make the modifications, the inputs would be:

(@Path VARCHAR(100), @dbType VARCHAR(6), @Retention INT = 2)

And this line:
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - 2

Becomes:
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @Retention

I would strongly suggest not using the maintenance plans for a couple of reasons. First, you gain no knowledge of what actually needs to be done when you use these types of things. Second, the error messages typically do not give enough information to troubleshoot. You usually have to go to the SQL Server Error Log for more information.

Tara

Mmats
Starting Member

47 Posts

Posted - 2004-06-25 : 16:26:35

thanks for the advice

Kristen
Test

22859 Posts

Posted - 2004-06-26 : 01:09:58

I had several MSDE installations on Laptops that had seemingly insurmountable problems with Maintenance Wizzard, so I gave up and learned to do it in SQL instead ...

Probably not what you want to hear, but.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources