Integrated Security: plans

Showing posts with label plans. Show all posts

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?

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 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?

Wednesday, March 7, 2012

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 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 ru
n
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 examp
le) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to us
e that option, you
either have to add something to the job that kicks out all users, or live wi
th 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 place
d
> the database in question into single user mode and run the same task on th
e
> 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 i
t
> 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 statemen
t not
> processed. Database needs to be in single user mode.

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 Errors

Hello,
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I have
selected the option to check integrity before doing backup. If anything fails,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
================================================================================ Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
================================================================================
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
--
Message posted via http://www.sqlmonster.comThe most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ================================================================================> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ================================================================================> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>> Hello,
>[quoted text clipped - 59 lines]
>> Sameer.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
>>The most likely cause of db integrity issues (especially recurring ones)
>>is
>>bad hardware. I would run some intensive hardware tests to find the root
>>cause.
>> Hello,
>>[quoted text clipped - 59 lines]
>> Sameer.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores to
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likely
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might want
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>> Thanks Andrew,
>[quoted text clipped - 17 lines]
>> Sameer.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
>>It still sounds like hardware related. Do you have a UPS and are you
>>confident of it's condition? But I just re-read your original post and
>>missed the part in which you were using Repair Allow data loss. That does
>>exactly what it says. So to fix the problems it has to destroy or loose
>>some data. The problem is that you don't know what data you loose and
>>your
>>data integrity is not lost. Your best bet for a situation such as that is
>>to backup from the last known good backup. Since you are using simple
>>mode
>>you can never do a restore to a point in time. That limits your restores
>>to
>>the last full or differential backup. If you only do that once a night
>>you
>>can loose up to 24 hours worth of work. You should rethink your backup
>>strategy. The fact that you have been doing this for some time now means
>>that you probably do not have a valid Full backup anymore. They most
>>likely
>>all have some form of corruption in them. Either from what is causing the
>>issues or from you doing the repairs and allowing data loss. You might
>>want
>>to consider creating a new db and copying the data out from one and back
>>into another with referential integrity constraints to see where you may
>>have bad data.
>> Thanks Andrew,
>>[quoted text clipped - 17 lines]
>> Sameer.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on one
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOUT
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
--
Message posted via http://www.sqlmonster.com|||ALTER DATABASE dbname SET SINGLE_USER WITH ROILLBACK IMMEDIATE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:5863a80856c4f@.uwe...
> While the database is online and being used, I would like to stop all future
> users from connecting from it, kill all current users logged-in and then
> change the db to a single user mode to be repaired.
> Could you please elaborate it more ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Wow, it worked like a charm....
How do I reset it back to multi-user after I finish repairing it ?
Thanks a million.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||ALTER DATABASE dbname SET MULTI_USER
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:5863d7195c667@.uwe...
> Wow, it worked like a charm....
> How do I reset it back to multi-user after I finish repairing it ?
> Thanks a million.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||Thanks Mr. Karaszi ... I appreciate it !
Now only if this recurring Integrity errors would vanish away after I replace
the RAM, hopefully ....
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||You are not using a RAM disk by any chance are you?
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on
> one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption
> came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away
> WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||Hi Andrew,
Thanks for coming back....what's a RAM disk ?
We're using a simple Primary Hard Disk being mirrored to a Secondary Hard
Disk using Windows Disk Mirroring functionality.
For Memory, we use 2 X 512 MB chips on 2 dimm slots.
Next plan is to buy 1 X 1GB memory chip and put it in 1 dimm slot and if need
be, buy another one and fill the last dimm slot.
--
Message posted via http://www.sqlmonster.com|||If you don't know then you are most likely not using it<g>. But you are
using software raid vs. hardware. I don't know if I would trust the
software mirroring. Maybe it's OK but that would be my guess. You might
want to look into a lug in card to do the mirroring.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:58683e30c7369@.uwe...
> Hi Andrew,
> Thanks for coming back....what's a RAM disk ?
> We're using a simple Primary Hard Disk being mirrored to a Secondary Hard
> Disk using Windows Disk Mirroring functionality.
> For Memory, we use 2 X 512 MB chips on 2 dimm slots.
> Next plan is to buy 1 X 1GB memory chip and put it in 1 dimm slot and if
> need
> be, buy another one and fill the last dimm slot.
> --
> Message posted via http://www.sqlmonster.com|||1. Unfortunately, Hardware Mirroring is out of question for our small company,
atleast for now.
But just to verify if Software Mirroring is the cause, then I will
temporarily disable it and observe what happens.
2. I usually get corruptions in 2 tables (each are over half-a-million
records). I don't have any additional indexes in those tables apart from the
Primary Key [Id] column.
......2.1 Is it possible that indexes get corrupted for heavy tables ? If
so, how do I safely rebuild indexes
3. The 2 tables I mentioned above, each table gets populated by its own
corresponding Client VB App developed by one of the consultatnts we had hired.
However, even when I do inspect the code and design of the app, I don't seem
to find any flaws.
......3.1 Could those client apps causing the trouble ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||It makes sense that the two most heavily used tables are the ones you see
the most issues with. Since they are changing more than the others there is
more opportunity for issues to arise. But the act of Inserting , Updating
and Deleting too much should not be a concern. Indexes don't get corrupted
after you reach a certain change threshold. I still think the hardware or
now maybe the mirroring software is the root cause. Hardware controllers
that do mirroring these days are extremely inexpensive. Most systems have
the capability built into the mother boards now. I think you will find it
is cheaper than you think and worth exploring.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:586f11a2109af@.uwe...
> 1. Unfortunately, Hardware Mirroring is out of question for our small
> company,
> atleast for now.
> But just to verify if Software Mirroring is the cause, then I will
> temporarily disable it and observe what happens.
>
> 2. I usually get corruptions in 2 tables (each are over half-a-million
> records). I don't have any additional indexes in those tables apart from
> the
> Primary Key [Id] column.
> ......2.1 Is it possible that indexes get corrupted for heavy tables ?
> If
> so, how do I safely rebuild indexes
>
> 3. The 2 tables I mentioned above, each table gets populated by its own
> corresponding Client VB App developed by one of the consultatnts we had
> hired.
> However, even when I do inspect the code and design of the app, I don't
> seem
> to find any flaws.
> ......3.1 Could those client apps causing the trouble ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1

Integrity Check Errors

Sunday, February 19, 2012

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
SSRS should accommodate SSAS native output format without flattening.
This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

Integrating Analysis Services with Reporting Services
http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
Rant: Reporting Services and Analysis Services
http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
The state of Reporting Services UDM (Analysis Services) integration
http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a
|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.

Friday, March 9, 2012

Integrity Checks Failed?

Integrity Checks Failed?

Integrity Checks Failed?

Wednesday, March 7, 2012

Integrity check failures during maintenance plans

Integrity check failures during maintenance plans

Integrity check failures during maintenance plans

Integrity Check Errors

Integrity Check Errors

Integrity Check Errors

Sunday, February 19, 2012

Integration between SSRS and SSAS

Integrated Security

Blog Archive

About Me