internal consistency error occurred while restoring a database

this is what i get when restoring a database from a *.bak file, knowing that this is the only backup file i have for my DELETED databases !!!
please help ASAP
Microsoft SQL-DMO (ODBC SQLState: 42000)
An internal consistency error occurred. Contact Technical Support for assistance.
RESTORE DATABASE is terminating abnormally.
I would try what the message suggests: contact PSS.
David Portas
SQL Server MVP

internal consistency error occurred while restoring a database

this is what i get when restoring a database from a *.bak file, knowing that this is the only backup file i have for my DELETED databases !!
please help ASA
Microsoft SQL-DMO (ODBC SQLState: 42000
--
An internal consistency error occurred. Contact Technical Support for assistance
RESTORE DATABASE is terminating abnormallyi'm on SQL2K SP3,
and the file i'm restoring from is reported normally from the OS !|||I would try what the message suggests: contact PSS.
--
David Portas
SQL Server MVP
--

internal consistency error occurred while restoring a database

this is what i get when restoring a database from a *.bak file, knowing that
this is the only backup file i have for my DELETED databases !!!
please help ASAP
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
An internal consistency error occurred. Contact Technical Support for assist
ance.
RESTORE DATABASE is terminating abnormally.I would try what the message suggests: contact PSS.
David Portas
SQL Server MVP
--

Intermittant Connection Errors

I have a Merge-Pull subscription setup between two SQL 2005 databases. While watching the status messages display for the subscription, I am constantly seeing the following message being displayed:

"The merge process could not connect to the Publisher '{server}:{database}'. Check to ensure that the server is running."

It will display for anywhere between 15 seconds to a few minutes, then it will display the standard "Waiting 60 seconds..." message which is what I'm used to seeing when all is working well.

There doesn't seem to be any network issues that I can identify. I ran a quick script to run ping continuously against the publisher server and verified that the server was constantly "pingable", even when the above message was displayed (which, by the way, was being displayed by using SSMS while connected to the publisher).

Does anyone know what would cause this message to appear all the time - for extended amounts of time, with occasional glimpses of a non-error status?

It's a wild guess but failing to login / authenticate ?|||I thought of that but am not sure that it applies. The reason being that I can leave the subscription/synchronization status window open in SSMS and it will toggle back and forth (seemingly randomly over time) between the message I mentioned above and some type of valid message.|||What about Timeouts (Connection or Query)? I have seen similar issues in Web Sync. If the amount of data changed since the last sync operation then I have experienced Timeouts while the sync is initialized. Not exactly as you describe but another parameter worth checking.

Rab
|||

I'm not sure what the issue was but it seems to have resolved itself - at least for the time being. If it happens again, I'll be looking for more answers :-)

Thanks for everyone's help.

|||I have this problem as well. It works sometimes and then fails with this error message which leads me to believe it isn't a login problem. I'm still new to replication so I could be wrong, but I think once I get this message, replication is broken and requires someone to intervene to reinitialize. Has anyone figured out what is going on?

Intermittant Connection Errors

I have a Merge-Pull subscription setup between two SQL 2005 databases. While watching the status messages display for the subscription, I am constantly seeing the following message being displayed:

"The merge process could not connect to the Publisher '{server}:{database}'. Check to ensure that the server is running."

It will display for anywhere between 15 seconds to a few minutes, then it will display the standard "Waiting 60 seconds..." message which is what I'm used to seeing when all is working well.

There doesn't seem to be any network issues that I can identify. I ran a quick script to run ping continuously against the publisher server and verified that the server was constantly "pingable", even when the above message was displayed (which, by the way, was being displayed by using SSMS while connected to the publisher).

Does anyone know what would cause this message to appear all the time - for extended amounts of time, with occasional glimpses of a non-error status?

It's a wild guess but failing to login / authenticate ?|||I thought of that but am not sure that it applies. The reason being that I can leave the subscription/synchronization status window open in SSMS and it will toggle back and forth (seemingly randomly over time) between the message I mentioned above and some type of valid message.|||What about Timeouts (Connection or Query)? I have seen similar issues in Web Sync. If the amount of data changed since the last sync operation then I have experienced Timeouts while the sync is initialized. Not exactly as you describe but another parameter worth checking.

Rab|||

I'm not sure what the issue was but it seems to have resolved itself - at least for the time being. If it happens again, I'll be looking for more answers :-)

Thanks for everyone's help.

|||I have this problem as well. It works sometimes and then fails with this error message which leads me to believe it isn't a login problem. I'm still new to replication so I could be wrong, but I think once I get this message, replication is broken and requires someone to intervene to reinitialize. Has anyone figured out what is going on?sql

Interesting SQL...

Anyone else experience this?

A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second.

SELECT
dbo.contract_co.producer_num_id, contract_co_status
FROM
dbo.contract_co,
dbo.v_contract_co_status
WHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id )
AND contract_co_status = 'Pending'
OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )

SELECT
dbo.contract_co.producer_num_id, contract_co_status
FROM dbo.contract_co
INNER JOIN dbo.v_contract_co_status
ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_id
WHERE contract_co_status = 'Pending'
OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )The two queries look like they will give different results, too. The first one appears to include a cartesian join. The OR in the where clause makes all the difference.|||Don't want to sound like a snob, but it's all due to the order of processing by QP:

1. JOIN
2. GROUP
3. WHERE
4. HAVING

By rewriting the old query you filtered out what the first query had to deal with while still trying to JOIN.|||actually, i believe it's

1. JOIN
2. WHERE
3. GROUP
4. HAVING

Interesting Maintenance Plan Problem

SQL Server 2005 - I created a maintenance plan to backup several
databases. The steps in the maintenance plan are very simple, first -
check database integrity, next reindex or reorganize the indexes, backup
the databases and finally remove backup files.
I set the schedule to begin at 12:05am and tested the process by
manually running (right-click on the job and start at step). The
process ran successfully, all steps.
That night, the process started at 12:05 and failed after the second
step with no indication why the process failed. I ran it manually and
it completed successfully. The following night the process failed after
the first step completed, again with no indication why the process
failed. Again, I ran the process manually and it completed all steps
successfully.
I modified the schedule to run at 12:30am instead - and all steps
completed successfully.
Does anybody have any ideas why the process would fail when scheduled to
run at 12:05, but not fail when scheduled to run at 12:30?
JeffDid something have the database in use?
Jeffrey Williams wrote:
> SQL Server 2005 - I created a maintenance plan to backup several
> databases. The steps in the maintenance plan are very simple, first -
> check database integrity, next reindex or reorganize the indexes, backup
> the databases and finally remove backup files.
> I set the schedule to begin at 12:05am and tested the process by
> manually running (right-click on the job and start at step). The
> process ran successfully, all steps.
> That night, the process started at 12:05 and failed after the second
> step with no indication why the process failed. I ran it manually and
> it completed successfully. The following night the process failed after
> the first step completed, again with no indication why the process
> failed. Again, I ran the process manually and it completed all steps
> successfully.
> I modified the schedule to run at 12:30am instead - and all steps
> completed successfully.
> Does anybody have any ideas why the process would fail when scheduled to
> run at 12:05, but not fail when scheduled to run at 12:30?
> Jeff
--
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||Jeffrey
Have you looked at ERROR.LOG ? Any entries?
"Jeffrey Williams" <jeff.williams3188@.verizon.net> wrote in message
news:OeWJCO$nHHA.208@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 - I created a maintenance plan to backup several
> databases. The steps in the maintenance plan are very simple, first -
> check database integrity, next reindex or reorganize the indexes, backup
> the databases and finally remove backup files.
> I set the schedule to begin at 12:05am and tested the process by manually
> running (right-click on the job and start at step). The process ran
> successfully, all steps.
> That night, the process started at 12:05 and failed after the second step
> with no indication why the process failed. I ran it manually and it
> completed successfully. The following night the process failed after the
> first step completed, again with no indication why the process failed.
> Again, I ran the process manually and it completed all steps successfully.
> I modified the schedule to run at 12:30am instead - and all steps
> completed successfully.
> Does anybody have any ideas why the process would fail when scheduled to
> run at 12:05, but not fail when scheduled to run at 12:30?
> Jeff|||Uri,
I looked in every log I could find - no errors, just the job stopped,
reporting a failure.
Jeff
Uri Dimant wrote:
> Jeffrey
> Have you looked at ERROR.LOG ? Any entries?
>
>
> "Jeffrey Williams" <jeff.williams3188@.verizon.net> wrote in message
> news:OeWJCO$nHHA.208@.TK2MSFTNGP05.phx.gbl...
>> SQL Server 2005 - I created a maintenance plan to backup several
>> databases. The steps in the maintenance plan are very simple, first -
>> check database integrity, next reindex or reorganize the indexes, backup
>> the databases and finally remove backup files.
>> I set the schedule to begin at 12:05am and tested the process by manually
>> running (right-click on the job and start at step). The process ran
>> successfully, all steps.
>> That night, the process started at 12:05 and failed after the second step
>> with no indication why the process failed. I ran it manually and it
>> completed successfully. The following night the process failed after the
>> first step completed, again with no indication why the process failed.
>> Again, I ran the process manually and it completed all steps successfully.
>> I modified the schedule to run at 12:30am instead - and all steps
>> completed successfully.
>> Does anybody have any ideas why the process would fail when scheduled to
>> run at 12:05, but not fail when scheduled to run at 12:30?
>> Jeff
>
>|||Brett,
There are no other scheduled jobs at that time, except for the
transaction log files that run every 15 minutes and take less than a
minute to run at that time (no users on the system at midnight).
Jeff
Brett I. Holcomb wrote:
> Did something have the database in use?
>
> Jeffrey Williams wrote:
>> SQL Server 2005 - I created a maintenance plan to backup several
>> databases. The steps in the maintenance plan are very simple, first
>> - check database integrity, next reindex or reorganize the indexes,
>> backup the databases and finally remove backup files.
>> I set the schedule to begin at 12:05am and tested the process by
>> manually running (right-click on the job and start at step). The
>> process ran successfully, all steps.
>> That night, the process started at 12:05 and failed after the second
>> step with no indication why the process failed. I ran it manually
>> and it completed successfully. The following night the process
>> failed after the first step completed, again with no indication why
>> the process failed. Again, I ran the process manually and it
>> completed all steps successfully.
>> I modified the schedule to run at 12:30am instead - and all steps
>> completed successfully.
>> Does anybody have any ideas why the process would fail when scheduled
>> to run at 12:05, but not fail when scheduled to run at 12:30?
>> Jeff
>|||How about the report file for the maint plan? Anything there?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jeffrey Williams" <jeff.williams3188@.verizon.net> wrote in message
news:4659B188.5000608@.verizon.net...
> Uri,
> I looked in every log I could find - no errors, just the job stopped, reporting a failure.
> Jeff
> Uri Dimant wrote:
>> Jeffrey
>> Have you looked at ERROR.LOG ? Any entries?
>>
>>
>> "Jeffrey Williams" <jeff.williams3188@.verizon.net> wrote in message
>> news:OeWJCO$nHHA.208@.TK2MSFTNGP05.phx.gbl...
>> SQL Server 2005 - I created a maintenance plan to backup several databases. The steps in the
>> maintenance plan are very simple, first - check database integrity, next reindex or reorganize
>> the indexes, backup the databases and finally remove backup files.
>> I set the schedule to begin at 12:05am and tested the process by manually running (right-click
>> on the job and start at step). The process ran successfully, all steps.
>> That night, the process started at 12:05 and failed after the second step with no indication why
>> the process failed. I ran it manually and it completed successfully. The following night the
>> process failed after the first step completed, again with no indication why the process failed.
>> Again, I ran the process manually and it completed all steps successfully.
>> I modified the schedule to run at 12:30am instead - and all steps completed successfully.
>> Does anybody have any ideas why the process would fail when scheduled to run at 12:05, but not
>> fail when scheduled to run at 12:30?
>> Jeff
>>|||Tibor Karaszi wrote:
> How about the report file for the maint plan? Anything there?
>
Nothing there either - the job just stops. I even reviewed the default
trace file and did not find anything there.
I can schedule the process at 12:30am and leave it there, but I was
hoping somebody else had seen something like this. I will keep
researching to see if I can find anything.
Thanks,
Jeffsql

interdatabase trigger possible?

Hello to all,

I have say two databases A and B.
A database has tables a1 and a2.
B database has a table b1.

My question is

Can i write a trigger for the table b1, which access the fields from
the table a1 and a2?

Simply, can i write a trigger which access data from other databases?

Thank you in advance,
vishnuSure for different databases on the same server you can use the
threepart name: Database.Owner.ObjectName (e.g. SELECT * FROM
somedatabase.dbo.SomeTable)

For database on a different server you have to use a linekd server
within the fourpart name: linkedServername.Database.Owner.ObjectName
(e.g. SELECT * FROM linkedServername.Database.Owner.ObjectName )

HTH, jens Suessmeyer.

Interaction between tables of local and remote SQL databases

Hi all,

I would be very glad if someone can suggest me what techniques I should use in the following scenario:

I have 2 SQL Server databases : DB1 and DB2. DB 1 is on a remote server (hosting server) and DB 2 is on a local server.

Some tables of each db contain tables that are polulated and changed by the appropriate application, i.e.:

DB1.users, DB1.orders,... etc are managed by "webapplication"

DB1.products ... are not managed by "webapplication" : in fact only used to read from

DB2.products, DB2.customers, .... etc are managed by "winapplication"

DB2.orders,....are partially managed by "winapplication"

Since the amount of data can go over 100000 records i'm wondering what would by the best approach to :

- synchronize the data of DB1.products and DB2.products in DB1 on remote server (updated newly added rows and update changed rows,....)

- the products data is only (at the moment) added, edited and deleted on the local server

I think SQLBulkCopy will not do the job. Should it be possible with some query? Or...?

Any suggestions are appreciated!!

O.

Some addition:

- Does SQLBulkCopy copy-append data to the destination database.table?

- What about identity keys?

- Should it be the best solution to modify the SqlCommand so it takes only those rows I want to be inserted as (depending on bulkcopies already done before) or is this done by SQLBulkcopy itself....?

Please inform.

O.

Integrity job error

Hi folks,

I created a job to check the integrity of my databases every week using SQL Server Maintenance Plan Wizard.
The job is failing every time...
For tb_basico database it works fine, but with the other one (tb_cep) it doesn't work...

Does someone have an idea to solve this problem?

The message is:

[1] Database tb_basico: Check Data and Index Linkage...

** Execution Time: 0 hrs, 0 mins, 11 secs **

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'tb_cep'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[2] Database tb_cep: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **The problem is that other users are in the database while you are attempting to run the job created by the wizard. The wizard created job needs exclusive use of the database to complete the tasks you've asked it to do.

If you want to continue to run the wizard task, then I'd do an ALTER DATABASE mydb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp), then run the job created by the wizard. After the job completes, you can ALTER DATABASE mydb SET MULTI_USER to allow the users back into the database.

-PatP

Integrity Checks Failed?

We have 3 maintenance plans, each running against a different subset of
databases on the same server. Each maintenance plan runs integrity checks
once a week. According to "Job History", one of the three jobs fails every
week. Looking at the sql logs every DBCC check shows "found 0 errors and
repaired 0 errors." In the server's Application Event Log we see this vague
error message:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/2/2005
Time: 12:00:27 AM
User: N/A
Computer: FBENSQLP1
Description:
SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status: Failed
- Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job was
invoked by Schedule 19 (Schedule 1). The last step to run was step 1 (Step
1).
Is it possible that this is some sort of a false alert? The other two
weekly integrity checks always show successful completions.
What might cause this job to "Fail" every week?Have you looked at the report that is generated from your maintenance plan?
Possibly this report will help you determine what is wrong. If you are not
generating the report, then review your options under the "Reporting" tab
while editing your maintenance plan.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"AnthonyMunoz" <AnthonyMunoz@.discussions.microsoft.com> wrote in message
news:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...
> We have 3 maintenance plans, each running against a different subset of
> databases on the same server. Each maintenance plan runs integrity checks
> once a week. According to "Job History", one of the three jobs fails
every
> week. Looking at the sql logs every DBCC check shows "found 0 errors and
> repaired 0 errors." In the server's Application Event Log we see this
vague
> error message:
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 1/2/2005
> Time: 12:00:27 AM
> User: N/A
> Computer: FBENSQLP1
> Description:
> SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
> 'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status:
Failed
> - Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job was
> invoked by Schedule 19 (Schedule 1). The last step to run was step 1
(Step
> 1).
> Is it possible that this is some sort of a false alert? The other two
> weekly integrity checks always show successful completions.
> What might cause this job to "Fail" every week?
>|||No, something indeed happened; the problem is finding out more details as to
why. There are several logging options available to you.
1. In the maintenance plan, Reporting Tab, choose to retain information
within the MSDB database but also create Job Run report to a text file.
Oftentimes, this information will provide you with the necessary detail.
2. On the job, make sure it is configured to output additional step info on
the advanced tab of the step settings.
3. You've already discovered the SQL Server error logs and the Windows
Application event logs, also take a look at the SQL Agent error logs.
To view the maintenance history, in SQL EM, right-click one of the
maintenance plans and choose to view the history. This log will tell you
each activity, and each database, whether successfull or failure. If there
were database connectivity issues, then this information will be missing and
the text log files your created above will be the only source of information
outside of the error logs you have already discovered.
Hope this helps.
Sincerely,
Anthony Thomas
"AnthonyMunoz" <AnthonyMunoz@.discussions.microsoft.com> wrote in message
news:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...
We have 3 maintenance plans, each running against a different subset of
databases on the same server. Each maintenance plan runs integrity checks
once a week. According to "Job History", one of the three jobs fails every
week. Looking at the sql logs every DBCC check shows "found 0 errors and
repaired 0 errors." In the server's Application Event Log we see this vague
error message:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/2/2005
Time: 12:00:27 AM
User: N/A
Computer: FBENSQLP1
Description:
SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status:
Failed
- Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job was
invoked by Schedule 19 (Schedule 1). The last step to run was step 1 (Step
1).
Is it possible that this is some sort of a false alert? The other two
weekly integrity checks always show successful completions.
What might cause this job to "Fail" every week?|||--____XAVHMWYRSQSWQDYPECYE____
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Hi!
We have the exact same error. We run the integrity check once a week on =this one database and it has run smoothly until three weeks ago. In the =log file it says "Database state cannot be changed while other users are =using the database". There are other jobs running before and after this =job and there are no problems with them. I can't figure out why this =particular job won't run.
Sincerely,
Anna
>> AnthonyThomas<Anthony.Thomas@.CommerceBank.com> 2005-01-03 17:11:38 >>
No, something indeed happened; the problem is finding out more details as =to
why. There are several logging options available to you.
1. In the maintenance plan, Reporting Tab, choose to retain information
within the MSDB database but also create Job Run report to a text file.
Oftentimes, this information will provide you with the necessary detail.
2. On the job, make sure it is configured to output additional step info =on
the advanced tab of the step settings.
3. You've already discovered the SQL Server error logs and the Windows
Application event logs, also take a look at the SQL Agent error logs.
To view the maintenance history, in SQL EM, right-click one of the
maintenance plans and choose to view the history. This log will tell you
each activity, and each database, whether successfull or failure. If =there
were database connectivity issues, then this information will be missing =and
the text log files your created above will be the only source of informatio=n
outside of the error logs you have already discovered.
Hope this helps.
Sincerely,
Anthony Thomas
--
"AnthonyMunoz" <AnthonyMunoz@.discussions.microsoft.com> wrote in message
news:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...
We have 3 maintenance plans, each running against a different subset of
databases on the same server. Each maintenance plan runs integrity checks
once a week. According to "Job History", one of the three jobs fails =every
week. Looking at the sql logs every DBCC check shows "found 0 errors and
repaired 0 errors." In the server's Application Event Log we see this =vague
error message:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/2/2005
Time: 12:00:27 AM
User: N/A
Computer: FBENSQLP1
Description:
SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status:
Failed
- Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job was
invoked by Schedule 19 (Schedule 1). The last step to run was step 1 =(Step
1).
Is it possible that this is some sort of a false alert? The other two
weekly integrity checks always show successful completions.
What might cause this job to "Fail" every week?
--____XAVHMWYRSQSWQDYPECYE____
Content-Type: multipart/related; boundary="____JVBAVOGAYIGGGPIKBBLQ____"
--____JVBAVOGAYIGGGPIKBBLQ____
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
&

---

Hi!
We have the exact same error. We run the integrity check once a =week on this one database and it has run smoothly until three weeks ago. =In the log file it says "Database state cannot be changed while other =users are using the database". There are other jobs running before and =after this job and there are no problems with them. I can't figure =out why this particular job won't run.
Sincerely,
Anna> AnthonyThomas 2005-01-03 17:11:38 >>
No, something indeed happened; the problem =is finding out more details as towhy. There are several logging =options available to you.1. In the maintenance plan, Reporting =Tab, choose to retain informationwithin the MSDB database but also =create Job Run report to a text file.Oftentimes, this information= will provide you with the necessary detail.2. On the job, make =sure it is configured to output additional step info onthe advanced =tab of the step settings.3. You've already discovered the SQL =Server error logs and the WindowsApplication event logs, also take a =look at the SQL Agent error logs.To view the maintenance history, =in SQL EM, right-click one of themaintenance plans and choose to view =the history. This log will tell youeach activity, and each =database, whether successfull or failure. If therewere database =connectivity issues, then this information will be missing andthe text =log files your created above will be the only source of informationouts=ide of the error logs you have already discovered.Hope this =helps.Sincerely,Anthony Thomas-- "A=nthonyMunoz" wrote in =messagenews:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...We= have 3 maintenance plans, each running against a different subset =ofdatabases on the same server. Each maintenance plan runs =integrity checksonce a week. According to "Job History", one of =the three jobs fails everyweek. Looking at the sql logs every =DBCC check shows "found 0 errors andrepaired 0 errors." In the =server's Application Event Log we see this vagueerror message:E=vent Type: WarningEvent Source: SQLSERVERAGENTEvent Category: Job =EngineEvent ID: 208Date: 1/2/2005Time: 12:00:27 AMUser: =N/AComputer: FBENSQLP1Description:SQL Server Scheduled Job ='Integrity Checks Job for DB Maintenance Plan'Internal Maintenance'' =(0x860A641AB3C0FD4CA22C0369E63523E4) - Status:Failed- Invoked on: =2005-01-02 00:00:00 - Message: The job failed. The Job wasinvoked= by Schedule 19 (Schedule 1). The last step to run was step 1 =(Step1).Is it possible that this is some sort of a false =alert? The other twoweekly integrity checks always show =successful completions.What might cause this job to "Fail" every =week?

---

--____JVBAVOGAYIGGGPIKBBLQ____--
--____XAVHMWYRSQSWQDYPECYE____--|||This is a multi-part message in MIME format.
--=_NextPart_000_0070_01C51FCF.9D6B6680
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You need to uncheck the checkbox in the MP wizard that states to "Fix =Minor Repairs". Then it won't attempt to put the db into single user =mode and it will operate properly. If there is a problem the job will =fail and then you can handle the issue the correct way as is best suited =for your environment.
-- Andrew J. Kelly SQL MVP

"Anna Emegard" <Anna.Emegard@.harryda.se> wrote in message =news:ufX7Xb$HFHA.1172@.TK2MSFTNGP12.phx.gbl...
Hi!
We have the exact same error. We run the integrity check once a week =on this one database and it has run smoothly until three weeks ago. In =the log file it says "Database state cannot be changed while other users =are using the database". There are other jobs running before and after =this job and there are no problems with them. I can't figure out why =this particular job won't run.
Sincerely,
Anna
>> AnthonyThomas<Anthony.Thomas@.CommerceBank.com> 2005-01-03 17:11:38 =
No, something indeed happened; the problem is finding out more details =as to
why. There are several logging options available to you.
1. In the maintenance plan, Reporting Tab, choose to retain =information
within the MSDB database but also create Job Run report to a text =file.
Oftentimes, this information will provide you with the necessary =detail.
2. On the job, make sure it is configured to output additional step =info on
the advanced tab of the step settings.
3. You've already discovered the SQL Server error logs and the Windows
Application event logs, also take a look at the SQL Agent error logs.
To view the maintenance history, in SQL EM, right-click one of the
maintenance plans and choose to view the history. This log will tell =you
each activity, and each database, whether successfull or failure. If =there
were database connectivity issues, then this information will be =missing and
the text log files your created above will be the only source of =information
outside of the error logs you have already discovered.
Hope this helps.
Sincerely,
Anthony Thomas
--
"AnthonyMunoz" <AnthonyMunoz@.discussions.microsoft.com> wrote in =message
news:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...
We have 3 maintenance plans, each running against a different subset =of
databases on the same server. Each maintenance plan runs integrity =checks
once a week. According to "Job History", one of the three jobs fails =every
week. Looking at the sql logs every DBCC check shows "found 0 errors =and
repaired 0 errors." In the server's Application Event Log we see this =vague
error message:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/2/2005
Time: 12:00:27 AM
User: N/A
Computer: FBENSQLP1
Description:
SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status:
Failed
- Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job =was
invoked by Schedule 19 (Schedule 1). The last step to run was step 1 =(Step
1).
Is it possible that this is some sort of a false alert? The other two
weekly integrity checks always show successful completions.
What might cause this job to "Fail" every week?
--=_NextPart_000_0070_01C51FCF.9D6B6680
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You need to uncheck the checkbox in the MP =wizard that states to "Fix Minor Repairs". Then it won't attempt to put the db =into single user mode and it will operate properly. If there is a =problem the job will fail and then you can handle the issue the correct way as is =best suited for your environment.
-- Andrew J. Kelly SQL MVP
"Anna Emegard" =wrote in message news:ufX7Xb$HFHA.1172=@.TK2MSFTNGP12.phx.gbl...
Hi!

We have the exact same error. We run the integrity check =once a week on this one database and it has run smoothly until three weeks ago. In =the log file it says "Database state cannot be changed while other users are =using the database". There are other jobs running before and after this job =and there are no problems with them. I can't figure out why this =particular job won't run.

Sincerely,
Anna> AnthonyThomas 2005-01-03 =17:11:38 >>
No, something indeed happened; the =problem is finding out more details as towhy. There are several logging =options available to you.1. In the maintenance plan, Reporting Tab, =choose to retain informationwithin the MSDB database but also create =Job Run report to a text file.Oftentimes, this information will provide =you with the necessary detail.2. On the job, make sure it is configured =to output additional step info onthe advanced tab of the step settings.3. You've already discovered the SQL Server error =logs and the WindowsApplication event logs, also take a look at the SQL =Agent error logs.To view the maintenance history, in SQL EM, right-click =one of themaintenance plans and choose to view the history. This =log will tell youeach activity, and each database, whether successfull or failure. If therewere database connectivity issues, then =this information will be missing andthe text log files your created =above will be the only source of informationoutside of the error logs you =have already discovered.Hope this helps.Sincerely,Anthony Thomas-- "AnthonyMunoz" =wrote in =messagenews:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...=We have 3 maintenance plans, each running against a different subset ofdatabases on the same server. Each maintenance plan runs =integrity checksonce a week. According to "Job History", one of the =three jobs fails everyweek. Looking at the sql logs every DBCC check =shows "found 0 errors andrepaired 0 errors." In the server's =Application Event Log we see this vagueerror message:Event Type: WarningEvent Source: SQLSERVERAGENTEvent Category: Job =EngineEvent ID: 208Date: 1/2/2005Time: 12:00:27 AMUser: =N/AComputer: FBENSQLP1Description:SQL Server Scheduled Job 'Integrity =Checks Job for DB Maintenance Plan'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status:Failed- Invoked =on: 2005-01-02 00:00:00 - Message: The job failed. The Job =wasinvoked by Schedule 19 (Schedule 1). The last step to run was step 1 (Step1).Is it possible that this is some sort of a false alert? The other twoweekly integrity checks always show =successful completions.What might cause this job to "Fail" every week?

---

--=_NextPart_000_0070_01C51FCF.9D6B6680--

Integrity Checks continue to fail

I have a Maintenance plan schedule on one my dev databases, and I'm
continually getting notices that it is failing. Here is the report that I
get:
Starting maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:00
PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other u
sers
are using the database 'CRMDEV'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE st
atement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[1] Database CRMDEV: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs
to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:01 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
How do I put a database in "single-user" mode to run an Integrity check?
Thanks,
SaraSaral6978 wrote:
> I have a Maintenance plan schedule on one my dev databases, and I'm
> continually getting notices that it is failing. Here is the report that I
> get:
> Starting maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:0
0 PM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'CRMDEV'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [1] Database CRMDEV: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database nee
ds to
> be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:01
PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> How do I put a database in "single-user" mode to run an Integrity check?
> Thanks,
> Sara
>
The maintenance plan is attempting to put the DB in single user mode
(that's what the failing ALTER DATABASE statement is doing). It's
failing because someone/something has a connection that can't be terminated.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||A database does NOT need to be in the single user mode to perform integrity
checks. But it does need to be in the single user mode to attempt any repair
.
Automated repair is a dangerous business, and I'd suggest that you modify
your maintenance plan to disable it. In addition, it's not rare for a
database to be left in the single user mode after the repair is done, thus
preventing others from using the database until you intervene.
Linchi
"Saral6978" wrote:

> I have a Maintenance plan schedule on one my dev databases, and I'm
> continually getting notices that it is failing. Here is the report that I
> get:
> Starting maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:0
0 PM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'CRMDEV'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [1] Database CRMDEV: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database nee
ds to
> be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:01
PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> How do I put a database in "single-user" mode to run an Integrity check?
> Thanks,
> Sara
>|||Linchi - thank you so much for your suggestion...I will disable the automati
c
repair...thanks!
"Linchi Shea" wrote:
[vbcol=seagreen]
> A database does NOT need to be in the single user mode to perform integrit
y
> checks. But it does need to be in the single user mode to attempt any repa
ir.
> Automated repair is a dangerous business, and I'd suggest that you modify
> your maintenance plan to disable it. In addition, it's not rare for a
> database to be left in the single user mode after the repair is done, thus
> preventing others from using the database until you intervene.
> Linchi
> "Saral6978" wrote:
>|||Okay - I just unchecked the "Attempt to repair" box, ran the Integrity check
,
and it was successful.
Thank you so much!
"Linchi Shea" wrote:
[vbcol=seagreen]
> A database does NOT need to be in the single user mode to perform integrit
y
> checks. But it does need to be in the single user mode to attempt any repa
ir.
> Automated repair is a dangerous business, and I'd suggest that you modify
> your maintenance plan to disable it. In addition, it's not rare for a
> database to be left in the single user mode after the repair is done, thus
> preventing others from using the database until you intervene.
> Linchi
> "Saral6978" wrote:
>

Integrity Checks continue to fail

I have a Maintenance plan schedule on one my dev databases, and I'm
continually getting notices that it is failing. Here is the report that I
get:
Starting maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:00 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'CRMDEV'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database CRMDEV: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:01 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
How do I put a database in "single-user" mode to run an Integrity check?
Thanks,
Sara
Saral6978 wrote:
> I have a Maintenance plan schedule on one my dev databases, and I'm
> continually getting notices that it is failing. Here is the report that I
> get:
> Starting maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:00 PM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other users
> are using the database 'CRMDEV'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database CRMDEV: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:01 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> How do I put a database in "single-user" mode to run an Integrity check?
> Thanks,
> Sara
>
The maintenance plan is attempting to put the DB in single user mode
(that's what the failing ALTER DATABASE statement is doing). It's
failing because someone/something has a connection that can't be terminated.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||A database does NOT need to be in the single user mode to perform integrity
checks. But it does need to be in the single user mode to attempt any repair.
Automated repair is a dangerous business, and I'd suggest that you modify
your maintenance plan to disable it. In addition, it's not rare for a
database to be left in the single user mode after the repair is done, thus
preventing others from using the database until you intervene.
Linchi
"Saral6978" wrote:

> I have a Maintenance plan schedule on one my dev databases, and I'm
> continually getting notices that it is failing. Here is the report that I
> get:
> Starting maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:00 PM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other users
> are using the database 'CRMDEV'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database CRMDEV: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'CRMDEV DB Maintenance Plan' on 1/11/2007 8:00:01 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> How do I put a database in "single-user" mode to run an Integrity check?
> Thanks,
> Sara
>
|||Linchi - thank you so much for your suggestion...I will disable the automatic
repair...thanks!
"Linchi Shea" wrote:
[vbcol=seagreen]
> A database does NOT need to be in the single user mode to perform integrity
> checks. But it does need to be in the single user mode to attempt any repair.
> Automated repair is a dangerous business, and I'd suggest that you modify
> your maintenance plan to disable it. In addition, it's not rare for a
> database to be left in the single user mode after the repair is done, thus
> preventing others from using the database until you intervene.
> Linchi
> "Saral6978" wrote:
|||Okay - I just unchecked the "Attempt to repair" box, ran the Integrity check,
and it was successful.
Thank you so much!
"Linchi Shea" wrote:
[vbcol=seagreen]
> A database does NOT need to be in the single user mode to perform integrity
> checks. But it does need to be in the single user mode to attempt any repair.
> Automated repair is a dangerous business, and I'd suggest that you modify
> your maintenance plan to disable it. In addition, it's not rare for a
> database to be left in the single user mode after the repair is done, thus
> preventing others from using the database until you intervene.
> Linchi
> "Saral6978" wrote:

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can run
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.Don't use the repair option. If you have a problem in the database, you want to know about it,
analyze why it happened, make sure you repair the faulty hardware (for example) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to use that option, you
either have to add something to the job that kicks out all users, or live with that error if there
are users connected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have placed
> the database in question into single user mode and run the same task on the
> database and it completes successfully. The problem is though that I can run
> the integrity check with the 'repair minor errors' option turned off and it
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.

Integrity Check Fails

I setup a DB Maint, Plan for several databases however,
the Integrity Check job fails for a few of my databases.
The error is "Repair statement not processed. Database
needs to be in single user mode."
Any thoughts on why this is happening?
Thanks,
DonPretty much what it says. What version and service pack of SQL Server? Note that you can't put
master in single user mode. I recommend that you remove that darn option to "attempt to repair minor
problems". :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Don" <drduquette@.aol.com> wrote in message news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> I setup a DB Maint, Plan for several databases however,
> the Integrity Check job fails for a few of my databases.
> The error is "Repair statement not processed. Database
> needs to be in single user mode."
> Any thoughts on why this is happening?
> Thanks,
> Don|||Thanks for the quick response! We are using SQLserver
2000 service pack 3. I realize that I could uncheck the
repair but that defeats the purpose if there is a problem
with the database. In addition, MS recommends that the
repair option be checked.
Is there any work around to this issue of putting the DB
in single user mode?
Thanks,
Don
>--Original Message--
>Pretty much what it says. What version and service pack
of SQL Server? Note that you can't put
>master in single user mode. I recommend that you remove
that darn option to "attempt to repair minor
>problems". :-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Don" <drduquette@.aol.com> wrote in message
news:000001c36ca4$732448e0$a101280a@.phx.gbl...
>> I setup a DB Maint, Plan for several databases however,
>> the Integrity Check job fails for a few of my databases.
>> The error is "Repair statement not processed. Database
>> needs to be in single user mode."
>> Any thoughts on why this is happening?
>> Thanks,
>> Don
>
>.
>|||Don,
Tibor mentioned a restriction on master. In addition, if a database has any
open connections it cannot be changed to single user mode unless you use the
WITH ROLLBACK clauses.
ALTER DATABASE mydatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
This breaks all unqualified connections and switches to single user mode.
(Much easier than the old method of writing a looping procedure to KILL
connections until they were finally all gone.)
Russell Fields
"Don" <drduquette@.aol.com> wrote in message
news:0e9901c36ca7$0659c2f0$a301280a@.phx.gbl...
> Thanks for the quick response! We are using SQLserver
> 2000 service pack 3. I realize that I could uncheck the
> repair but that defeats the purpose if there is a problem
> with the database. In addition, MS recommends that the
> repair option be checked.
> Is there any work around to this issue of putting the DB
> in single user mode?
> Thanks,
> Don
> >--Original Message--
> >Pretty much what it says. What version and service pack
> of SQL Server? Note that you can't put
> >master in single user mode. I recommend that you remove
> that darn option to "attempt to repair minor
> >problems". :-)
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Don" <drduquette@.aol.com> wrote in message
> news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> >> I setup a DB Maint, Plan for several databases however,
> >> the Integrity Check job fails for a few of my databases.
> >> The error is "Repair statement not processed. Database
> >> needs to be in single user mode."
> >>
> >> Any thoughts on why this is happening?
> >>
> >> Thanks,
> >> Don
> >
> >
> >.
> >|||The purpose of CHECKDB is to get *notified* in the unlikely event of a problem. I surely don't want
some background thing try to repair the database if I run into a problem with it. I want to be
there, do a log backup first, think, etc etc etc.
Anyhow, master cannot be in single user mode, if this is the one which is causing your problem, then
you have to remove it from the plan, or don't do background repair.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Don" <drduquette@.aol.com> wrote in message news:0e9901c36ca7$0659c2f0$a301280a@.phx.gbl...
> Thanks for the quick response! We are using SQLserver
> 2000 service pack 3. I realize that I could uncheck the
> repair but that defeats the purpose if there is a problem
> with the database. In addition, MS recommends that the
> repair option be checked.
> Is there any work around to this issue of putting the DB
> in single user mode?
> Thanks,
> Don
> >--Original Message--
> >Pretty much what it says. What version and service pack
> of SQL Server? Note that you can't put
> >master in single user mode. I recommend that you remove
> that darn option to "attempt to repair minor
> >problems". :-)
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Don" <drduquette@.aol.com> wrote in message
> news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> >> I setup a DB Maint, Plan for several databases however,
> >> the Integrity Check job fails for a few of my databases.
> >> The error is "Repair statement not processed. Database
> >> needs to be in single user mode."
> >>
> >> Any thoughts on why this is happening?
> >>
> >> Thanks,
> >> Don
> >
> >
> >.
> >

integration of 2 SQL 2005 DB Servers

Hi,

I have 2 database servers and each has same database.Program that inserts these databases randomly selects one of them to insert data.So only difference is data.

I have to generate some report using these 2 sources.System administrators reject integrating these 2 servers into one.

My question is , I want to run a query that looks the column x in table y and finds unique records in this table.But this works just in one DB.What do you recommend me to find solution that finds real unique records, i.e. looks both servers to find records.

Is there a Microsoft product that may be configured brain of different DB Servers or any other solution?

I have also memory restrictions and data is considered as more than 100 GBytes for each server.

Answers will be highly appreciated.

Thanks.

sysdamins' guys also denied you create links between these servers?|||No, link is allowed.