Is there a limit on the number of databases a maintenance plan can back up?!

Question

Is there a limit on the number of databases a maintenance plan can back up?!

WebTechie

SSChampion

Points: 11405
More actions
June 30, 2010 at 6:17 am

#223352

Hello,
We have a major vendor application. It creates a database for each function or new usage that our company has. Currently we have added 108 functions which equates to 108 databases.
We had a major failure the other day on one of the servers. This caused SQK Server to shut down and wouldn't open. It took all day, but we finally got SQL Server running again. I then restored the last good backup. I then went to check the transaction logs and some of my transaction logs weren't there. In fact, it had been a couple of days since the last transaction log backup of the databases.
I checked the maintenance plan and it was set to backup all user databases. When I checked the error log, I see where the maintenance plan had a failure. I can see the TSQL it tried to run. I only see 30 or so databases in the SQL. Not all the 108 databases were in the TSQL scripting.
Is there a limit to the number of databases that a maintenance plan will handle? How can you have the maintenance plan set to back up all user databases and only some transaction logs get backed up?
Does anyone have any insight or can point me in the right troubleshooting direction?
Thanks,
Tony
This happened on a Windows 2003 Server with SQL Server 2008 Enterprise edition. It is also on a clustered node.
Things will work out. Get back up, change some parameters and recode.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the only thing I can think of off hand is whether it was possible the server ran out of disk space after backing up the 30th database....when it moved to db31, with no space left, the rest of the job failed?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Were all 108 databases set to full recovery model?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WebTechie SSChampion Points: 11405 More actions · Answer 3

Yes and that is the thing.

That was one of the first things I checked. They were all set to full!

Thanks though for the feedback!

Tony

Things will work out. Get back up, change some parameters and recode.

WebTechie SSChampion Points: 11405 More actions · Answer 4

Thanks Lowell for the feedback. I appreciate.

But we had backups of the databases.

Now the SAN did have an error, which is why SQL crashed in the first place.

I was able to restore to the last backup. But I was not able to bring it back to a point in time because there weren't any transaction logs for all the databases.

I didn't want some databases to be a one date and others to be at another date. So I recommended to management and they concurred to bring them all back to the time of the database backups.

I am wondering if there wasn't a problem going on for 3 days. That is how many days some databases were missing in the transaction logs.

Thanks for the thought!

Tony

Things will work out. Get back up, change some parameters and recode.