Check Database Integrity in Maintenance Plan not working in SQL Server 2005 SP2

I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:

Executed as user: Domain\SqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance Plans\Test Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.

Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.

Thanks

E.G.

I've the same problem.

Here is the details:

Date 16-04-2007 02:00:01
Log Job History (Reindex Plan)

Step ID 1
Server LISSSQL01
Job Name Reindex Plan
Step Name Subplan_1
Duration 01:17:10
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: INTERNAL\lisa-ouadmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 02:00:01 DTExec: The package execution returned DTSER_FAILURE (1). Started: 02:00:01 Finished: 03:17:06 Elapsed: 4624.2 seconds. The package execution failed. The step failed.

Does anyone know how to workarround this?

Regards,

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 Ferguson
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
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...[vbcol=seagreen]
> 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:
|||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...[vbcol=seagreen]
> 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:
|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>

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. :-)
>

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 statemen
t 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 giv
e exact UTL).
If you do check this option, the database indeed need to be in single user m
ode. Main plan tries to
set it to single user, but that will fail if any user is in the database, wh
ich 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 statemen
t 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 Ente
rprise Manager open? I
> don't have the live database extended. I normally work on our test databa
se 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 ca
n
> then try to repair any single table at a later data during your maintenanc
e
> 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 unch
ecked
> "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...[vbcol=seagreen]
> 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. W
hat 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 mo
dify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off.
Thanks again.
> Marc F.
> Ryan wrote:|||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...[vbcol=seagreen]
> 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:|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>

Cheap mans clustering

Hi all,
This question could be posted in several different groups, but this is the
chosen one.
We plan to do High Availability in the coming financial year and have
budgetary constraints.
If I can detail the setup.
The SQL will have transactional replication, snapshot replication, and
merge replication! I cannot for the life of me see how replication can be
viable (or compatible) in a failover or clustering scenario. We will not
have a SAN (too expensive). We will have a NAS, but my understanding is
that will ask for trouble. I thought that for this to work, the databases
would have to be on a SAN.
Furthermore we will not have the budget for a proper SQL clustering
solution, so thrirdparty is likely. XOSoft seems to be the primary
candidate.
Comments are welcome, personally I don't like the sound of it. I would
however appreciate knowig how SQL replication still works on the
cluster/failover scenarios.
Many thx
Paul
Running SQL Server on NAS is no way to achieve high availability! In
most cases NAS isn't even supported and is definitely not recommended,
except maybe for a non-critical, low throughput database. The following
article gives MS's official line on this, my own advice is: don't go
there.
http://support.microsoft.com/kb/304261
Replication is commonly used as a low-budget alternative to clustering,
however you may also want to take a look at Database Mirroring in SQL
Server 2005. Unlike clustering, mirroring doesn't require any special
hardware.
David Portas
SQL Server MVP
|||Oooohhh. Don't do NAS with SQL.
Your are looking for inexpensive? I assume the cost of SQL Clustering or
log shipping is in the SQL Enterprise license and you are going with
Standard. However, You can write your own version of Log shipping using
SQL standard. It's really just coping over the Tlogs to another server and
restoring. Have a DNS entry that abstracts the SQL Server name and failover
can be performed quickly. There's a little more to it if you want logins
and such but it's not that bad.
Danny
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul
|||Lemme guess, your management is the type to buy the dummy security cameras
and signs and think that it is "just like the real thing" but lots cheaper?
What you have outlined is not a highly available design. It is a
low-availability system pretending to be a high-availability plan. SQL on
NAS does give you two nifty features for one low price; low performance AND
low-availability.
You might look at Peer-to-Peer replication using SQL 2005 as an availability
option. There is a risk of some small data loss during a failure event,
but no more than with XOSoft. Database mirroring has several options
depending on hw much data loss is tolerable and how automatic you want the
failover. Of course, the tighter the constraints, the higher the
performance impact.
The short version is that High Availability isn't cheap no matter how it is
implemented. It takes a combination of people, process, and technology to
achieve a true HA system. If your employers want to skimp on one part, I
suspect they will not invest in the other two elements either.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul
|||In addition to the other posts, below gives a good comparison of pros and cons between the three
available technologies for SQL Server 2000:
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul

Cheap mans clustering

Hi all,
This question could be posted in several different groups, but this is the
chosen one.
We plan to do High Availability in the coming financial year and have
budgetary constraints.
If I can detail the setup.
The SQL will have transactional replication, snapshot replication, and
merge replication! I cannot for the life of me see how replication can be
viable (or compatible) in a failover or clustering scenario. We will not
have a SAN (too expensive). We will have a NAS, but my understanding is
that will ask for trouble. I thought that for this to work, the databases
would have to be on a SAN.
Furthermore we will not have the budget for a proper SQL clustering
solution, so thrirdparty is likely. XOSoft seems to be the primary
candidate.
Comments are welcome, personally I don't like the sound of it. I would
however appreciate knowig how SQL replication still works on the
cluster/failover scenarios.
Many thx
PaulRunning SQL Server on NAS is no way to achieve high availability! In
most cases NAS isn't even supported and is definitely not recommended,
except maybe for a non-critical, low throughput database. The following
article gives MS's official line on this, my own advice is: don't go
there.
http://support.microsoft.com/kb/304261
Replication is commonly used as a low-budget alternative to clustering,
however you may also want to take a look at Database Mirroring in SQL
Server 2005. Unlike clustering, mirroring doesn't require any special
hardware.
--
David Portas
SQL Server MVP
--|||Oooohhh. Don't do NAS with SQL.
Your are looking for inexpensive? I assume the cost of SQL Clustering or
log shipping is in the SQL Enterprise license and you are going with
Standard. However, You can write your own version of Log shipping using
SQL standard. It's really just coping over the Tlogs to another server and
restoring. Have a DNS entry that abstracts the SQL Server name and failover
can be performed quickly. There's a little more to it if you want logins
and such but it's not that bad.
Danny
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul|||Lemme guess, your management is the type to buy the dummy security cameras
and signs and think that it is "just like the real thing" but lots cheaper?
What you have outlined is not a highly available design. It is a
low-availability system pretending to be a high-availability plan. SQL on
NAS does give you two nifty features for one low price; low performance AND
low-availability.
You might look at Peer-to-Peer replication using SQL 2005 as an availability
option. There is a risk of some small data loss during a failure event,
but no more than with XOSoft. Database mirroring has several options
depending on hw much data loss is tolerable and how automatic you want the
failover. Of course, the tighter the constraints, the higher the
performance impact.
The short version is that High Availability isn't cheap no matter how it is
implemented. It takes a combination of people, process, and technology to
achieve a true HA system. If your employers want to skimp on one part, I
suspect they will not invest in the other two elements either.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul|||In addition to the other posts, below gives a good comparison of pros and cons between the three
available technologies for SQL Server 2000:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul

Cheap mans clustering

Hi all,
This question could be posted in several different groups, but this is the
chosen one.
We plan to do High Availability in the coming financial year and have
budgetary constraints.
If I can detail the setup.
The SQL will have transactional replication, snapshot replication, and
merge replication! I cannot for the life of me see how replication can be
viable (or compatible) in a failover or clustering scenario. We will not
have a SAN (too expensive). We will have a NAS, but my understanding is
that will ask for trouble. I thought that for this to work, the databases
would have to be on a SAN.
Furthermore we will not have the budget for a proper SQL clustering
solution, so thrirdparty is likely. XOSoft seems to be the primary
candidate.
Comments are welcome, personally I don't like the sound of it. I would
however appreciate knowig how SQL replication still works on the
cluster/failover scenarios.
Many thx
PaulRunning SQL Server on NAS is no way to achieve high availability! In
most cases NAS isn't even supported and is definitely not recommended,
except maybe for a non-critical, low throughput database. The following
article gives MS's official line on this, my own advice is: don't go
there.
http://support.microsoft.com/kb/304261
Replication is commonly used as a low-budget alternative to clustering,
however you may also want to take a look at Database Mirroring in SQL
Server 2005. Unlike clustering, mirroring doesn't require any special
hardware.
David Portas
SQL Server MVP
--|||Oooohhh. Don't do NAS with SQL.
Your are looking for inexpensive? I assume the cost of SQL Clustering or
log shipping is in the SQL Enterprise license and you are going with
Standard. However, You can write your own version of Log shipping using
SQL standard. It's really just coping over the Tlogs to another server and
restoring. Have a DNS entry that abstracts the SQL Server name and failover
can be performed quickly. There's a little more to it if you want logins
and such but it's not that bad.
Danny
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul|||Lemme guess, your management is the type to buy the dummy security cameras
and signs and think that it is "just like the real thing" but lots cheaper?
What you have outlined is not a highly available design. It is a
low-availability system pretending to be a high-availability plan. SQL on
NAS does give you two nifty features for one low price; low performance AND
low-availability.
You might look at Peer-to-Peer replication using SQL 2005 as an availability
option. There is a risk of some small data loss during a failure event,
but no more than with XOSoft. Database mirroring has several options
depending on hw much data loss is tolerable and how automatic you want the
failover. Of course, the tighter the constraints, the higher the
performance impact.
The short version is that High Availability isn't cheap no matter how it is
implemented. It takes a combination of people, process, and technology to
achieve a true HA system. If your employers want to skimp on one part, I
suspect they will not invest in the other two elements either.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul|||In addition to the other posts, below gives a good comparison of pros and co
ns between the three
available technologies for SQL Server 2000:
http://www.microsoft.com/technet/pr...oy/sqlhalp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1vebb4usw7c62.14jpupfkmyvi6.dlg@.40tude.net...
> Hi all,
> This question could be posted in several different groups, but this is the
> chosen one.
> We plan to do High Availability in the coming financial year and have
> budgetary constraints.
> If I can detail the setup.
>
> The SQL will have transactional replication, snapshot replication, and
> merge replication! I cannot for the life of me see how replication can be
> viable (or compatible) in a failover or clustering scenario. We will not
> have a SAN (too expensive). We will have a NAS, but my understanding is
> that will ask for trouble. I thought that for this to work, the databases
> would have to be on a SAN.
> Furthermore we will not have the budget for a proper SQL clustering
> solution, so thrirdparty is likely. XOSoft seems to be the primary
> candidate.
> Comments are welcome, personally I don't like the sound of it. I would
> however appreciate knowig how SQL replication still works on the
> cluster/failover scenarios.
> Many thx
> Paul