Check Data and Index Linkage FAILED

Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were checking and trying to
>> repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional parameters then the DB must
>> be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You can then try to repair
>> any single table at a later data during your maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
--
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were
>> checking and trying to repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional
>> parameters then the DB must be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You
>> can then try to repair any single table at a later data during your
>> maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>> From your response, I assumed that it's the Integrity tab that I
>> should modify. So I unchecked "Attempt to repair any minor
>> problems". Is that correct or am I way off. Thanks again.
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>