asked on

Maintenance plan problems

Hello experts, I have problems with backup maintenance plans of sqlserver 2000. I have a plan for user dbs and a plan for system dbs. The one of user dbs has 1 db of 32G and a db of 15G. The idea is that the MP remove files older than 3 hours so that only one backup copy remains on disk. This is due space issues on this server. They don't want to invest in this since they will be using another application soon (I hope). The MP does not remove the older copies of the biggest db and we can problems with the backup of the db of 15G. What I di now is, I made a seperate MP for each big database adn so seperate them from the others and let it run at different times. But can someone tell me why sqlserver 2000 does this? Why it does not remove the files like it is told to do? The server runs ms win 2003.

Kruger_monkey

Does the following apply to your instance?

http://www.bidn.com/blogs/BradSchacht/ssis/249/sql-maintenance-plan-doesn-t-delete-backups

sharscho

ASKER

Nooop I wish it applied then I would have a solution for this issue. I don't have a . infront of the text box. I have just BAK in the text box. It does remove the backups for the other databases but for the one of 32G it does not. any other suggestions??

ASKER CERTIFIED SOLUTION

Kruger_monkey

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sharscho

ASKER

Ok you have a point here. I went and look at the commvault backup application of hitachi data protection suite and I see that sometimes backups of this server end after the the sqlserver backup time. I looked at the error log also and it I got the following:
2010-03-28 03:02:24.45 backup Database backed up: Database: GMS_XEI, creation date(time): 2008/05/15(18:26:28), pages dumped: 1564, first LSN: 544:431:1, last LSN: 544:434:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'e:\MSSQL\BACKUP\GMS_XEI\GMS_XEI_db_201003280302.BAK'}).
2010-03-28 03:07:02.03 spid65 BackupMedium::ReportIoError: write failure on backup device 'e:\MSSQL\BACKUP\GMSArchiefHist\GMSArchiefHist_db_201003280302.BAK'. Operating system error 112(error not found).
2010-03-28 03:07:02.03 spid65 Internal I/O request 0x356EAD90: Op: Write, pBuffer: 0x08C60000, Size: 983040, Position: 14501811712, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x60601600, OffsetHigh: 0x3, m_buf: 0x08C60000, m_len: 983040, m_actualBytes: 0, m_errcode: 112, BackupFile: e:\MSSQL\BACKUP\GMSArchiefHist\GMSArchiefHist_db_201003280302.BAK
2010-03-28 03:07:02.03 backup BACKUP failed to complete the command BACKUP DATABASE [GMSArchiefHist] TO DISK = N'e:\MSSQL\BACKUP\GMSArchiefHist\GMSArchiefHist_db_201003280302.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
2010-03-28 03:07:05.99 backup Database backed up: Database: GMSTMPImport, creation date(time): 2008/01/24(08:39:13), pages dumped: 4524, first LSN: 7334:71878:1, last LSN: 7334:71880:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'e:\MSSQL\BACKUP\GMSTMPImport\GMSTMPImport_db_201003280307.BAK'}).
2010-03-28 03:07:08.21 backup Database backed up: Database: HPSREP, creation date(time): 2008/01/15(09:50:29), pages dumped: 8651, first LSN: 48:30569:1, last LSN: 48:30571:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'e:\MSSQL\BACKUP\HPSREP\HPSREP_db_201003280307.BAK'}).

I did move the sqlserver backup time further so that it starts after the times I saw in the commvault application. Adn I will see how it goes tonight and tomorrow night. I will post my findings. Thank you very much for your advices.

Kruger_monkey

You're welcome, hopefully that is the cause of the problem.