My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups
.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Friday, March 30, 2012
How do I measure the cost of compiling an execution plan?
My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>sql
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>sql
How do I measure the cost of compiling an execution plan?
My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
Thanks
SET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?
|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>
|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
Thanks
SET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?
|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>
|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>
Monday, March 26, 2012
How do I interpret results from a failed integrity test?
I have setup a database maintenance plan that consistently fails when
attempting to check the integrity of my database. I have tried running
the maintenance plan switching the include/exclude indexes options,
without any difference to the result. The logging report for the
failing integrity step of the maintenance plan includes the following:
========================================
===========================
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'myServer' as 'myServer\myUserName' (trusted)
Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:23 AM
[1] Database myDataBase: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424)
in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data
_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424)
in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data
_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:31 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
========================================
===========================
From this I assume that "Extent (1:357424) in database ID 7 is
allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
What does this mean and how do I resolve the problem?
Thanks,
StephenStephen,
Check the article at
serr_2_8ape.asp." target="_blank">http://msdn.microsoft.com/library/d...err_2_8ape.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0408251803.4bd6cfdd@.posting.google.com...
> I have setup a database maintenance plan that consistently fails when
> attempting to check the integrity of my database. I have tried running
> the maintenance plan switching the include/exclude indexes options,
> without any difference to the result. The logging report for the
> failing integrity step of the maintenance plan includes the following:
> ========================================
===========================
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'myServer' as 'myServer\myUserName' (trusted)
> Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:23 AM
> [1] Database myDataBase: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:35742
4) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_da
ta_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:35742
4) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_da
ta_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> ** Execution Time: 0 hrs, 0 mins, 8 secs **
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:31 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ========================================
===========================
>
> From this I assume that "Extent (1:357424) in database ID 7 is
> allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
> What does this mean and how do I resolve the problem?
> Thanks,
> Stephen|||Stephen,
If you enounter corruption related error messages the avenue most often
suggested is to restore from a known good backup. If for some reason this
is not available to you I would suggest contacting Microsoft SQL Server
Support to discuss your options and the possible implications of running
dbcc with the repair allow data loss option. Running this has some
significant risks and you may have other options.
http://support.microsoft.com/defaul...doffer41a&sd=GN
Thanks,
David Gerard
Microsoft SQL Server Supportsql
attempting to check the integrity of my database. I have tried running
the maintenance plan switching the include/exclude indexes options,
without any difference to the result. The logging report for the
failing integrity step of the maintenance plan includes the following:
========================================
===========================
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'myServer' as 'myServer\myUserName' (trusted)
Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:23 AM
[1] Database myDataBase: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424)
in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data
_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424)
in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data
_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:31 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
========================================
===========================
From this I assume that "Extent (1:357424) in database ID 7 is
allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
What does this mean and how do I resolve the problem?
Thanks,
StephenStephen,
Check the article at
serr_2_8ape.asp." target="_blank">http://msdn.microsoft.com/library/d...err_2_8ape.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0408251803.4bd6cfdd@.posting.google.com...
> I have setup a database maintenance plan that consistently fails when
> attempting to check the integrity of my database. I have tried running
> the maintenance plan switching the include/exclude indexes options,
> without any difference to the result. The logging report for the
> failing integrity step of the maintenance plan includes the following:
> ========================================
===========================
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'myServer' as 'myServer\myUserName' (trusted)
> Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:23 AM
> [1] Database myDataBase: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:35742
4) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_da
ta_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:35742
4) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_da
ta_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> ** Execution Time: 0 hrs, 0 mins, 8 secs **
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:31 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ========================================
===========================
>
> From this I assume that "Extent (1:357424) in database ID 7 is
> allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
> What does this mean and how do I resolve the problem?
> Thanks,
> Stephen|||Stephen,
If you enounter corruption related error messages the avenue most often
suggested is to restore from a known good backup. If for some reason this
is not available to you I would suggest contacting Microsoft SQL Server
Support to discuss your options and the possible implications of running
dbcc with the repair allow data loss option. Running this has some
significant risks and you may have other options.
http://support.microsoft.com/defaul...doffer41a&sd=GN
Thanks,
David Gerard
Microsoft SQL Server Supportsql
How do I interpret results from a failed integrity test?
I have setup a database maintenance plan that consistently fails when
attempting to check the integrity of my database. I have tried running
the maintenance plan switching the include/exclude indexes options,
without any difference to the result. The logging report for the
failing integrity step of the maintenance plan includes the following:
================================================== =================
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'myServer' as 'myServer\myUserName' (trusted)
Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:23 AM
[1] Database myDataBase: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:31 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
================================================== =================
From this I assume that "Extent (1:357424) in database ID 7 is
allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
What does this mean and how do I resolve the problem?
Thanks,
Stephen
Stephen,
Check the article at
http://msdn.microsoft.com/library/de...rr_2_8ape.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0408251803.4bd6cfdd@.posting.google.c om...
> I have setup a database maintenance plan that consistently fails when
> attempting to check the integrity of my database. I have tried running
> the maintenance plan switching the include/exclude indexes options,
> without any difference to the result. The logging report for the
> failing integrity step of the maintenance plan includes the following:
> ================================================== =================
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'myServer' as 'myServer\myUserName' (trusted)
> Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:23 AM
> [1] Database myDataBase: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> ** Execution Time: 0 hrs, 0 mins, 8 secs **
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:31 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ================================================== =================
>
> From this I assume that "Extent (1:357424) in database ID 7 is
> allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
> What does this mean and how do I resolve the problem?
> Thanks,
> Stephen
|||Stephen,
If you enounter corruption related error messages the avenue most often
suggested is to restore from a known good backup. If for some reason this
is not available to you I would suggest contacting Microsoft SQL Server
Support to discuss your options and the possible implications of running
dbcc with the repair allow data loss option. Running this has some
significant risks and you may have other options.
http://support.microsoft.com/default...offer41a&sd=GN
Thanks,
David Gerard
Microsoft SQL Server Support
attempting to check the integrity of my database. I have tried running
the maintenance plan switching the include/exclude indexes options,
without any difference to the result. The logging report for the
failing integrity step of the maintenance plan includes the following:
================================================== =================
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'myServer' as 'myServer\myUserName' (trusted)
Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:23 AM
[1] Database myDataBase: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:31 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
================================================== =================
From this I assume that "Extent (1:357424) in database ID 7 is
allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
What does this mean and how do I resolve the problem?
Thanks,
Stephen
Stephen,
Check the article at
http://msdn.microsoft.com/library/de...rr_2_8ape.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0408251803.4bd6cfdd@.posting.google.c om...
> I have setup a database maintenance plan that consistently fails when
> attempting to check the integrity of my database. I have tried running
> the maintenance plan switching the include/exclude indexes options,
> without any difference to the result. The logging report for the
> failing integrity step of the maintenance plan includes the following:
> ================================================== =================
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'myServer' as 'myServer\myUserName' (trusted)
> Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:23 AM
> [1] Database myDataBase: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> ** Execution Time: 0 hrs, 0 mins, 8 secs **
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:31 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ================================================== =================
>
> From this I assume that "Extent (1:357424) in database ID 7 is
> allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
> What does this mean and how do I resolve the problem?
> Thanks,
> Stephen
|||Stephen,
If you enounter corruption related error messages the avenue most often
suggested is to restore from a known good backup. If for some reason this
is not available to you I would suggest contacting Microsoft SQL Server
Support to discuss your options and the possible implications of running
dbcc with the repair allow data loss option. Running this has some
significant risks and you may have other options.
http://support.microsoft.com/default...offer41a&sd=GN
Thanks,
David Gerard
Microsoft SQL Server Support
How do I interpret results from a failed integrity test?
I have setup a database maintenance plan that consistently fails when
attempting to check the integrity of my database. I have tried running
the maintenance plan switching the include/exclude indexes options,
without any difference to the result. The logging report for the
failing integrity step of the maintenance plan includes the following:
=================================================================== Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'myServer' as 'myServer\myUserName' (trusted)
Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:23 AM
[1] Database myDataBase: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:31 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
===================================================================
From this I assume that "Extent (1:357424) in database ID 7 is
allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
What does this mean and how do I resolve the problem?
Thanks,
StephenStephen,
Check the article at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_8ape.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0408251803.4bd6cfdd@.posting.google.com...
> I have setup a database maintenance plan that consistently fails when
> attempting to check the integrity of my database. I have tried running
> the maintenance plan switching the include/exclude indexes options,
> without any difference to the result. The logging report for the
> failing integrity step of the maintenance plan includes the following:
> ===================================================================> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'myServer' as 'myServer\myUserName' (trusted)
> Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:23 AM
> [1] Database myDataBase: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> ** Execution Time: 0 hrs, 0 mins, 8 secs **
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:31 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ===================================================================>
> From this I assume that "Extent (1:357424) in database ID 7 is
> allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
> What does this mean and how do I resolve the problem?
> Thanks,
> Stephen|||Stephen,
If you enounter corruption related error messages the avenue most often
suggested is to restore from a known good backup. If for some reason this
is not available to you I would suggest contacting Microsoft SQL Server
Support to discuss your options and the possible implications of running
dbcc with the repair allow data loss option. Running this has some
significant risks and you may have other options.
http://support.microsoft.com/default.aspx?scid=fh;en-us;Prodoffer41a&sd=GN
Thanks,
David Gerard
Microsoft SQL Server Support
attempting to check the integrity of my database. I have tried running
the maintenance plan switching the include/exclude indexes options,
without any difference to the result. The logging report for the
failing integrity step of the maintenance plan includes the following:
=================================================================== Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'myServer' as 'myServer\myUserName' (trusted)
Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:23 AM
[1] Database myDataBase: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:31 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
===================================================================
From this I assume that "Extent (1:357424) in database ID 7 is
allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
What does this mean and how do I resolve the problem?
Thanks,
StephenStephen,
Check the article at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_8ape.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0408251803.4bd6cfdd@.posting.google.com...
> I have setup a database maintenance plan that consistently fails when
> attempting to check the integrity of my database. I have tried running
> the maintenance plan switching the include/exclude indexes options,
> without any difference to the result. The logging report for the
> failing integrity step of the maintenance plan includes the following:
> ===================================================================> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'myServer' as 'myServer\myUserName' (trusted)
> Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:23 AM
> [1] Database myDataBase: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> ** Execution Time: 0 hrs, 0 mins, 8 secs **
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:31 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ===================================================================>
> From this I assume that "Extent (1:357424) in database ID 7 is
> allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
> What does this mean and how do I resolve the problem?
> Thanks,
> Stephen|||Stephen,
If you enounter corruption related error messages the avenue most often
suggested is to restore from a known good backup. If for some reason this
is not available to you I would suggest contacting Microsoft SQL Server
Support to discuss your options and the possible implications of running
dbcc with the repair allow data loss option. Running this has some
significant risks and you may have other options.
http://support.microsoft.com/default.aspx?scid=fh;en-us;Prodoffer41a&sd=GN
Thanks,
David Gerard
Microsoft SQL Server Support
Subscribe to:
Posts (Atom)