• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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.
0
sharscho
Asked:
sharscho
  • 3
  • 2
1 Solution
 
Kruger_monkeyCommented:
0
 
sharschoAuthor Commented:
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??
0
 
Kruger_monkeyCommented:
2 things I can think of.  If it is the bigger database only, what is the space like on the box that it is on.  I believe that SQL needs a certain amount of space to delete the database, possibly there is not enough space for the large database and as such it fails.

Also, what about other backups/applications running accessing those files at the same time?  

See the following thread that has a similar discussion in it, the 2 points above get raised in it.

http://www.sqlservercentral.com/Forums/Topic293491-5-1.aspx
0
 
sharschoAuthor Commented:
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.
0
 
Kruger_monkeyCommented:
You're welcome, hopefully that is the cause of the problem.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now