ERROR: Subreport: plan

Showing posts with label plan. Show all posts

Tuesday, March 27, 2012

Estimated Query Plan

Hi,

I am writing a client application that shows estimated queries plans and statistics. I know how to obtain estimated plans by using SQL Server Management Studio. But is it possible to obtain by using database functions?

I have found sys.dm_exec_query_plan, but it seems that this function can only be used for executed (or executing) queries...

Thanks

SET SHOWPLAN_XML ON

exec YourSP

SET SHOWPLAN_XML OFF

|||

I do not want to obtain the execution plan for a stored procedure. I do want to use a stored procedure (that may be invoked by a client application) to obtain the execution plan for a user-submmitted query. (It is just like SQL Manager Studio does).

Thanks

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A'--Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?
Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
..
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?
|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?
sql

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicat
e
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archi.../07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predic
ate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the difference
s
> between Seek Predicate and Predicate?

Estimated Execution Plan Fails, XML Error?

Good afternoon, all. Whenever I try to use Query | Display Estimated
Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
message:
Error processing execution plan results. The error message is:
There is an error in XML document (1, 501).
There is an unclosed literal string. Line 1, position 501.
The only suggestion I've heard of is to instal SQL SPs, but that didn't
help. This is SQL 2K5 running on Win2K3. I don't believe there's any
strangeness like odd characters in instance names, and all language settings
are default. I've seen this before on another server, but Google has nothing.
Any ideas? Thanks!
I think I heard that the XML used for this can not handle some unusual
object names, but at best that is a long shot.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Aug 2007 13:32:02 -0700, JonOfAllTrades
<JonOfAllTrades@.discussions.microsoft.com> wrote:

> Good afternoon, all. Whenever I try to use Query | Display Estimated
>Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
>message:
>Error processing execution plan results. The error message is:
>There is an error in XML document (1, 501).
>There is an unclosed literal string. Line 1, position 501.
> The only suggestion I've heard of is to instal SQL SPs, but that didn't
>help. This is SQL 2K5 running on Win2K3. I don't believe there's any
>strangeness like odd characters in instance names, and all language settings
>are default. I've seen this before on another server, but Google has nothing.
> Any ideas? Thanks!

Estimated execution plan

I've been using the estimated execution plan feature in SQL 2005 and I see that the "cost %" is sometimes just wacko (e.g. 333% or 25173%).

That makes me wonder just how much I can count on the estimated subtree cost value for a given path in the plan. I relied on this value (subtree cost) in SQL 2000 and would occasionally see the same kinds of wacko "cost%". I was hoping that 2005 would clean this up.

I am aware that the calculations in the plan depend upon a representative database of data. I think I am very experienced using the estimated execution plan tool in SQL 2000. I'm just concerned that, with the new release, that perhaps I shouldn't trust it as much, especially after continuing to see the wierdness with the cost% displayed.

Does anyone have any reliable information about whether the estimated subtree cost is something I should rely upon, even if the cost% is screwy?

Thanks for any insight.

To clarify the original question:

Is the calculation for an estimated subtree cost calculated independently of the displayed "cost %" for an individual, graphically-displayed (logical | physical) operator in the estimated execution plan?

I would guess that it could be independent since the subtree cost should be: a percent of the overall procedure being estimated

whereas the "cost %" for an individual, graphically-displayed (logical | physical) operator is a percent of the estimated cost of the command to be executed.

On the other-hand, the same operator cost value could also be used in both calculations.

Probably only the person who wrote that code might know, so I will do some experimenting to see if the values equate between SQL versions. Also, I am overall more pleased with what I see in the 2005 estimated execution plan presentation so don't judge this as a strong complaint. The new version is more useful.

Estimated Execution Plan

How do I display this in Query Analyzer ?Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.sql

Estimated Execution Plan

How do I display this in Query Analyzer ?
Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>
|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>
|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.

Estimated Execution Plan

How do I display this in Query Analyzer ?Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.

Estimated cost shows 300% in query plan

Hello all,
On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
(Windows Server 2003), most of the query plans I see are showing, on the
estimated cost of each step of the plan, more than 100% (e.g. 300% for a
seek).
Does anyone have an explanation or has already seen that ?
tia,
Rudi Bruchez
MCDBAIt's a bug in the graphical query plans when dealing with parallel query
plans. I have seen percentage reaching 4 figures even. I don't think it will
be fixed, considering it wasn't fixed in SP4.
--
Jacco Schalkwijk
SQL Server MVP
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:138syoixibdyn$.14zc2fkwrr1j5.dlg@.40tude.net...
> Hello all,
> On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
> (Windows Server 2003), most of the query plans I see are showing, on the
> estimated cost of each step of the plan, more than 100% (e.g. 300% for a
> seek).
> Does anyone have an explanation or has already seen that ?
> tia,
> Rudi Bruchez
> MCDBA|||On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
> It's a bug in the graphical query plans when dealing with parallel query
> plans. I have seen percentage reaching 4 figures even. I don't think it will
> be fixed, considering it wasn't fixed in SP4.
Hi,
Thanks for the feedback. My problem is that I've set the maximum degree of
parallelism to 1 at the server level, and there's no mention of parallelism
on the graphical plan. Should this still appear ?
Btw, even with a maxdop to 1, I still see some "degree of parallelism"
event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
Is it explanable ?
thanks again,
Rudi Bruchez|||On Thu, 27 Oct 2005 17:05:35 +0200, Rudi Bruchez
<rudi#no-spam#at.babaluga.com> wrote:
>On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
>(Windows Server 2003), most of the query plans I see are showing, on the
>estimated cost of each step of the plan, more than 100% (e.g. 300% for a
>seek).
>Does anyone have an explanation or has already seen that ?
It happens.
:)
How about the after-query plan?
J.|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>> It's a bug in the graphical query plans when dealing with parallel
>> query plans. I have seen percentage reaching 4 figures even. I don't
>> think it will be fixed, considering it wasn't fixed in SP4.
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:
> Rudi Bruchez wrote:
>> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>> It's a bug in the graphical query plans when dealing with parallel
>> query plans. I have seen percentage reaching 4 figures even. I don't
>> think it will be fixed, considering it wasn't fixed in SP4.
>> Hi,
>> Thanks for the feedback. My problem is that I've set the maximum
>> degree of parallelism to 1 at the server level, and there's no
>> mention of parallelism on the graphical plan. Should this still
>> appear ?
>> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
>> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
>> Is it explanable ?
>> thanks again,
>> Rudi Bruchez
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

Estimated cost shows 300% in query plan

How about the after-query plan?
J.
|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

Estimated cost shows 300% in query plan

How about the after-query plan?
J.|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.sql

Wednesday, March 21, 2012

Errors when rebuilding indexes

We have SQL Server 2005 set up and we are seeing the following error messages
in our logs from Daily maintenance Plan.
Please comment on the severity of these errors.
Thanks,
mkb
================================================
Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE =
OFF )
" failed with the following error: "SQL Server detected a logical
consistency-based I/O error: incorrect pageid (expected 1:2676; actual
14616:395852048). It occurred during a read of page (1:2676) in database ID
13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_W ebApp.mdf'. Additional
messages in the SQL Server error log or system event log may provide more
detail. This is a severe error condition that threatens database integrity
and must be corrected immediately. Complete a full database consistency check
(DBCC CHECKDB). This error can be caused by many factors; for more
information, see SQL Server Books Online.
The statement has been terminated.". Possible failure reasons: Problems with
the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.
================================================== =
Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
REORGANIZE WITH ( LOB_COMPACTION = OFF )
" failed with the following error: "SQL Server detected a logical
consistency-based I/O error: incorrect pageid (expected 1:2653; actual
47686:707280961). It occurred during a read of page (1:2653) in database ID
13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_W ebApp.mdf'. Additional
messages in the SQL Server error log or system event log may provide more
detail. This is a severe error condition that threatens database integrity
and must be corrected immediately. Complete a full database consistency check
(DBCC CHECKDB). This error can be caused by many factors; for more
information, see SQL Server Books Online.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
================================================== ==
Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "An internal error occurred in DBCC that
prevented further processing. Contact Customer Support Services.
Check terminated. A failure was detected while collecting facts. Possibly
tempdb out of space or a system table is inconsistent. Check previous
errors.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.
================================================== =
Hi
It seems that there is some corruption in the database. Have you had any
hardware issues? Is there any other information in the SQL Server log or
Windows Event Logs?
You could try dropping the index and running DBCC CHECKDB again. If it works
then re-create the index and run it again. If you don't find out what caused
this then it could happen again.
John
"MKBOBBA" wrote:

> We have SQL Server 2005 set up and we are seeing the following error messages
> in our logs from Daily maintenance Plan.
> Please comment on the severity of these errors.
> Thanks,
> mkb
> ================================================
> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
> REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE =
> OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2676; actual
> 14616:395852048). It occurred during a read of page (1:2676) in database ID
> 13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_W ebApp.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency check
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.
> The statement has been terminated.". Possible failure reasons: Problems with
> the query, "ResultSet" property not set correctly, parameters not set
> correctly, or connection not established correctly.
>
> ================================================== =
> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
> REORGANIZE WITH ( LOB_COMPACTION = OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2653; actual
> 47686:707280961). It occurred during a read of page (1:2653) in database ID
> 13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_W ebApp.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency check
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameters
> not set correctly, or connection not established correctly.
> ================================================== ==
> Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
> " failed with the following error: "An internal error occurred in DBCC that
> prevented further processing. Contact Customer Support Services.
> Check terminated. A failure was detected while collecting facts. Possibly
> tempdb out of space or a system table is inconsistent. Check previous
> errors.". Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly, or connection not
> established correctly.
> ================================================== =
>
|||And I forgot to add.. Make sure you have a good backup before doing this! You
could try restoring a backup on a different machine to try your tests out
before working on live (providing you have the hardware!)
John
"MKBOBBA" wrote:

> We have SQL Server 2005 set up and we are seeing the following error messages
> in our logs from Daily maintenance Plan.
> Please comment on the severity of these errors.
> Thanks,
> mkb
> ================================================
> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
> REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE =
> OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2676; actual
> 14616:395852048). It occurred during a read of page (1:2676) in database ID
> 13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_W ebApp.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency check
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.
> The statement has been terminated.". Possible failure reasons: Problems with
> the query, "ResultSet" property not set correctly, parameters not set
> correctly, or connection not established correctly.
>
> ================================================== =
> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
> REORGANIZE WITH ( LOB_COMPACTION = OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2653; actual
> 47686:707280961). It occurred during a read of page (1:2653) in database ID
> 13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_W ebApp.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency check
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameters
> not set correctly, or connection not established correctly.
> ================================================== ==
> Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
> " failed with the following error: "An internal error occurred in DBCC that
> prevented further processing. Contact Customer Support Services.
> Check terminated. A failure was detected while collecting facts. Possibly
> tempdb out of space or a system table is inconsistent. Check previous
> errors.". Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly, or connection not
> established correctly.
> ================================================== =
>

Errors when rebuilding indexes

We have SQL Server 2005 set up and we are seeing the following error messages
in our logs from Daily maintenance Plan.
Please comment on the severity of these errors.
Thanks,
mkb
================================================ Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
" failed with the following error: "SQL Server detected a logical
consistency-based I/O error: incorrect pageid (expected 1:2676; actual
14616:395852048). It occurred during a read of page (1:2676) in database ID
13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_WebApp.mdf'. Additional
messages in the SQL Server error log or system event log may provide more
detail. This is a severe error condition that threatens database integrity
and must be corrected immediately. Complete a full database consistency check
(DBCC CHECKDB). This error can be caused by many factors; for more
information, see SQL Server Books Online.
The statement has been terminated.". Possible failure reasons: Problems with
the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.
===================================================
Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
REORGANIZE WITH ( LOB_COMPACTION = OFF )
" failed with the following error: "SQL Server detected a logical
consistency-based I/O error: incorrect pageid (expected 1:2653; actual
47686:707280961). It occurred during a read of page (1:2653) in database ID
13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_WebApp.mdf'. Additional
messages in the SQL Server error log or system event log may provide more
detail. This is a severe error condition that threatens database integrity
and must be corrected immediately. Complete a full database consistency check
(DBCC CHECKDB). This error can be caused by many factors; for more
information, see SQL Server Books Online.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
====================================================
Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "An internal error occurred in DBCC that
prevented further processing. Contact Customer Support Services.
Check terminated. A failure was detected while collecting facts. Possibly
tempdb out of space or a system table is inconsistent. Check previous
errors.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.
===================================================Hi
It seems that there is some corruption in the database. Have you had any
hardware issues? Is there any other information in the SQL Server log or
Windows Event Logs?
You could try dropping the index and running DBCC CHECKDB again. If it works
then re-create the index and run it again. If you don't find out what caused
this then it could happen again.
John
"MKBOBBA" wrote:
> We have SQL Server 2005 set up and we are seeing the following error messages
> in our logs from Daily maintenance Plan.
> Please comment on the severity of these errors.
> Thanks,
> mkb
> ================================================> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
> REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE => OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2676; actual
> 14616:395852048). It occurred during a read of page (1:2676) in database ID
> 13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_WebApp.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency check
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.
> The statement has been terminated.". Possible failure reasons: Problems with
> the query, "ResultSet" property not set correctly, parameters not set
> correctly, or connection not established correctly.
>
> ===================================================> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
> REORGANIZE WITH ( LOB_COMPACTION = OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2653; actual
> 47686:707280961). It occurred during a read of page (1:2653) in database ID
> 13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_WebApp.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency check
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameters
> not set correctly, or connection not established correctly.
> ====================================================> Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
> " failed with the following error: "An internal error occurred in DBCC that
> prevented further processing. Contact Customer Support Services.
> Check terminated. A failure was detected while collecting facts. Possibly
> tempdb out of space or a system table is inconsistent. Check previous
> errors.". Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly, or connection not
> established correctly.
> ===================================================>|||And I forgot to add.. Make sure you have a good backup before doing this! You
could try restoring a backup on a different machine to try your tests out
before working on live (providing you have the hardware!)
John
"MKBOBBA" wrote:
> We have SQL Server 2005 set up and we are seeing the following error messages
> in our logs from Daily maintenance Plan.
> Please comment on the severity of these errors.
> Thanks,
> mkb
> ================================================> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
> REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE => OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2676; actual
> 14616:395852048). It occurred during a read of page (1:2676) in database ID
> 13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_WebApp.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency check
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.
> The statement has been terminated.". Possible failure reasons: Problems with
> the query, "ResultSet" property not set correctly, parameters not set
> correctly, or connection not established correctly.
>
> ===================================================> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllUserData]
> REORGANIZE WITH ( LOB_COMPACTION = OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2653; actual
> 47686:707280961). It occurred during a read of page (1:2653) in database ID
> 13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\SP_IntranetMySites_WebApp.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency check
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameters
> not set correctly, or connection not established correctly.
> ====================================================> Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
> " failed with the following error: "An internal error occurred in DBCC that
> prevented further processing. Contact Customer Support Services.
> Check terminated. A failure was detected while collecting facts. Possibly
> tempdb out of space or a system table is inconsistent. Check previous
> errors.". Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly, or connection not
> established correctly.
> ===================================================>

Errors when rebuilding indexes

We have SQL Server 2005 set up and we are seeing the following error message
s
in our logs from Daily maintenance Plan.
Please comment on the severity of these errors.
Thanks,
mkb
========================================
========
Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllU
serData]
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE
=
OFF )
" failed with the following error: "SQL Server detected a logical
consistency-based I/O error: incorrect pageid (expected 1:2676; actual
14616:395852048). It occurred during a read of page (1:2676) in database ID
13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL. 1\\MSSQL\\DATA\\SP_IntranetMySites_WebAp
p.mdf'. Additional
messages in the SQL Server error log or system event log may provide more
detail. This is a severe error condition that threatens database integrity
and must be corrected immediately. Complete a full database consistency chec
k
(DBCC CHECKDB). This error can be caused by many factors; for more
information, see SQL Server Books Online.
The statement has been terminated.". Possible failure reasons: Problems with
the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.
========================================
===========
Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[AllU
serData]
REORGANIZE WITH ( LOB_COMPACTION = OFF )
" failed with the following error: "SQL Server detected a logical
consistency-based I/O error: incorrect pageid (expected 1:2653; actual
47686:707280961). It occurred during a read of page (1:2653) in database ID
13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL. 1\\MSSQL\\DATA\\SP_IntranetMySites_WebAp
p.mdf'. Additional
messages in the SQL Server error log or system event log may provide more
detail. This is a severe error condition that threatens database integrity
and must be corrected immediately. Complete a full database consistency chec
k
(DBCC CHECKDB). This error can be caused by many factors; for more
information, see SQL Server Books Online.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
========================================
============
Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "An internal error occurred in DBCC that
prevented further processing. Contact Customer Support Services.
Check terminated. A failure was detected while collecting facts. Possibly
tempdb out of space or a system table is inconsistent. Check previous
errors.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.
========================================
===========Hi
It seems that there is some corruption in the database. Have you had any
hardware issues? Is there any other information in the SQL Server log or
Windows Event Logs?
You could try dropping the index and running DBCC CHECKDB again. If it works
then re-create the index and run it again. If you don't find out what caused
this then it could happen again.
John
"MKBOBBA" wrote:

> We have SQL Server 2005 set up and we are seeing the following error messa
ges
> in our logs from Daily maintenance Plan.
> Please comment on the severity of these errors.
> Thanks,
> mkb
> ========================================
========
> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[Al
lUserData]
> REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLIN
E =
> OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2676; actual
> 14616:395852048). It occurred during a read of page (1:2676) in database I
D
> 13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL. 1\\MSSQL\\DATA\\SP_IntranetMySites_WebAp
p.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency ch
eck
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.
> The statement has been terminated.". Possible failure reasons: Problems wi
th
> the query, "ResultSet" property not set correctly, parameters not set
> correctly, or connection not established correctly.
>
> ========================================
===========
> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[Al
lUserData]
> REORGANIZE WITH ( LOB_COMPACTION = OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2653; actual
> 47686:707280961). It occurred during a read of page (1:2653) in database I
D
> 13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL. 1\\MSSQL\\DATA\\SP_IntranetMySites_WebAp
p.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency ch
eck
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameter
s
> not set correctly, or connection not established correctly.
> ========================================
============
> Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
> " failed with the following error: "An internal error occurred in DBCC tha
t
> prevented further processing. Contact Customer Support Services.
> Check terminated. A failure was detected while collecting facts. Possibly
> tempdb out of space or a system table is inconsistent. Check previous
> errors.". Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly, or connection no
t
> established correctly.
> ========================================
===========
>|||And I forgot to add.. Make sure you have a good backup before doing this! Yo
u
could try restoring a backup on a different machine to try your tests out
before working on live (providing you have the hardware!)
John
"MKBOBBA" wrote:

> We have SQL Server 2005 set up and we are seeing the following error messa
ges
> in our logs from Daily maintenance Plan.
> Please comment on the severity of these errors.
> Thanks,
> mkb
> ========================================
========
> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[Al
lUserData]
> REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLIN
E =
> OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2676; actual
> 14616:395852048). It occurred during a read of page (1:2676) in database I
D
> 13 at offset 0x000000014e8000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL. 1\\MSSQL\\DATA\\SP_IntranetMySites_WebAp
p.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency ch
eck
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.
> The statement has been terminated.". Possible failure reasons: Problems wi
th
> the query, "ResultSet" property not set correctly, parameters not set
> correctly, or connection not established correctly.
>
> ========================================
===========
> Executing the query "ALTER INDEX [AllUserData_PK] ON [dbo].[Al
lUserData]
> REORGANIZE WITH ( LOB_COMPACTION = OFF )
> " failed with the following error: "SQL Server detected a logical
> consistency-based I/O error: incorrect pageid (expected 1:2653; actual
> 47686:707280961). It occurred during a read of page (1:2653) in database I
D
> 13 at offset 0x000000014ba000 in file 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL. 1\\MSSQL\\DATA\\SP_IntranetMySites_WebAp
p.mdf'. Additional
> messages in the SQL Server error log or system event log may provide more
> detail. This is a severe error condition that threatens database integrity
> and must be corrected immediately. Complete a full database consistency ch
eck
> (DBCC CHECKDB). This error can be caused by many factors; for more
> information, see SQL Server Books Online.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameter
s
> not set correctly, or connection not established correctly.
> ========================================
============
> Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
> " failed with the following error: "An internal error occurred in DBCC tha
t
> prevented further processing. Contact Customer Support Services.
> Check terminated. A failure was detected while collecting facts. Possibly
> tempdb out of space or a system table is inconsistent. Check previous
> errors.". Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly, or connection no
t
> established correctly.
> ========================================
===========
>sql

errors whan running backup plan

My event log has the following entries every day ( because the plan runs every
day)
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels Backup
Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by Schedule
20 (Schedule 1). The last step to run was step 1 (Step 1).
That error message follows four other entries like this.
Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump devices:
1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
I seem to have all the backups in the folder that there should be, but then what
is happening with the first error message?
I also can manually make a backup without any error messages appearing so
something is wrong with my plan but what? it doesn't look that complicated to
set up.Do you have any other steps on this maintenance plan such as an integrity
check?
Jim
"mmac" <mmac@.junkmail.bin> wrote in message
news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> My event log has the following entries every day ( because the plan runs
every
> day)
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels
Backup
> Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
> 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
Schedule
> 20 (Schedule 1). The last step to run was step 1 (Step 1).
> That error message follows four other entries like this.
> Database backed up: Database: model, creation date(time):
2000/08/06(01:40:52),
> pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
devices:
> 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft
SQL
> Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> I seem to have all the backups in the folder that there should be, but
then what
> is happening with the first error message?
> I also can manually make a backup without any error messages appearing so
> something is wrong with my plan but what? it doesn't look that complicated
to
> set up.
>|||Do you get any better errormessages if you specify a report file for the
plan and check that report file?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mmac" <mmac@.junkmail.bin> wrote in message
news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> My event log has the following entries every day ( because the plan runs
every
> day)
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Michaels
Backup
> Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked on:
> 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
Schedule
> 20 (Schedule 1). The last step to run was step 1 (Step 1).
> That error message follows four other entries like this.
> Database backed up: Database: model, creation date(time):
2000/08/06(01:40:52),
> pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
devices:
> 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft
SQL
> Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> I seem to have all the backups in the folder that there should be, but
then what
> is happening with the first error message?
> I also can manually make a backup without any error messages appearing so
> something is wrong with my plan but what? it doesn't look that complicated
to
> set up.
>|||Ahh that was helpful.
here is what I get:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
...
So I removed the system databases leaving only my database from the backup
and it went fine. do I need to save the system databases differently? or at
all?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Do you get any better errormessages if you specify a report file for the
> plan and check that report file?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "mmac" <mmac@.junkmail.bin> wrote in message
> news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> > My event log has the following entries every day ( because the plan runs
> every
> > day)
> >
> > SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan
'Michaels
> Backup
> > Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked
on:
> > 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
> Schedule
> > 20 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > That error message follows four other entries like this.
> >
> > Database backed up: Database: model, creation date(time):
> 2000/08/06(01:40:52),
> > pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
> devices:
> > 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft
> SQL
> > Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> >
> > I seem to have all the backups in the folder that there should be, but
> then what
> > is happening with the first error message?
> > I also can manually make a backup without any error messages appearing
so
> > something is wrong with my plan but what? it doesn't look that
complicated
> to
> > set up.
> >
> >
>|||> So I removed the system databases leaving only my database from the
backup
> and it went fine. do I need to save the system databases differently? or
at
> all?
Yes, create a different maint pan for master, model and msdb and do backup
once a day... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mmac" <no@.thanks.com> wrote in message
news:eByJn9X$DHA.320@.TK2MSFTNGP10.phx.gbl...
> Ahh that was helpful.
> here is what I get:
>
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
> Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30 PM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
> 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
> be in single user mode.
> ...
> So I removed the system databases leaving only my database from the
backup
> and it went fine. do I need to save the system databases differently? or
at
> all?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Do you get any better errormessages if you specify a report file for the
> > plan and check that report file?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "mmac" <mmac@.junkmail.bin> wrote in message
> > news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> > > My event log has the following entries every day ( because the plan
runs
> > every
> > > day)
> > >
> > > SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan
> 'Michaels
> > Backup
> > > Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked
> on:
> > > 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
> > Schedule
> > > 20 (Schedule 1). The last step to run was step 1 (Step 1).
> > >
> > > That error message follows four other entries like this.
> > >
> > > Database backed up: Database: model, creation date(time):
> > 2000/08/06(01:40:52),
> > > pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
> > devices:
> > > 1, device information: (FILE=1, TYPE=DISK: {'C:\Program
Files\Microsoft
> > SQL
> > > Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> > >
> > > I seem to have all the backups in the folder that there should be, but
> > then what
> > > is happening with the first error message?
> > > I also can manually make a backup without any error messages appearing
> so
> > > something is wrong with my plan but what? it doesn't look that
> complicated
> > to
> > > set up.
> > >
> > >
> >
> >
>|||and they will backup in "single user mode"?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OUnYqaf$DHA.688@.tk2msftngp13.phx.gbl...
> > So I removed the system databases leaving only my database from the
> backup
> > and it went fine. do I need to save the system databases differently? or
> at
> > all?
> Yes, create a different maint pan for master, model and msdb and do backup
> once a day... :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "mmac" <no@.thanks.com> wrote in message
> news:eByJn9X$DHA.320@.TK2MSFTNGP10.phx.gbl...
> > Ahh that was helpful.
> > here is what I get:
> >
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
> > Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30 PM
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
> > 'MASTER'.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> > [1] Database master: Check Data and Index Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Repair statement not processed. Database needs
> to
> > be in single user mode.
> > ...
> > So I removed the system databases leaving only my database from the
> backup
> > and it went fine. do I need to save the system databases differently? or
> at
> > all?
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > Do you get any better errormessages if you specify a report file for the
> > > plan and check that report file?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "mmac" <mmac@.junkmail.bin> wrote in message
> > > news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> > > > My event log has the following entries every day ( because the plan
> runs
> > > every
> > > > day)
> > > >
> > > > SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan
> > 'Michaels
> > > Backup
> > > > Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed - Invoked
> > on:
> > > > 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked by
> > > Schedule
> > > > 20 (Schedule 1). The last step to run was step 1 (Step 1).
> > > >
> > > > That error message follows four other entries like this.
> > > >
> > > > Database backed up: Database: model, creation date(time):
> > > 2000/08/06(01:40:52),
> > > > pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of dump
> > > devices:
> > > > 1, device information: (FILE=1, TYPE=DISK: {'C:\Program
> Files\Microsoft
> > > SQL
> > > > Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> > > >
> > > > I seem to have all the backups in the folder that there should be, but
> > > then what
> > > > is happening with the first error message?
> > > > I also can manually make a backup without any error messages appearing
> > so
> > > > something is wrong with my plan but what? it doesn't look that
> > complicated
> > > to
> > > > set up.
> > > >
> > > >
> > >
> > >
> >
> >
>|||There's no need to set the single user mode unless you check the dreaded
"Attempt to repair minor problems" under the integrity check part. I do not
recommend using this option at all. If I have a problem with a corrupt
database, I want to know that.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mmac" <mmac@.junkmail.bin> wrote in message
news:uy7MGro$DHA.2212@.TK2MSFTNGP10.phx.gbl...
> and they will backup in "single user mode"?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OUnYqaf$DHA.688@.tk2msftngp13.phx.gbl...
> > > So I removed the system databases leaving only my database from the
> > backup
> > > and it went fine. do I need to save the system databases differently?
or
> > at
> > > all?
> >
> > Yes, create a different maint pan for master, model and msdb and do
backup
> > once a day... :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "mmac" <no@.thanks.com> wrote in message
> > news:eByJn9X$DHA.320@.TK2MSFTNGP10.phx.gbl...
> > > Ahh that was helpful.
> > > here is what I get:
> > >
> > >
> > > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server
> > > 'MYSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
> > > Starting maintenance plan 'Michaels Backup Plan' on 2/27/2004 12:08:30
PM
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
database
> > > 'MASTER'.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
> > > [1] Database master: Check Data and Index Linkage...
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]Repair statement not processed. Database
needs
> > to
> > > be in single user mode.
> > > ...
> > > So I removed the system databases leaving only my database from the
> > backup
> > > and it went fine. do I need to save the system databases differently?
or
> > at
> > > all?
> > >
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > message news:um0i$ME$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > > Do you get any better errormessages if you specify a report file for
the
> > > > plan and check that report file?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > > >
> > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "mmac" <mmac@.junkmail.bin> wrote in message
> > > > news:u84PwfC$DHA.1464@.tk2msftngp13.phx.gbl...
> > > > > My event log has the following entries every day ( because the
plan
> > runs
> > > > every
> > > > > day)
> > > > >
> > > > > SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan
> > > 'Michaels
> > > > Backup
> > > > > Plan'' (0x8E4169EC6E0A93468357CC016A8A9186) - Status: Failed -
Invoked
> > > on:
> > > > > 2004-02-11 02:00:00 - Message: The job failed. The Job was invoked
by
> > > > Schedule
> > > > > 20 (Schedule 1). The last step to run was step 1 (Step 1).
> > > > >
> > > > > That error message follows four other entries like this.
> > > > >
> > > > > Database backed up: Database: model, creation date(time):
> > > > 2000/08/06(01:40:52),
> > > > > pages dumped: 91, first LSN: 5:22:1, last LSN: 5:24:1, number of
dump
> > > > devices:
> > > > > 1, device information: (FILE=1, TYPE=DISK: {'C:\Program
> > Files\Microsoft
> > > > SQL
> > > > > Server\MSSQL\BACKUP\model_db_200402110200.BAK'}).
> > > > >
> > > > > I seem to have all the backups in the folder that there should be,
but
> > > > then what
> > > > > is happening with the first error message?
> > > > > I also can manually make a backup without any error messages
appearing
> > > so
> > > > > something is wrong with my plan but what? it doesn't look that
> > > complicated
> > > > to
> > > > > set up.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

errors whan running backup plan

Monday, March 19, 2012

Errors of System DB Backup T-logJobs for Maint Plan

I have been bothered with the error signs of the DB Backup
jobs for my DB Maint Plan. These job errors were shown in
SQL Server Agent node of the Enterprise Manager.
After checking the DB Maint Plan history, I found the
errors were due to the Backup T-log job errors on system
databases, e.g. master, msdb. Later on I found out that
we can set master to Full recovery model, but still no T-
log backup choice available, while tempdb can not even be
set to Full model.
If I still want to select 'All Databases' in my Maint
Plan, how can I avoid these Maint Plan job errors in the
SQL Server Agent node? How do I decide the DB recovery
model (Full or Simple) for the system databases in my
database servers when configuring my Maint Plan?
Furthermore, I have heard that there are no T-log backup
device for the system databases, only for user databases.
What exactly does this mean?
Thanks in Advance.
JeffreyI normally create one maint plan for my sysdbs this way you can avoid that
and some other errors.
You cannot restore the master database like you restore the users db, the
master db has to be rebuild. Because of the size of your system db i
normally backed them up twice a day, and overwrite every two weeks and i
have it set to simple.
Take a look at BOL (books online) to understand more about backup models.
Yovan Fernandez
"Jeffrey" <jwang@.dot.state.tx.us> wrote in message
news:0bd901c377d7$bcf03a40$a001280a@.phx.gbl...
> I have been bothered with the error signs of the DB Backup
> jobs for my DB Maint Plan. These job errors were shown in
> SQL Server Agent node of the Enterprise Manager.
> After checking the DB Maint Plan history, I found the
> errors were due to the Backup T-log job errors on system
> databases, e.g. master, msdb. Later on I found out that
> we can set master to Full recovery model, but still no T-
> log backup choice available, while tempdb can not even be
> set to Full model.
> If I still want to select 'All Databases' in my Maint
> Plan, how can I avoid these Maint Plan job errors in the
> SQL Server Agent node? How do I decide the DB recovery
> model (Full or Simple) for the system databases in my
> database servers when configuring my Maint Plan?
> Furthermore, I have heard that there are no T-log backup
> device for the system databases, only for user databases.
> What exactly does this mean?
> Thanks in Advance.
> Jeffrey
>

Friday, March 9, 2012

Errors during Maintenance Plan execution

I am getting error on several database when trying to use
the Maintenance Plan to backup all databses in our SQL
Server 2000 with Service Pack 3.
The following is the text generated from one of the
database in the log file. The other databases generate
the same error message
Log portion============================== [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'SQLCatalog'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[18] Database SQLCatalog: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
========================================
Any ideas on how to handle this? Is this a matter of
running the maintenance plan at a different time?You've checked the option to "Attempt to repair minor problems" for which
SQL Server tries to set the db in single user mode which will fail if you
have users in the database. Remove that option, if you do get a problem with
the database, you want to know about it and be there to make a conscious
decision of your actions at that stage.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> I am getting error on several database when trying to use
> the Maintenance Plan to backup all databses in our SQL
> Server 2000 with Service Pack 3.
> The following is the text generated from one of the
> database in the log file. The other databases generate
> the same error message
> Log portion==============================> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'SQLCatalog'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [18] Database SQLCatalog: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ========================================> Any ideas on how to handle this? Is this a matter of
> running the maintenance plan at a different time?|||That helped. So now I have an error with the transaction
log backup portion of the Maintenace Plan.
Here is the erro
==========================================================Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
failed. [SQLSTATE 42000] (Error 22029). The step failed.
===============================================
I can't find this in the error logs or the backup log so
I'm not sure what is happening
>--Original Message--
>You've checked the option to "Attempt to repair minor
problems" for which
>SQL Server tries to set the db in single user mode which
will fail if you
>have users in the database. Remove that option, if you
do get a problem with
>the database, you want to know about it and be there to
make a conscious
>decision of your actions at that stage.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Jim" <jim.abel@.lmco.com> wrote in message
>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying to
use
>> the Maintenance Plan to backup all databses in our SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
>> state cannot be changed while other users are using the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>
>.
>|||Possibly the database is in simple recovery model. You will not find details
error info from looking at just the job. There's a GUI for this in the main
folder, but I prefer the text report file option that maint wiz has.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:10f501c3be74$0be078d0$a401280a@.phx.gbl...
> That helped. So now I have an error with the transaction
> log backup portion of the Maintenace Plan.
> Here is the erro
> ==========================================================> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
> failed. [SQLSTATE 42000] (Error 22029). The step failed.
> ===============================================> I can't find this in the error logs or the backup log so
> I'm not sure what is happening
> >--Original Message--
> >You've checked the option to "Attempt to repair minor
> problems" for which
> >SQL Server tries to set the db in single user mode which
> will fail if you
> >have users in the database. Remove that option, if you
> do get a problem with
> >the database, you want to know about it and be there to
> make a conscious
> >decision of your actions at that stage.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Jim" <jim.abel@.lmco.com> wrote in message
> >news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> >> I am getting error on several database when trying to
> use
> >> the Maintenance Plan to backup all databses in our SQL
> >> Server 2000 with Service Pack 3.
> >> The following is the text generated from one of the
> >> database in the log file. The other databases generate
> >> the same error message
> >>
> >> Log portion==============================> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> >> state cannot be changed while other users are using the
> >> database 'SQLCatalog'
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> >> DATABASE statement failed.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >> sp_dboption command failed.
> >> [18] Database SQLCatalog: Check Data and Index
> Linkage...
> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in single
> >> user mode.
> >>
> >> The following errors were found:
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in single
> >> user mode.
> >> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >>
> >> ========================================> >>
> >> Any ideas on how to handle this? Is this a matter of
> >> running the maintenance plan at a different time?
> >
> >
> >.
> >|||Hello Jim,
First of all, thanks to Tibor for pointing you in the right direction.
Just as an additional information,
you can refer to the following article which explains how to t-shoot
General SQL Maint Wiz failures,
INF: Troubleshooting Database Maintenance Plan Failures
http://support.microsoft.com/default.aspx?scid=kb;en-us;288577
In case, you are doing Transaction log backups of one of the databases
which is set to
Simple Recovery Mode, you might want to refer to following article
explaining this scenario,
BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292
Please let us know if these suggestions resolve your issue, if not , feel
free to post any further questions you have.
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security."
This posting is provided "AS IS" with no warranties, and confers no rights.
>Content-Class: urn:content-classes:message
>From: "Jim" <jim.abel@.lmco.com>
>Sender: "Jim" <jim.abel@.lmco.com>
>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
<#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Errors during Maintenance Plan execution
>Date: Tue, 9 Dec 2003 08:46:52 -0800
>Lines: 71
>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:319894
>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>That helped. So now I have an error with the transaction
>log backup portion of the Maintenace Plan.
>Here is the erro
>==========================================================>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>failed. [SQLSTATE 42000] (Error 22029). The step failed.
>===============================================>I can't find this in the error logs or the backup log so
>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>problems" for which
>>SQL Server tries to set the db in single user mode which
>will fail if you
>>have users in the database. Remove that option, if you
>do get a problem with
>>the database, you want to know about it and be there to
>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying to
>use
>> the Maintenance Plan to backup all databses in our SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
>> state cannot be changed while other users are using the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>|||All right guys are really helping me out. I looked up
the proble and it has come down to the following The
maintenace Plan says that it cannot do transaction log
backups on the master and the msdb databases.
My question now is how can I exclude those 2 DB's from
the Transaction log backup portion of the maintenance
Plan?
>--Original Message--
>
>Hello Jim,
>First of all, thanks to Tibor for pointing you in the
right direction.
>Just as an additional information,
>you can refer to the following article which explains
how to t-shoot
>General SQL Maint Wiz failures,
>INF: Troubleshooting Database Maintenance Plan Failures
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;288577
>In case, you are doing Transaction log backups of one of
the databases
>which is set to
>Simple Recovery Mode, you might want to refer to
following article
>explaining this scenario,
>BUG: Expired Transaction Log Backups May Not Be Deleted
by Maintenance Plan
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;303292
>Please let us know if these suggestions resolve your
issue, if not , feel
>free to post any further questions you have.
>Thanks for posting to MSDN Managed Newsgroup.
>Vikrant Dalwale
>Microsoft SQL Server Support Professional
>Microsoft highly recommends to all of our customers
that they visit the
>http://www.microsoft.com/protect site and perform the
three straightforward
>steps listed to improve your computer's security."
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>==========================================================>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
failed.
>>===============================================>>I can't find this in the error logs or the backup log
so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
to
>>use
>> the Maintenance Plan to backup all databses in our
SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Database
>> state cannot be changed while other users are using
the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>.
>|||Hello Jim,
You need to create a seperate Maintenance Plan for those DBs which need
both the Database and Transaction log backups and
a seperate one for master and msdb for only Database backups.
Does that answer your question ?
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no rights.
>Content-Class: urn:content-classes:message
>From: "Jim" <jim.abel@.lmco.com>
>Sender: "Jim" <jim.abel@.lmco.com>
>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
<#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
<10f501c3be74$0be078d0$a401280a@.phx.gbl>
<TChLAfovDHA.2520@.cpmsftngxa07.phx.gbl>
>Subject: Re: Errors during Maintenance Plan execution
>Date: Fri, 12 Dec 2003 08:27:51 -0800
>Lines: 171
>Message-ID: <05dc01c3c0cc$e343d240$a001280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcPAzONDhi3qx6uXTfm9O5c0Z9Bk1A==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:320465
>NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
>X-Tomcat-NG: microsoft.public.sqlserver.server
>All right guys are really helping me out. I looked up
>the proble and it has come down to the following The
>maintenace Plan says that it cannot do transaction log
>backups on the master and the msdb databases.
>My question now is how can I exclude those 2 DB's from
>the Transaction log backup portion of the maintenance
>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;288577
>>In case, you are doing Transaction log backups of one of
>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be Deleted
>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;303292
>>Please let us know if these suggestions resolve your
>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>that they visit the
>>http://www.microsoft.com/protect site and perform the
>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>to
>>use
>> the Maintenance Plan to backup all databses in our
>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>Database
>> state cannot be changed while other users are using
>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>|||Thank you Vikrant
That answers all of my questions.
I appreciate all of the help.
Happy Holidays
>--Original Message--
>Hello Jim,
>You need to create a seperate Maintenance Plan for
those DBs which need
>both the Database and Transaction log backups and
> a seperate one for master and msdb for only Database
backups.
>Does that answer your question ?
>Thanks for using MSDN Managed Newsgroup.
>Vikrant Dalwale
>Microsoft SQL Server Support Professional
>
>Microsoft highly recommends to all of our customers
that they visit the
>http://www.microsoft.com/protect site and perform the
three straightforward
>steps listed to improve your computer's security.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
><10f501c3be74$0be078d0$a401280a@.phx.gbl>
><TChLAfovDHA.2520@.cpmsftngxa07.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Fri, 12 Dec 2003 08:27:51 -0800
>>Lines: 171
>>Message-ID: <05dc01c3c0cc$e343d240$a001280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>Thread-Index: AcPAzONDhi3qx6uXTfm9O5c0Z9Bk1A==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
microsoft.public.sqlserver.server:320465
>>NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>All right guys are really helping me out. I looked up
>>the proble and it has come down to the following The
>>maintenace Plan says that it cannot do transaction log
>>backups on the master and the msdb databases.
>>My question now is how can I exclude those 2 DB's from
>>the Transaction log backup portion of the maintenance
>>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;288577
>>In case, you are doing Transaction log backups of one
of
>>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be
Deleted
>>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;303292
>>Please let us know if these suggestions resolve your
>>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>>that they visit the
>>http://www.microsoft.com/protect site and perform the
>>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties,
and
>>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE
V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair
minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>>which
>>will fail if you
>>have users in the database. Remove that option, if
you
>>do get a problem with
>>the database, you want to know about it and be there
>>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>>to
>>use
>> the Maintenance Plan to backup all databses in our
>>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>>Database
>> state cannot be changed while other users are
using
>>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Repair
>> statement not processed. Database needs to be in
>>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Repair
>> statement not processed. Database needs to be in
>>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter
of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>>
>.
>|||I have a similar problem so I verified that my database is
the only one in the maintenance plan (does not include
master or msdb). Yet my Transaction Log backup still
fails with the same message:
"The job failed. The Job was invoked by User U1ST074SORTS2
\rdenison. The last step to run was step 1 (Step 1)."
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
step failed."
Any other ideas? I'm really interested in resolving this
because my Xaction log gets huge, especially on a
distributor server.
Roger.
>--Original Message--
>All right guys are really helping me out. I looked up
>the proble and it has come down to the following The
>maintenace Plan says that it cannot do transaction log
>backups on the master and the msdb databases.
>My question now is how can I exclude those 2 DB's from
>the Transaction log backup portion of the maintenance
>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;288577
>>In case, you are doing Transaction log backups of one of
>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be Deleted
>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;303292
>>Please let us know if these suggestions resolve your
>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>that they visit the
>>http://www.microsoft.com/protect site and perform the
>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>to
>>use
>> the Maintenance Plan to backup all databses in our
>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>Database
>> state cannot be changed while other users are using
>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>.
>|||Why not create your own job to do the backups and not rely on the MP at all?
Then you will know exactly what it is trying to do and when.
--
Andrew J. Kelly
SQL Server MVP
"Roger Denison" <anonymous@.discussions.microsoft.com> wrote in message
news:215001c3cfee$1f72d010$3101280a@.phx.gbl...
> I have a similar problem so I verified that my database is
> the only one in the maintenance plan (does not include
> master or msdb). Yet my Transaction Log backup still
> fails with the same message:
> "The job failed. The Job was invoked by User U1ST074SORTS2
> \rdenison. The last step to run was step 1 (Step 1)."
> "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
> step failed."
> Any other ideas? I'm really interested in resolving this
> because my Xaction log gets huge, especially on a
> distributor server.
> Roger.
> >--Original Message--
> >All right guys are really helping me out. I looked up
> >the proble and it has come down to the following The
> >maintenace Plan says that it cannot do transaction log
> >backups on the master and the msdb databases.
> >
> >My question now is how can I exclude those 2 DB's from
> >the Transaction log backup portion of the maintenance
> >Plan?
> >
> >>--Original Message--
> >>
> >>
> >>Hello Jim,
> >>
> >>First of all, thanks to Tibor for pointing you in the
> >right direction.
> >>Just as an additional information,
> >>you can refer to the following article which explains
> >how to t-shoot
> >>General SQL Maint Wiz failures,
> >>
> >>INF: Troubleshooting Database Maintenance Plan Failures
> >>http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;288577
> >>
> >>In case, you are doing Transaction log backups of one of
> >the databases
> >>which is set to
> >>Simple Recovery Mode, you might want to refer to
> >following article
> >>explaining this scenario,
> >>
> >>BUG: Expired Transaction Log Backups May Not Be Deleted
> >by Maintenance Plan
> >>http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;303292
> >>
> >>Please let us know if these suggestions resolve your
> >issue, if not , feel
> >>free to post any further questions you have.
> >>
> >>Thanks for posting to MSDN Managed Newsgroup.
> >>
> >>Vikrant Dalwale
> >>
> >>Microsoft SQL Server Support Professional
> >>
> >>Microsoft highly recommends to all of our customers
> >that they visit the
> >>http://www.microsoft.com/protect site and perform the
> >three straightforward
> >>steps listed to improve your computer's security."
> >>This posting is provided "AS IS" with no warranties, and
> >confers no rights.
> >>
> >>
> >>
> >>--
> >>Content-Class: urn:content-classes:message
> >>From: "Jim" <jim.abel@.lmco.com>
> >>Sender: "Jim" <jim.abel@.lmco.com>
> >>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
> >><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
> >>Subject: Re: Errors during Maintenance Plan execution
> >>Date: Tue, 9 Dec 2003 08:46:52 -0800
> >>Lines: 71
> >>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="iso-8859-1"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
> >>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==> >>Newsgroups: microsoft.public.sqlserver.server
> >>Path: cpmsftngxa07.phx.gbl
> >>Xref: cpmsftngxa07.phx.gbl
> >microsoft.public.sqlserver.server:319894
> >>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
> >>X-Tomcat-NG: microsoft.public.sqlserver.server
> >>
> >>That helped. So now I have an error with the
> >transaction
> >>log backup portion of the Maintenace Plan.
> >>
> >>Here is the erro
> >>
> >>========================================================> >==> >>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
> >>failed. [SQLSTATE 42000] (Error 22029). The step
> >failed.
> >>===============================================> >>
> >>I can't find this in the error logs or the backup log
> >so
> >>I'm not sure what is happening
> >>--Original Message--
> >>You've checked the option to "Attempt to repair minor
> >>problems" for which
> >>SQL Server tries to set the db in single user mode
> >which
> >>will fail if you
> >>have users in the database. Remove that option, if you
> >>do get a problem with
> >>the database, you want to know about it and be there
> >to
> >>make a conscious
> >>decision of your actions at that stage.
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>Archive at:
> >>http://groups.google.com/groups?
> >>oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >>"Jim" <jim.abel@.lmco.com> wrote in message
> >>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> >> I am getting error on several database when trying
> >to
> >>use
> >> the Maintenance Plan to backup all databses in our
> >SQL
> >> Server 2000 with Service Pack 3.
> >> The following is the text generated from one of the
> >> database in the log file. The other databases
> >generate
> >> the same error message
> >>
> >> Log portion==============================> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
> >5070:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >Database
> >> state cannot be changed while other users are using
> >the
> >> database 'SQLCatalog'
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> >> DATABASE statement failed.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >> sp_dboption command failed.
> >> [18] Database SQLCatalog: Check Data and Index
> >>Linkage...
> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
> >7919:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in
> >single
> >> user mode.
> >>
> >> The following errors were found:
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in
> >single
> >> user mode.
> >> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >>
> >> ========================================> >>
> >> Any ideas on how to handle this? Is this a matter of
> >> running the maintenance plan at a different time?
> >>
> >>
> >>.
> >>
> >>
> >>
> >>.
> >>
> >.
> >

Tuesday, March 27, 2012

Estimated Query Plan

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Estimated Execution Plan Fails, XML Error?

Estimated execution plan

Estimated Execution Plan

Estimated Execution Plan

Estimated Execution Plan

Estimated cost shows 300% in query plan

Estimated cost shows 300% in query plan

Estimated cost shows 300% in query plan

Wednesday, March 21, 2012

Errors when rebuilding indexes

Errors when rebuilding indexes

Errors when rebuilding indexes

errors whan running backup plan

errors whan running backup plan

Monday, March 19, 2012

Errors of System DB Backup T-logJobs for Maint Plan

Friday, March 9, 2012

Errors during Maintenance Plan execution

ERROR: Subreport

Blog Archive

About Me