May 29, 2008 at 8:01 am
I have a SQL 2K5 Enterprise SP2 server running in a 4-node cluster with 1800+ databases on it.
Server has 3 Maintenance plans.
1. Full Database Backups - All Databases 8pm nightly
2. DataBase Maintenance (Index reorg, check for errors) - All Databases 10am Saturday
3. Transaction Log Backups - All Databases Noon daily
I noticed that plan #3 was failing with an error message that a specific database had not had a full backup. It had that same error for two days running that the Database had not had a full backup yet and so it could not perform a log backup(I could understand one because if it was a new database, then it might have been created before noon but after the 8pm nightly backup).
I checked plan #1 and it was set to backup all databases, I checked the folder that the backups are set to back up to and there was no backup for the specific database. The next day it was the same. I then opened the maintenance plan, and changed it from all databases to select specific databases, and then changed it back to all databases and saved it (which took at least 5 minutes). This morning, for the first time, I have a backup of the said database.
Is this normal for a maintenance plan set to process "All Databases"? I can't seem to find anything related via a google search... though I'm thinking my google-fu is getting rusty or something.
May 29, 2008 at 8:19 am
All user databases should pick them all up. It's possible that "all" is resolved at design time and picks up specific databases, but I would have thought that was fixed in SP2. I might add a new db, and then run a trace of job 1 to see if it's really ignoring those databases. I thought it ran a query against sys.databases when it executed.
May 29, 2008 at 11:19 am
I have done a trace on this. I have a Maintenance Plans that is doing a full backup of all user databases. I'm on version 9.00.3054.00. This is what is happening at runtime.
SELECT dtb.name AS [Name] FROM master.sys.databases AS dtb WHERE (dtb.database_id>4)
So I think that there is something else going on in your environment. Could it be that a database has been created, dropped and then recreated? (It could be that you could see these things in the default trace.)
I think that it's very useful to log all DDL events to a table. Then you're always able to see what has happened when. I could recommend using Event Notifications for this.
1800 is really a lot of databases. Here's some thoughts about your Maintenance Plans. Do you need to do a full backup of all databases daily? Have you considered adding differential backups to your backup and restore strategy? How about hourly transaction log backups to reduce the potential loss of data?
With this number of databases it's also a question about when you're deleting the old backup files. You could do a backup of all databases and then on success delete the old backup files. (If you have a Maintenance Plan with a Back Up Database Task and a Maintenance Cleanup Task then it works like this.)
You could also do a backup of the first database and then on success delete its old backup files. Then you could continue to the second database and so on. (My backup stored procedure DatabaseBackup works like this. http://ola.hallengren.com/sql-server-backup.html)
Ola Hallengren
May 29, 2008 at 1:25 pm
Ola Hallengren (5/29/2008)
I have done a trace on this. I have a Maintenance Plans that is doing a full backup of all user databases. I'm on version 9.00.3054.00. This is what is happening at runtime.SELECT dtb.name AS [Name] FROM master.sys.databases AS dtb WHERE (dtb.database_id>4)
So I think that there is something else going on in your environment. Could it be that a database has been created, dropped and then recreated? (It could be that you could see these things in the default trace.)
No, these databases are hosted for our clients... That isn't happening.
Ola Hallengren (5/29/2008)
I think that it's very useful to log all DDL events to a table. Then you're always able to see what has happened when. I could recommend using Event Notifications for this.1800 is really a lot of databases. Here's some thoughts about your Maintenance Plans. Do you need to do a full backup of all databases daily? Have you considered adding differential backups to your backup and restore strategy? How about hourly transaction log backups to reduce the potential loss of data?
We are required to ship a full backup off site on a daily basis. I also agree about the large number of databases... There's actually two seperate instances each with 1800 or so databases on them. The cluster handles single server loss, and the SAN covers disk issues. The total disk volume of the databases is about 200GB.
Ola Hallengren (5/29/2008)
With this number of databases it's also a question about when you're deleting the old backup files. You could do a backup of all databases and then on success delete the old backup files. (If you have a Maintenance Plan with a Back Up Database Task and a Maintenance Cleanup Task then it works like this.)
The full database backup maintenance plan runs something like this.
1. Maintenance Cleanup Task (Delete all database backups from disk location)
2. Backup Databases via full backups to Disk Location
3. Cleanup History Task, purge all history older than 4 weeks.
Ola Hallengren (5/29/2008)
You could also do a backup of the first database and then on success delete its old backup files. Then you could continue to the second database and so on. (My backup stored procedure DatabaseBackup works like this. http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html)Ola Hallengren
I agree... normally I'd have written an SP to manage this too, but it's a management decision to use Maint Plans because they're easier to read and understand for people who've basically been through the MS SQL 2k5 Admin/setup class. They also tend to have easier to read history and logs.
May 29, 2008 at 1:59 pm
Thanks, mtassin.
>The cluster handles single server loss, and the SAN covers disk issues.
You could still get a corrupt database if you're unlucky. Then it could be good to have more frequent log backups.
>The full database backup maintenance plan runs something like this.
Isn't it a little risky to delete all backups from disk before doing new backups? I like to always have the latest good backup on disk, no matter what happens. I think that even if you have backups on tape too, it's often faster to do a restore from disk.
> agree... normally I'd have written an SP to manage this too, but it's a management decision to use Maint Plans because they're easier to read and understand for people who've basically been through the MS SQL 2k5 Admin/setup class. They also tend to have easier to read history and logs.
Sure, the Maintenance Plans are easy to use. However considering their history of bugs in SQL Server 2005, I'm not sure if they're the most reliable solution.
Ola Hallengren
May 29, 2008 at 2:32 pm
Ola Hallengren (5/29/2008)
Sure, the Maintenance Plans are easy to use. However considering their history of bugs in SQL Server 2005, I'm not sure if they're the most reliable solution.Ola Hallengren
Can you provide links to the appropriate bugs in them? Just curious, I'm way more familiar with the ins and outs of SQL 2K, not 2K5, and I was unware of Maint Plan bugs.
May 29, 2008 at 3:48 pm
I have experienced a number of issues with the Maintenance Plans but I think that most of them have been fixed in SP2. You have to install the hotfix in kb article 934458.
http://support.microsoft.com/kb/934458
http://support.microsoft.com/kb/933508
http://support.microsoft.com/kb/922651
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262295
Ola Hallengren
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply