August 21, 2002 at 7:42 am
We upgraded one of our servers from 7 to 2000 yesterday. This server had a maintenance plan on it that included integrity checks on master and msdb with indexes and the repair option selected. This job ran flawlessly under sql 7. Now, it is failing under 2000 complaining that the database must be in single-user mode (the integrity check is already supposed to do that!). i deleted and recreated the maint plan under 2000 and still have the problem.
I run this exact same setup on a sql 7 server without problems. Anyone else run into this?
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
August 22, 2002 at 8:17 am
anyone?
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
August 22, 2002 at 10:17 am
quote:
Sorry I don't reminder the details, but it seems I remember reading that under SQL 2K the repair option required single user where it did not under SQL 7.May I suggest you make a trial run with the repair option off and see what happens.
solart
Actually, from my understanding, the repair option requires single-user mode in both versions. And, according to the SQL Server documentation, the integrity checker in the maintenance plans is supposed to put the database in single-user mode. otherwirse it would be pretty stupid to have that as an option when setting up a maintenance plan.
Yes, with the option turned off, it works fine. But I am trying to find out if there is a fix or something since the interface allows this switch.
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
August 22, 2002 at 1:11 pm
quote:
Refer to KB Q290602This says there is a known bug when system databases are included in the maintenance plan (and explains why).
My assumption is that if you create a new maintenance plan without the system databases, then your maintenance plan will work.
At least this may be worth a try.
Of course this implies we are going to have to figure out how to deal with the system databases.
solart Q264154 may be of interest.
Thanks for the KB #. Checked it out and it is EXACTLY my problem. Geez. Verified as a SQL 2000 bug in Feb of 2001 and still not fixed. Real high on their priority list i see!
As far as running this on your own, it is tricky about how you have to set a database to single-user mode with EM and QA. You can connect to the database via QA and set it to single-user mode using sp_dboption and then run the DBCC with the REPAIR parm. Or, you can connect via EM and set the option in the UI. But, then you must DISCONNECT in EM or it will persist a connection. You cannot simply click on another database on the server in EM. Even though a connection will not show to the database in Current Activity, it is still there and you will not be able to open a QA session against it. You will HAVE to actually DISCONNECT before you will be able to connect via QA and run the DBCC with REPAIR.
Took me a bit to figure out the sequence!
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
August 22, 2002 at 1:23 pm
One of the rules of thumb we follow where I work is if there are minor issues, we want a DBA looking at it, we don't SQL Server trying to automatically repair it. So we generally leave the repair minor errors unchecked. If SQL Server detects an issue, you'll know about it. Especially if you have your alerts set up in addition to the maintenance plan.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 22, 2002 at 1:33 pm
quote:
One of the rules of thumb we follow where I work is if there are minor issues, we want a DBA looking at it, we don't SQL Server trying to automatically repair it. So we generally leave the repair minor errors unchecked. If SQL Server detects an issue, you'll know about it. Especially if you have your alerts set up in addition to the maintenance plan.K. Brian Kelley
I agree. The killer for us is a bit double-sided. It started with the fact that we recently converted from Exchange to Notes. I have yet to get the client setup to be able to mail from SQL since the conversion. That causes us to monitor the results of these jobs every morning.
Second, we convert to SQL 2000 from 7 and suddenly we start getting nice, big red x's on the integrity checks and spend time trying to figure out what is going on and what our conversion corrupted in MASTER and MSDB!
It isn't the fist time I have said that it is nice that it turned out to be a bug versus the alternative!
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
August 22, 2002 at 2:28 pm
The only way MS support Lotus for SQL Server is as an SMTP server and I know that blows. I know very few people who use SQL Server with Lotus Notes who are satisfied. I haven't tried it, but if the Lotus Notes SMTP angle isn't viable, perhaps you could set up a relay such as through an IIS server with SMTP running.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply