I'm running into problems with a merge publication where I am trying to use
both subset (dynamic) filters and join filters. I was hoping that the join
filters operated on the slice of data carved out by the subset filters, but
that doesn't appear to be happening. Instead, the union of the results of
the subset filter on the entire database and the dynamic filter on the
entire database is what is synchronized.
For example, assume I have the following three tables with the listed
columns:
- User (Id, Name)
- Entry (Id, Date, UserId, SignatureId)
- Signature (Id, Date, Data)
The goals of the filters are as follows:
- Only retrieve the entry rows that are less than 30 days old.
- Only retrieve the signature rows that are less than 30 days old.
- Only retrieve the entry rows for that particular user.
I setup subset filters for the following tables:
Entry: ... WHERE Date > (GETDATE() - 30)
Signature: ... WHERE Date > (GETDATE() - 30)
I also setup a join filter to only get a particular user's Entries:
... INNER JOIN [dbo].[Entry] ON Entry.UserId = User.Id and
User.Name = HOST_NAME()
(Assume I pass in the User's name as the HOST_NAME.)
When I synchronize with SQL CE, I find that I do indeed get only the Entry
rows associated with that user, but I get ALL of that user's Entry rows,
regardless of the Date.
In addition, I only get Signatures that are less than 30 days old. The
problem is the old Entry rows point to Signature rows that do not exist as
they were filtered out, and I receive an error stating that "The row update
or insert cannot be reapplied due to an integrity violation" (Error 28549)
My question boils down to this:
In what order and on what subsets of data do filters get applied?
More specifically, how can I set up filters with this schema to avoid the
integrity violation problem?
Thanks for your time.
- Eli
Hi Eli,
i think you should read my post "Push referenced records" since we're facing
a similar problem. The solution for us was to eliminate the foreign key
constraints (all of them), because you can not ensure the order of
insertation when the snapshot arrives to the subscriber. In addition we tried
to extend the foreign key definitions with the "NOT FOR REPLICATION" option -
because we didn't want to drop the constraints - but this option is not
supported in CE. ... we also had to eliminate the triggers - fortunately "NOT
FOR REPLICATION" is supported for triggers.
When creating the replication you have to specify the function that is
filtering the records per subscribers (e.g. SUSER_SNAME(), HOST_NAME()). I
think you should put the "User.Name = HOST_NAME()" clause to the rowfilter.
Filtering by date is still a problem to solve. We have the same situation...
regards
pierre
|||Thanks for the reply, Pierre.
I solved the problem by eliminating the subset filters on tables that
already have join filters, and moved the filtering logic into the join
filter. For example, I added the last line to my join filter:
... INNER JOIN [dbo].[Entry] ON Entry.UserId = User.Id and
User.Name = HOST_NAME()
AND Entry.Dt > (GETDATE() - 30)
Thanks again,
- Eli
"Pierre Packard" <Pierre Packard@.discussions.microsoft.com> wrote in message
news:4A13E8A8-B3F0-415B-9560-2C20AB78B6F6@.microsoft.com...
> Hi Eli,
> i think you should read my post "Push referenced records" since we're
facing
> a similar problem. The solution for us was to eliminate the foreign key
> constraints (all of them), because you can not ensure the order of
> insertation when the snapshot arrives to the subscriber. In addition we
tried
> to extend the foreign key definitions with the "NOT FOR REPLICATION"
option -
> because we didn't want to drop the constraints - but this option is not
> supported in CE. ... we also had to eliminate the triggers - fortunately
"NOT
> FOR REPLICATION" is supported for triggers.
> When creating the replication you have to specify the function that is
> filtering the records per subscribers (e.g. SUSER_SNAME(), HOST_NAME()). I
> think you should put the "User.Name = HOST_NAME()" clause to the
rowfilter.
> Filtering by date is still a problem to solve. We have the same
situation...
> regards
> pierre
>
Showing posts with label subset. Show all posts
Showing posts with label subset. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
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: Faile
d
- 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?|||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.1
172@.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 d
atabase". There are other jobs running before and after this job and there a
re no problems with them. I can't figure out why this particular job won't r
un.
Sincerely,
Anna
[vbcol=seagreen]
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?
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: Faile
d
- 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?|||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.1
172@.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 d
atabase". There are other jobs running before and after this job and there a
re no problems with them. I can't figure out why this particular job won't r
un.
Sincerely,
Anna
[vbcol=seagreen]
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?
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--
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 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?
|||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
[vbcol=seagreen]
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?
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?
|||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
[vbcol=seagreen]
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?
Subscribe to:
Posts (Atom)