Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2003
    Posts
    27

    Unanswered: Maintenance utility acting on it's own???

    Hello

    I've set up a maintenance plan for one of the SQL servers in the network I'm administering. When I checked the logs this morning, I was surprised to find that all backups had failed. I was about to throw my computer out the window, when I thought about actually checking the folders where the backup files are supposed to be. I was even more surprised to find that a succesful backup actually had taken place! It seems that this backup had occured about 10 minutes later than the one that failed. When I checked the jobs, they all seemed to have failed (Last Run Status was failed).

    How is this possible??? Is SQL Server running it's own backups, or is it possible that there's some backup job somewhere that is listed with the jobs for the server???

    Any help and suggestions is very welcome.

    MNJ

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A lot of things can cause a job to fail, and it may fail partway through so you would see backups for many of your databases (or even all of your databases if it failed on a step after the backup).

    Check to see what step you job failed on, and it is good practice to log the output from your backup job. Editing the step(s) that may cause trouble, go to the Advanced tab, and set the Output file location. You can also view previous output from here.

    blindman

  3. #3
    Join Date
    Aug 2003
    Posts
    27
    Yea, it seems that some of the backups failed because the file sizes were too big, and therefore failed to back up across a network share. I still haven't figured out why those databases have been backed up anyway though, because they're all set to no retries and the log doesn't mention anything about a succesful backup of those DBs.

    Now I've decided to do local backups instead.

    MNJ

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by kilobyte
    Yea, it seems that some of the backups failed because the file sizes were too big, and therefore failed to back up across a network share. I still haven't figured out why those databases have been backed up anyway though, because they're all set to no retries and the log doesn't mention anything about a succesful backup of those DBs.

    Now I've decided to do local backups instead.

    MNJ
    I found in my situation that the best thing to do is break out into 4 maint plans. I save them on the local disk and have an automatic delete of them after X number of days.

    [list=1][*]Get all the system databases in one plan w/ nightly backups. You can just default this one.[*]Get the critical and rapidly changing application database (the ones using transaction logs) as separate maint plan. The ones you may have to rollback to a point in time.[*]Get the semi-static databases as a third. Low users or mostly lookup data.[*]The 4th is for our WebSweeper DB. The thing is huge and we don't have a significant worry if we lose some of the web access info.[/list=1]

    I then depend on the nightly diferentials and weekly fulls to take care of long term recovery and use what is on the local disk for short term. At one point in time I did try to save them to a network location, but I consistently (at least once a week) would lose connectivity to that drive and my b/u were dead.

    Another option, is publishing and distribution. If you have another server off-site or can get one with a decent bandwidth connection, you can publish your apps database(s) nightly to the other server. We had (it was only a P200 with 20GB HD and 1GB of RAM ) and will soon have again a SQL box at our recovery site that does nothing but tick over and catches the databases nightly. That makes life a lot easier from recovery planning.

    I have found that most open file agents of b/u software are crap, in my opinion. If you are running out of room on your server's disk, take a look at the plans above. Cut transaction logs for only the most critical and changing databases. And in the maint plans I have described the backups for the critical databases are kept 4 days, but the transaction logs are only kept for 2 days. If need I can always go to tape earlier logs. But most times a major point-in-time restore is going to be less than 48 hours. I know the exception is on a Monday, but how often does it happen? I've had 4 PIT restores in the past 3 years and of those it has generally been to 6AM the current day.

    The semi-static databases are kept local for 2 days and then are cycled out. The system databases, I just back up for grins. If the SQL SW crashes that hard, you'll probably just re-install SQL and then reattach your DB's in place. If it was the WinXX software, same thing.

    Also when you set up your maint plans, have them do data integrity and DB shrink procedures daily. That will probably buy some disk space.

    And sometimes, you just have to tell the boss "We need some more drives for this POS server." If he doesn't listen, just smile during the autopsy after the crash, and say "I told you so."

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    kilobyte: What do you go by saying that a successful backup actually occurred? A failed backup will leave a backup file on disk that cannot be used for restore operation. Can you try to do a restore to a new database from that backup file if space permits?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    jimpen: A very compelling plan! BTW, do you really believe that system databases can be omitted from maintenance plans? Also, I don't think it's a good idea to just shrink logs of all databases accross the board. Those with Full Recovery mode should have log device pre-allocated to avoid growth overhead during business hours.

  7. #7
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by rdjabarov
    jimpen: A very compelling plan! BTW, do you really believe that system databases can be omitted from maintenance plans? Also, I don't think it's a good idea to just shrink logs of all databases accross the board. Those with Full Recovery mode should have log device pre-allocated to avoid growth overhead during business hours.
    As I said, I do the system DBs for grins. I have kept our server very vanilla. There is only one stored procedure, and a table that I would like to retain after a crash. And even then, that table's data is kept elsewhere. I also changed the model a little for standards sake (min 50MB log file). If it is a minor crash, SQL server will generally either recover itself, or need a little work from the SysAdm and it will come up. But I've had mine go down on Apps install so bad that I had MS's support in one ear and a consultant in the other. After a day, they said just reinstall and reattach the other databases. Nothing could be recovered from the master, model, etc.

    As for the log files, I already have them set up for a larger minimum size where needed. The rollback DBs get their transactions backed up and then are truncated. The slow database are just automatic trunc on checkpoint so they never really grow.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    wow, that's pretty brave! Good luck!

  9. #9
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Question

    What's brave about it?

    As long as the tape backups are working, even if someone walked in and ran an axe through the hard drives the worst loss is about 24 hours of data.

    We are primarily a daylight business - about 7A-7P Mon-Fri with some weekend dedicated types.

    If the server crashes at on Sunday at midnight and W2K Server has to be reinstalled as well as the SQL 7, the model, master, msdb and tempdb will all be replaced anyway. It is virtually impossible to restore a master database. Sometime, build a baby server and try it.

    After the reinstall on the same server, if the DB files are still on the volume, you can run a sp_attach_db to get them back online. If you lost the volume and are starting with a blank HD, then you have the files restored from tape to the \mssql\backup directory and then proceed to restore the database to it's original place.

    If someone went bogus on the data and multiplied every table by 5 times then you have a PIT recovery. And if it was done Saturday then you get back to Friday after hours.

    And this is not even considering that you publish the database.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  10. #10
    Join Date
    Aug 2003
    Posts
    27
    Thanks for the help guys, this has turned out to become an interesting discussion of a vital subject. I consider myself a bit of a newbie in this area, so I'm glad about the input.

    About the backups. No, I haven't tried restoring from the files, I just sort assumed they were ok. The file size seemed all right, so I figured they were just fine. The log files produced by the maint. job said something like this:

    'Backup can not be performed on database 'msdb'. This sub task is ignored.'

    , but I'm suspecting this might be about the transaction log backups Those log files are not always as helpful as I'd wish...

    Anyway, I'm now doing local backups now and that seems to work a lot better. Today the log file said succes at the bottom! Except for what seems to be the log file for the transaction log backups. I'll have to delve into that a little deeper.

    Btw, the backup plan I use is the following:
    1. Weekly full and daily incremental backups on tape for major database servers
    2. Daily full backups on disk for all database servers

    I like to have backups on tape in case the office burns down or whatever. I don't differentiate too much between different databases within the same server. The reasons for this are twofold. I don't feel I know enough about it, and I don't feel I have the time. Also, people tend to create new databases as often as they change their underwear here, and that does not make administering the servers any eaiser.

    Again, thanks for the input.

    MNJ

  11. #11
    Join Date
    Aug 2003
    Posts
    27
    Btw, I use Veritas Backup Exec 9.0 and that seems to work just fine with the agents and all. And yes, I have done test restores.

    MNJ

  12. #12
    Join Date
    Aug 2003
    Posts
    27
    Hello again.

    Just wanted to say that backups work fine now that they're being done locally. Except for the transaction log backups which still fail.

    I think this is an unrelated problem though, so I've started a new thread at: http://65.61.175.198/showthread.php?threadid=889573

    Again, any help and comments will be greatly appreciated.

    MNJ

  13. #13
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Thumbs down Some companies are crazy.

    Originally posted by kilobyte
    I don't differentiate too much between different databases within the same server. The reasons for this are twofold. I don't feel I know enough about it, and I don't feel I have the time. Also, people tend to create new databases as often as they change their underwear here, and that does not make administering the servers any eaiser.
    MNJ
    If they are just adding database(s) and developing/testing on your production servers you need to slap some people around . They can and will at some point crash your SQL and/or server with some process that goes wild or access the disk and delete an important file.

    You may need to speak up at some point and make them realize that they need to have test/development servers versus daily production.

    I work for a relatively small company and even we have scraped an older server into using as a test box before we fire it up onto the production SQL server.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  14. #14
    Join Date
    Aug 2003
    Posts
    27

    Re: Some companies are crazy.

    Originally posted by jimpen
    If they are just adding database(s) and developing/testing on your production servers you need to slap some people around . They can and will at some point crash your SQL and/or server with some process that goes wild or access the disk and delete an important file.

    You may need to speak up at some point and make them realize that they need to have test/development servers versus daily production.
    I know, I know. We are in that phase now in fact, trying to have test servers for each production server. There have been times, though, where it's been necessary to change something really quickly, and I am fully aware that it is not a good strategy.

    I've begun thinking about how to come up with a new maintenance plan, and I just might start a new thread on that subject, to get some input from outside.

    Anyways, thanks for the help so far, you've all saved me hours of work!

    MNJ

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    kilobyte: You're getting transaction log backup errors probably because you included databases with simple recovery mode. In one of your posts you mentioned msdb. This database will always start up in simple recovery mode, which will fail the entire step even if the rest of db's got backed up successfully.

    jimpen: you said that "It is virtually impossible to restore a master database." - I have no more comments

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •