Hello,
My understanding is that modifying data in system tables in SQL Server 2005
isn't allowed. Currently, I have an issue where a full dbcc checkdb is
failing due to records existing in sys.columns, but not in sys.objects.
I believe these orphaned rows are a result of a delete statement being ran
against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
upgrade. This delete removed all objects from sysobjects which were owned by
a specific user id as the user was to be removed from the system. This
delete apparently left records behind for 2 of these user tables in
syscolumns.
I have gone through the results of the dbcc checkdb and every inconsistency
is in reference to these 2 object IDs existing in sys.columns, but not
sys.objects. In SQL Server 2000 I would just remove these records from
syscolumns and I believe I'd be good. Since this is causing the full dbcc
checkdb to fail, I'd like to see what options are available for removing
these records. Any help would be greatly appreciated.
Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
dbcc checkdb with physical_only comes back clean. I assume this is due to a
physical_only not performing a catalog check. Would this inconsistency cause
a database restore to fail?
Thanks,
Greg
Check BOL for the following options for DBCC CHECKDB:
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Also, have you actually TRIED to delete the offending rows yourself?
Although MS says not supported . . . you never know! Attempt on a TEST
server first! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> Hello,
> My understanding is that modifying data in system tables in SQL Server
> 2005
> isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> failing due to records existing in sys.columns, but not in sys.objects.
> I believe these orphaned rows are a result of a delete statement being ran
> against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> upgrade. This delete removed all objects from sysobjects which were owned
> by
> a specific user id as the user was to be removed from the system. This
> delete apparently left records behind for 2 of these user tables in
> syscolumns.
> I have gone through the results of the dbcc checkdb and every
> inconsistency
> is in reference to these 2 object IDs existing in sys.columns, but not
> sys.objects. In SQL Server 2000 I would just remove these records from
> syscolumns and I believe I'd be good. Since this is causing the full dbcc
> checkdb to fail, I'd like to see what options are available for removing
> these records. Any help would be greatly appreciated.
> Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> dbcc checkdb with physical_only comes back clean. I assume this is due to
> a
> physical_only not performing a catalog check. Would this inconsistency
> cause
> a database restore to fail?
> Thanks,
> Greg
|||Thanks for the response. I have tried setting 'allow updates' to 1 and
deleting these rows from sys.columns, but it errors out with "Ad hoc updates
to system catalogs are not allowed. I'd prefer not to try any of the checkdb
repair options as I'm not sure whether this removes just the specific rows
that I'd like to delete or whole pages with other data that I don't want to
remove.
If it's not possible to delete these rows, it looks like the only option to
correct the inconsistency within these system tables is to export all of the
user objects/data into a new database. I can understand the reasoning behind
Microsoft taking away the ability to mess around with system tables. But,
considering that this database is just over 900GB, exporting seems to be an
exceedingly painful solution compared to just deleting all rows from a table
where object_id = object_id. I guess the other option is to just live with
the dbcc checkdb errors every week and keep track of the output to make sure
that there aren't any additional errors. Hopefully there's another way.
Thanks,
Greg
"TheSQLGuru" wrote:
> Check BOL for the following options for DBCC CHECKDB:
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Also, have you actually TRIED to delete the offending rows yourself?
> Although MS says not supported . . . you never know! Attempt on a TEST
> server first! :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
>
>
|||On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> If it's not possible to delete these rows, it looks like the only option to
> correct the inconsistency within these system tables is to export all of the
> user objects/data into a new database. I can understand the reasoning behind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be an
> exceedingly painful solution compared to just deleting all rows from a table
> where object_id = object_id. I guess the other option is to just live with
> the dbcc checkdb errors every week and keep track of the output to make sure
> that there aren't any additional errors. Hopefully there's another way.
>
Exporting the data would indeed be a painful solution but maybe it is
the only way, I really don't know. Obviously this demonstrates why it
was an incredibly bad idea to do what you did in the first place. Why
would you ever resort to deleting data directly from sysobjects? Doing
that with critical data (especially with 900GB of critical data!)
seems like a pretty irresponsible action on the part of someone.
I mention this in case you are still looking for the right solution
for whatever it is you were trying to achieve - as well as to satisfy
my own surprise and curiosity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks for the feedback Tibor. I think you're probably right.
"Tibor Karaszi" wrote:
> Allow updates is indeed a dummy config in 2005. It is only there for the command not to error, it
> will not allow updates of the system tables. I would suggest using a backup on which you test DBCC
> with repair option. I'm not sure whether DBCC repair will attempt repair system table
> inconsistencies. I understand it can be a hassle considering the database size. You could open a
> case with MS Support, but to be honest, I wouldn't expect them to have much more to suggest.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
>
|||Well, thanks for the comments David, although not very helpful. I wasn't
actually the person who originally deleted these records, but I am left to
figure this out after the checkdb came back with errors.
From what I understand, this was done to try and drop 20,000+ objects that
were owned by a particular user id as this user was to be dropped.
"David Portas" wrote:
> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> Exporting the data would indeed be a painful solution but maybe it is
> the only way, I really don't know. Obviously this demonstrates why it
> was an incredibly bad idea to do what you did in the first place. Why
> would you ever resort to deleting data directly from sysobjects? Doing
> that with critical data (especially with 900GB of critical data!)
> seems like a pretty irresponsible action on the part of someone.
> I mention this in case you are still looking for the right solution
> for whatever it is you were trying to achieve - as well as to satisfy
> my own surprise and curiosity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||On 24 Mar, 14:38, gj111 <g...@.discussions.microsoft.com> wrote:
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
>
Then it was perhaps due to ignorance and inexperience rather than pure
recklessness. Surely the quick, easy and safe way would have been to
script some DROP statements from the catalogue to do the same job. Or
to use sp_changeobjectowner.
As you are the person who has to do the mopping up, you might wonder
what other horrors you are going to discover. Good luck!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I would recommend taking the original suggestion of contacting Microsoft
support. They do have ways of modifying the system tables but unless you
really know what you're doing, modifying deleting these rows may make the
problem worse. If nothing else, there are a lot of disk pages allocated to
those 20,000 tables that are no longer addressable and will need to be
cleaned up.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
|||I discussed this with Greg offline and the solution I offered (which is
unfortunately undocumented) seems to have done the trick without any
size-of-data operations. It only worked because the corruption is totally
benign in this case - so its not something that I can publicize (although
you're welcome to email me through the blog link below)
Best way to cope with this once its happened is to call Product Support and
have them help you (or email me if you don't need an instantaneous
response). Best way to avoid it is to educate your DBAs not to mess with the
system tables...
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
|||Thanks Roger. I'll definitely get with support. I'm also in Orlando this
week, maybe I'll get lucky and get a chance to pick someone's brain.
Thanks,
Greg
"Roger Wolter[MSFT]" wrote:
> I would recommend taking the original suggestion of contacting Microsoft
> support. They do have ways of modifying the system tables but unless you
> really know what you're doing, modifying deleting these rows may make the
> problem worse. If nothing else, there are a lot of disk pages allocated to
> those 20,000 tables that are no longer addressable and will need to be
> cleaned up.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
>
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Thursday, March 29, 2012
Delete data from sys.columns in SQL Server 2005
Hello,
My understanding is that modifying data in system tables in SQL Server 2005
isn't allowed. Currently, I have an issue where a full dbcc checkdb is
failing due to records existing in sys.columns, but not in sys.objects.
I believe these orphaned rows are a result of a delete statement being ran
against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
upgrade. This delete removed all objects from sysobjects which were owned by
a specific user id as the user was to be removed from the system. This
delete apparently left records behind for 2 of these user tables in
syscolumns.
I have gone through the results of the dbcc checkdb and every inconsistency
is in reference to these 2 object IDs existing in sys.columns, but not
sys.objects. In SQL Server 2000 I would just remove these records from
syscolumns and I believe I'd be good. Since this is causing the full dbcc
checkdb to fail, I'd like to see what options are available for removing
these records. Any help would be greatly appreciated.
Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
dbcc checkdb with physical_only comes back clean. I assume this is due to a
physical_only not performing a catalog check. Would this inconsistency cause
a database restore to fail?
Thanks,
GregCheck BOL for the following options for DBCC CHECKDB:
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Also, have you actually TRIED to delete the offending rows yourself?
Although MS says not supported . . . you never know! Attempt on a TEST
server first! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> Hello,
> My understanding is that modifying data in system tables in SQL Server
> 2005
> isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> failing due to records existing in sys.columns, but not in sys.objects.
> I believe these orphaned rows are a result of a delete statement being ran
> against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> upgrade. This delete removed all objects from sysobjects which were owned
> by
> a specific user id as the user was to be removed from the system. This
> delete apparently left records behind for 2 of these user tables in
> syscolumns.
> I have gone through the results of the dbcc checkdb and every
> inconsistency
> is in reference to these 2 object IDs existing in sys.columns, but not
> sys.objects. In SQL Server 2000 I would just remove these records from
> syscolumns and I believe I'd be good. Since this is causing the full dbcc
> checkdb to fail, I'd like to see what options are available for removing
> these records. Any help would be greatly appreciated.
> Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> dbcc checkdb with physical_only comes back clean. I assume this is due to
> a
> physical_only not performing a catalog check. Would this inconsistency
> cause
> a database restore to fail?
> Thanks,
> Greg|||Thanks for the response. I have tried setting 'allow updates' to 1 and
deleting these rows from sys.columns, but it errors out with "Ad hoc updates
to system catalogs are not allowed. I'd prefer not to try any of the checkdb
repair options as I'm not sure whether this removes just the specific rows
that I'd like to delete or whole pages with other data that I don't want to
remove.
If it's not possible to delete these rows, it looks like the only option to
correct the inconsistency within these system tables is to export all of the
user objects/data into a new database. I can understand the reasoning behind
Microsoft taking away the ability to mess around with system tables. But,
considering that this database is just over 900GB, exporting seems to be an
exceedingly painful solution compared to just deleting all rows from a table
where object_id = object_id. I guess the other option is to just live with
the dbcc checkdb errors every week and keep track of the output to make sure
that there aren't any additional errors. Hopefully there's another way.
Thanks,
Greg
"TheSQLGuru" wrote:
> Check BOL for the following options for DBCC CHECKDB:
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Also, have you actually TRIED to delete the offending rows yourself?
> Although MS says not supported . . . you never know! Attempt on a TEST
> server first! :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> > Hello,
> >
> > My understanding is that modifying data in system tables in SQL Server
> > 2005
> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> > failing due to records existing in sys.columns, but not in sys.objects.
> >
> > I believe these orphaned rows are a result of a delete statement being ran
> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> > upgrade. This delete removed all objects from sysobjects which were owned
> > by
> > a specific user id as the user was to be removed from the system. This
> > delete apparently left records behind for 2 of these user tables in
> > syscolumns.
> >
> > I have gone through the results of the dbcc checkdb and every
> > inconsistency
> > is in reference to these 2 object IDs existing in sys.columns, but not
> > sys.objects. In SQL Server 2000 I would just remove these records from
> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
> > checkdb to fail, I'd like to see what options are available for removing
> > these records. Any help would be greatly appreciated.
> >
> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> > dbcc checkdb with physical_only comes back clean. I assume this is due to
> > a
> > physical_only not performing a catalog check. Would this inconsistency
> > cause
> > a database restore to fail?
> >
> > Thanks,
> > Greg
>
>|||Allow updates is indeed a dummy config in 2005. It is only there for the command not to error, it
will not allow updates of the system tables. I would suggest using a backup on which you test DBCC
with repair option. I'm not sure whether DBCC repair will attempt repair system table
inconsistencies. I understand it can be a hassle considering the database size. You could open a
case with MS Support, but to be honest, I wouldn't expect them to have much more to suggest.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
> Thanks for the response. I have tried setting 'allow updates' to 1 and
> deleting these rows from sys.columns, but it errors out with "Ad hoc updates
> to system catalogs are not allowed. I'd prefer not to try any of the checkdb
> repair options as I'm not sure whether this removes just the specific rows
> that I'd like to delete or whole pages with other data that I don't want to
> remove.
> If it's not possible to delete these rows, it looks like the only option to
> correct the inconsistency within these system tables is to export all of the
> user objects/data into a new database. I can understand the reasoning behind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be an
> exceedingly painful solution compared to just deleting all rows from a table
> where object_id = object_id. I guess the other option is to just live with
> the dbcc checkdb errors every week and keep track of the output to make sure
> that there aren't any additional errors. Hopefully there's another way.
> Thanks,
> Greg
> "TheSQLGuru" wrote:
>> Check BOL for the following options for DBCC CHECKDB:
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Also, have you actually TRIED to delete the offending rows yourself?
>> Although MS says not supported . . . you never know! Attempt on a TEST
>> server first! :-)
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "gj111" <gj111@.discussions.microsoft.com> wrote in message
>> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
>> > Hello,
>> >
>> > My understanding is that modifying data in system tables in SQL Server
>> > 2005
>> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
>> > failing due to records existing in sys.columns, but not in sys.objects.
>> >
>> > I believe these orphaned rows are a result of a delete statement being ran
>> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
>> > upgrade. This delete removed all objects from sysobjects which were owned
>> > by
>> > a specific user id as the user was to be removed from the system. This
>> > delete apparently left records behind for 2 of these user tables in
>> > syscolumns.
>> >
>> > I have gone through the results of the dbcc checkdb and every
>> > inconsistency
>> > is in reference to these 2 object IDs existing in sys.columns, but not
>> > sys.objects. In SQL Server 2000 I would just remove these records from
>> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
>> > checkdb to fail, I'd like to see what options are available for removing
>> > these records. Any help would be greatly appreciated.
>> >
>> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
>> > dbcc checkdb with physical_only comes back clean. I assume this is due to
>> > a
>> > physical_only not performing a catalog check. Would this inconsistency
>> > cause
>> > a database restore to fail?
>> >
>> > Thanks,
>> > Greg
>>|||On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> If it's not possible to delete these rows, it looks like the only option to
> correct the inconsistency within these system tables is to export all of the
> user objects/data into a new database. I can understand the reasoning behind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be an
> exceedingly painful solution compared to just deleting all rows from a table
> where object_id = object_id. I guess the other option is to just live with
> the dbcc checkdb errors every week and keep track of the output to make sure
> that there aren't any additional errors. Hopefully there's another way.
>
Exporting the data would indeed be a painful solution but maybe it is
the only way, I really don't know. Obviously this demonstrates why it
was an incredibly bad idea to do what you did in the first place. Why
would you ever resort to deleting data directly from sysobjects? Doing
that with critical data (especially with 900GB of critical data!)
seems like a pretty irresponsible action on the part of someone.
I mention this in case you are still looking for the right solution
for whatever it is you were trying to achieve - as well as to satisfy
my own surprise and curiosity.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the feedback Tibor. I think you're probably right.
"Tibor Karaszi" wrote:
> Allow updates is indeed a dummy config in 2005. It is only there for the command not to error, it
> will not allow updates of the system tables. I would suggest using a backup on which you test DBCC
> with repair option. I'm not sure whether DBCC repair will attempt repair system table
> inconsistencies. I understand it can be a hassle considering the database size. You could open a
> case with MS Support, but to be honest, I wouldn't expect them to have much more to suggest.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
> > Thanks for the response. I have tried setting 'allow updates' to 1 and
> > deleting these rows from sys.columns, but it errors out with "Ad hoc updates
> > to system catalogs are not allowed. I'd prefer not to try any of the checkdb
> > repair options as I'm not sure whether this removes just the specific rows
> > that I'd like to delete or whole pages with other data that I don't want to
> > remove.
> >
> > If it's not possible to delete these rows, it looks like the only option to
> > correct the inconsistency within these system tables is to export all of the
> > user objects/data into a new database. I can understand the reasoning behind
> > Microsoft taking away the ability to mess around with system tables. But,
> > considering that this database is just over 900GB, exporting seems to be an
> > exceedingly painful solution compared to just deleting all rows from a table
> > where object_id = object_id. I guess the other option is to just live with
> > the dbcc checkdb errors every week and keep track of the output to make sure
> > that there aren't any additional errors. Hopefully there's another way.
> >
> > Thanks,
> > Greg
> >
> > "TheSQLGuru" wrote:
> >
> >> Check BOL for the following options for DBCC CHECKDB:
> >> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> >>
> >> Also, have you actually TRIED to delete the offending rows yourself?
> >> Although MS says not supported . . . you never know! Attempt on a TEST
> >> server first! :-)
> >>
> >> --
> >> TheSQLGuru
> >> President
> >> Indicium Resources, Inc.
> >>
> >> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> >> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> >> > Hello,
> >> >
> >> > My understanding is that modifying data in system tables in SQL Server
> >> > 2005
> >> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> >> > failing due to records existing in sys.columns, but not in sys.objects.
> >> >
> >> > I believe these orphaned rows are a result of a delete statement being ran
> >> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> >> > upgrade. This delete removed all objects from sysobjects which were owned
> >> > by
> >> > a specific user id as the user was to be removed from the system. This
> >> > delete apparently left records behind for 2 of these user tables in
> >> > syscolumns.
> >> >
> >> > I have gone through the results of the dbcc checkdb and every
> >> > inconsistency
> >> > is in reference to these 2 object IDs existing in sys.columns, but not
> >> > sys.objects. In SQL Server 2000 I would just remove these records from
> >> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
> >> > checkdb to fail, I'd like to see what options are available for removing
> >> > these records. Any help would be greatly appreciated.
> >> >
> >> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> >> > dbcc checkdb with physical_only comes back clean. I assume this is due to
> >> > a
> >> > physical_only not performing a catalog check. Would this inconsistency
> >> > cause
> >> > a database restore to fail?
> >> >
> >> > Thanks,
> >> > Greg
> >>
> >>
> >>
>|||Well, thanks for the comments David, although not very helpful. I wasn't
actually the person who originally deleted these records, but I am left to
figure this out after the checkdb came back with errors.
From what I understand, this was done to try and drop 20,000+ objects that
were owned by a particular user id as this user was to be dropped.
"David Portas" wrote:
> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> >
> > If it's not possible to delete these rows, it looks like the only option to
> > correct the inconsistency within these system tables is to export all of the
> > user objects/data into a new database. I can understand the reasoning behind
> > Microsoft taking away the ability to mess around with system tables. But,
> > considering that this database is just over 900GB, exporting seems to be an
> > exceedingly painful solution compared to just deleting all rows from a table
> > where object_id = object_id. I guess the other option is to just live with
> > the dbcc checkdb errors every week and keep track of the output to make sure
> > that there aren't any additional errors. Hopefully there's another way.
> >
> Exporting the data would indeed be a painful solution but maybe it is
> the only way, I really don't know. Obviously this demonstrates why it
> was an incredibly bad idea to do what you did in the first place. Why
> would you ever resort to deleting data directly from sysobjects? Doing
> that with critical data (especially with 900GB of critical data!)
> seems like a pretty irresponsible action on the part of someone.
> I mention this in case you are still looking for the right solution
> for whatever it is you were trying to achieve - as well as to satisfy
> my own surprise and curiosity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||On 24 Mar, 14:38, gj111 <g...@.discussions.microsoft.com> wrote:
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
>
Then it was perhaps due to ignorance and inexperience rather than pure
recklessness. Surely the quick, easy and safe way would have been to
script some DROP statements from the catalogue to do the same job. Or
to use sp_changeobjectowner.
As you are the person who has to do the mopping up, you might wonder
what other horrors you are going to discover. Good luck!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
You might already be familiar with this technique, but for the benefit of other readers:
These type of operations are pretty straight forward to automate by generating the SQL statements
and copy the results into a query window and execute it:
SELECT 'DROP TABLE dbo.[' + name + ']'
FROM sysobjects
WHERE uid = USER_ID('dbo')
AND type = 'U'
Above is for 2000. Similar can be done for 2005 using schema and catalog views.
Or, one can use a cursor and built the DROP command in a variable which you then execute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only option to
>> > correct the inconsistency within these system tables is to export all of the
>> > user objects/data into a new database. I can understand the reasoning behind
>> > Microsoft taking away the ability to mess around with system tables. But,
>> > considering that this database is just over 900GB, exporting seems to be an
>> > exceedingly painful solution compared to just deleting all rows from a table
>> > where object_id = object_id. I guess the other option is to just live with
>> > the dbcc checkdb errors every week and keep track of the output to make sure
>> > that there aren't any additional errors. Hopefully there's another way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||I would recommend taking the original suggestion of contacting Microsoft
support. They do have ways of modifying the system tables but unless you
really know what you're doing, modifying deleting these rows may make the
problem worse. If nothing else, there are a lot of disk pages allocated to
those 20,000 tables that are no longer addressable and will need to be
cleaned up.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only
>> > option to
>> > correct the inconsistency within these system tables is to export all
>> > of the
>> > user objects/data into a new database. I can understand the reasoning
>> > behind
>> > Microsoft taking away the ability to mess around with system tables.
>> > But,
>> > considering that this database is just over 900GB, exporting seems to
>> > be an
>> > exceedingly painful solution compared to just deleting all rows from a
>> > table
>> > where object_id = object_id. I guess the other option is to just live
>> > with
>> > the dbcc checkdb errors every week and keep track of the output to make
>> > sure
>> > that there aren't any additional errors. Hopefully there's another
>> > way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||I discussed this with Greg offline and the solution I offered (which is
unfortunately undocumented) seems to have done the trick without any
size-of-data operations. It only worked because the corruption is totally
benign in this case - so its not something that I can publicize (although
you're welcome to email me through the blog link below)
Best way to cope with this once its happened is to call Product Support and
have them help you (or email me if you don't need an instantaneous
response). Best way to avoid it is to educate your DBAs not to mess with the
system tables...
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only
>> > option to
>> > correct the inconsistency within these system tables is to export all
>> > of the
>> > user objects/data into a new database. I can understand the reasoning
>> > behind
>> > Microsoft taking away the ability to mess around with system tables.
>> > But,
>> > considering that this database is just over 900GB, exporting seems to
>> > be an
>> > exceedingly painful solution compared to just deleting all rows from a
>> > table
>> > where object_id = object_id. I guess the other option is to just live
>> > with
>> > the dbcc checkdb errors every week and keep track of the output to make
>> > sure
>> > that there aren't any additional errors. Hopefully there's another
>> > way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||Thanks Roger. I'll definitely get with support. I'm also in Orlando this
week, maybe I'll get lucky and get a chance to pick someone's brain.
Thanks,
Greg
"Roger Wolter[MSFT]" wrote:
> I would recommend taking the original suggestion of contacting Microsoft
> support. They do have ways of modifying the system tables but unless you
> really know what you're doing, modifying deleting these rows may make the
> problem worse. If nothing else, there are a lot of disk pages allocated to
> those 20,000 tables that are no longer addressable and will need to be
> cleaned up.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> > Well, thanks for the comments David, although not very helpful. I wasn't
> > actually the person who originally deleted these records, but I am left to
> > figure this out after the checkdb came back with errors.
> >
> > From what I understand, this was done to try and drop 20,000+ objects that
> > were owned by a particular user id as this user was to be dropped.
> >
> > "David Portas" wrote:
> >
> >> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> >> >
> >> > If it's not possible to delete these rows, it looks like the only
> >> > option to
> >> > correct the inconsistency within these system tables is to export all
> >> > of the
> >> > user objects/data into a new database. I can understand the reasoning
> >> > behind
> >> > Microsoft taking away the ability to mess around with system tables.
> >> > But,
> >> > considering that this database is just over 900GB, exporting seems to
> >> > be an
> >> > exceedingly painful solution compared to just deleting all rows from a
> >> > table
> >> > where object_id = object_id. I guess the other option is to just live
> >> > with
> >> > the dbcc checkdb errors every week and keep track of the output to make
> >> > sure
> >> > that there aren't any additional errors. Hopefully there's another
> >> > way.
> >> >
> >>
> >> Exporting the data would indeed be a painful solution but maybe it is
> >> the only way, I really don't know. Obviously this demonstrates why it
> >> was an incredibly bad idea to do what you did in the first place. Why
> >> would you ever resort to deleting data directly from sysobjects? Doing
> >> that with critical data (especially with 900GB of critical data!)
> >> seems like a pretty irresponsible action on the part of someone.
> >>
> >> I mention this in case you are still looking for the right solution
> >> for whatever it is you were trying to achieve - as well as to satisfy
> >> my own surprise and curiosity.
> >>
> >> --
> >> David Portas, SQL Server MVP
> >>
> >> Whenever possible please post enough code to reproduce your problem.
> >> Including CREATE TABLE and INSERT statements usually helps.
> >> State what version of SQL Server you are using and specify the content
> >> of any error messages.
> >>
> >> SQL Server Books Online:
> >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> >> --
> >>
> >>
>sql
My understanding is that modifying data in system tables in SQL Server 2005
isn't allowed. Currently, I have an issue where a full dbcc checkdb is
failing due to records existing in sys.columns, but not in sys.objects.
I believe these orphaned rows are a result of a delete statement being ran
against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
upgrade. This delete removed all objects from sysobjects which were owned by
a specific user id as the user was to be removed from the system. This
delete apparently left records behind for 2 of these user tables in
syscolumns.
I have gone through the results of the dbcc checkdb and every inconsistency
is in reference to these 2 object IDs existing in sys.columns, but not
sys.objects. In SQL Server 2000 I would just remove these records from
syscolumns and I believe I'd be good. Since this is causing the full dbcc
checkdb to fail, I'd like to see what options are available for removing
these records. Any help would be greatly appreciated.
Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
dbcc checkdb with physical_only comes back clean. I assume this is due to a
physical_only not performing a catalog check. Would this inconsistency cause
a database restore to fail?
Thanks,
GregCheck BOL for the following options for DBCC CHECKDB:
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Also, have you actually TRIED to delete the offending rows yourself?
Although MS says not supported . . . you never know! Attempt on a TEST
server first! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> Hello,
> My understanding is that modifying data in system tables in SQL Server
> 2005
> isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> failing due to records existing in sys.columns, but not in sys.objects.
> I believe these orphaned rows are a result of a delete statement being ran
> against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> upgrade. This delete removed all objects from sysobjects which were owned
> by
> a specific user id as the user was to be removed from the system. This
> delete apparently left records behind for 2 of these user tables in
> syscolumns.
> I have gone through the results of the dbcc checkdb and every
> inconsistency
> is in reference to these 2 object IDs existing in sys.columns, but not
> sys.objects. In SQL Server 2000 I would just remove these records from
> syscolumns and I believe I'd be good. Since this is causing the full dbcc
> checkdb to fail, I'd like to see what options are available for removing
> these records. Any help would be greatly appreciated.
> Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> dbcc checkdb with physical_only comes back clean. I assume this is due to
> a
> physical_only not performing a catalog check. Would this inconsistency
> cause
> a database restore to fail?
> Thanks,
> Greg|||Thanks for the response. I have tried setting 'allow updates' to 1 and
deleting these rows from sys.columns, but it errors out with "Ad hoc updates
to system catalogs are not allowed. I'd prefer not to try any of the checkdb
repair options as I'm not sure whether this removes just the specific rows
that I'd like to delete or whole pages with other data that I don't want to
remove.
If it's not possible to delete these rows, it looks like the only option to
correct the inconsistency within these system tables is to export all of the
user objects/data into a new database. I can understand the reasoning behind
Microsoft taking away the ability to mess around with system tables. But,
considering that this database is just over 900GB, exporting seems to be an
exceedingly painful solution compared to just deleting all rows from a table
where object_id = object_id. I guess the other option is to just live with
the dbcc checkdb errors every week and keep track of the output to make sure
that there aren't any additional errors. Hopefully there's another way.
Thanks,
Greg
"TheSQLGuru" wrote:
> Check BOL for the following options for DBCC CHECKDB:
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Also, have you actually TRIED to delete the offending rows yourself?
> Although MS says not supported . . . you never know! Attempt on a TEST
> server first! :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> > Hello,
> >
> > My understanding is that modifying data in system tables in SQL Server
> > 2005
> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> > failing due to records existing in sys.columns, but not in sys.objects.
> >
> > I believe these orphaned rows are a result of a delete statement being ran
> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> > upgrade. This delete removed all objects from sysobjects which were owned
> > by
> > a specific user id as the user was to be removed from the system. This
> > delete apparently left records behind for 2 of these user tables in
> > syscolumns.
> >
> > I have gone through the results of the dbcc checkdb and every
> > inconsistency
> > is in reference to these 2 object IDs existing in sys.columns, but not
> > sys.objects. In SQL Server 2000 I would just remove these records from
> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
> > checkdb to fail, I'd like to see what options are available for removing
> > these records. Any help would be greatly appreciated.
> >
> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> > dbcc checkdb with physical_only comes back clean. I assume this is due to
> > a
> > physical_only not performing a catalog check. Would this inconsistency
> > cause
> > a database restore to fail?
> >
> > Thanks,
> > Greg
>
>|||Allow updates is indeed a dummy config in 2005. It is only there for the command not to error, it
will not allow updates of the system tables. I would suggest using a backup on which you test DBCC
with repair option. I'm not sure whether DBCC repair will attempt repair system table
inconsistencies. I understand it can be a hassle considering the database size. You could open a
case with MS Support, but to be honest, I wouldn't expect them to have much more to suggest.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
> Thanks for the response. I have tried setting 'allow updates' to 1 and
> deleting these rows from sys.columns, but it errors out with "Ad hoc updates
> to system catalogs are not allowed. I'd prefer not to try any of the checkdb
> repair options as I'm not sure whether this removes just the specific rows
> that I'd like to delete or whole pages with other data that I don't want to
> remove.
> If it's not possible to delete these rows, it looks like the only option to
> correct the inconsistency within these system tables is to export all of the
> user objects/data into a new database. I can understand the reasoning behind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be an
> exceedingly painful solution compared to just deleting all rows from a table
> where object_id = object_id. I guess the other option is to just live with
> the dbcc checkdb errors every week and keep track of the output to make sure
> that there aren't any additional errors. Hopefully there's another way.
> Thanks,
> Greg
> "TheSQLGuru" wrote:
>> Check BOL for the following options for DBCC CHECKDB:
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Also, have you actually TRIED to delete the offending rows yourself?
>> Although MS says not supported . . . you never know! Attempt on a TEST
>> server first! :-)
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "gj111" <gj111@.discussions.microsoft.com> wrote in message
>> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
>> > Hello,
>> >
>> > My understanding is that modifying data in system tables in SQL Server
>> > 2005
>> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
>> > failing due to records existing in sys.columns, but not in sys.objects.
>> >
>> > I believe these orphaned rows are a result of a delete statement being ran
>> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
>> > upgrade. This delete removed all objects from sysobjects which were owned
>> > by
>> > a specific user id as the user was to be removed from the system. This
>> > delete apparently left records behind for 2 of these user tables in
>> > syscolumns.
>> >
>> > I have gone through the results of the dbcc checkdb and every
>> > inconsistency
>> > is in reference to these 2 object IDs existing in sys.columns, but not
>> > sys.objects. In SQL Server 2000 I would just remove these records from
>> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
>> > checkdb to fail, I'd like to see what options are available for removing
>> > these records. Any help would be greatly appreciated.
>> >
>> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
>> > dbcc checkdb with physical_only comes back clean. I assume this is due to
>> > a
>> > physical_only not performing a catalog check. Would this inconsistency
>> > cause
>> > a database restore to fail?
>> >
>> > Thanks,
>> > Greg
>>|||On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> If it's not possible to delete these rows, it looks like the only option to
> correct the inconsistency within these system tables is to export all of the
> user objects/data into a new database. I can understand the reasoning behind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be an
> exceedingly painful solution compared to just deleting all rows from a table
> where object_id = object_id. I guess the other option is to just live with
> the dbcc checkdb errors every week and keep track of the output to make sure
> that there aren't any additional errors. Hopefully there's another way.
>
Exporting the data would indeed be a painful solution but maybe it is
the only way, I really don't know. Obviously this demonstrates why it
was an incredibly bad idea to do what you did in the first place. Why
would you ever resort to deleting data directly from sysobjects? Doing
that with critical data (especially with 900GB of critical data!)
seems like a pretty irresponsible action on the part of someone.
I mention this in case you are still looking for the right solution
for whatever it is you were trying to achieve - as well as to satisfy
my own surprise and curiosity.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the feedback Tibor. I think you're probably right.
"Tibor Karaszi" wrote:
> Allow updates is indeed a dummy config in 2005. It is only there for the command not to error, it
> will not allow updates of the system tables. I would suggest using a backup on which you test DBCC
> with repair option. I'm not sure whether DBCC repair will attempt repair system table
> inconsistencies. I understand it can be a hassle considering the database size. You could open a
> case with MS Support, but to be honest, I wouldn't expect them to have much more to suggest.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
> > Thanks for the response. I have tried setting 'allow updates' to 1 and
> > deleting these rows from sys.columns, but it errors out with "Ad hoc updates
> > to system catalogs are not allowed. I'd prefer not to try any of the checkdb
> > repair options as I'm not sure whether this removes just the specific rows
> > that I'd like to delete or whole pages with other data that I don't want to
> > remove.
> >
> > If it's not possible to delete these rows, it looks like the only option to
> > correct the inconsistency within these system tables is to export all of the
> > user objects/data into a new database. I can understand the reasoning behind
> > Microsoft taking away the ability to mess around with system tables. But,
> > considering that this database is just over 900GB, exporting seems to be an
> > exceedingly painful solution compared to just deleting all rows from a table
> > where object_id = object_id. I guess the other option is to just live with
> > the dbcc checkdb errors every week and keep track of the output to make sure
> > that there aren't any additional errors. Hopefully there's another way.
> >
> > Thanks,
> > Greg
> >
> > "TheSQLGuru" wrote:
> >
> >> Check BOL for the following options for DBCC CHECKDB:
> >> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> >>
> >> Also, have you actually TRIED to delete the offending rows yourself?
> >> Although MS says not supported . . . you never know! Attempt on a TEST
> >> server first! :-)
> >>
> >> --
> >> TheSQLGuru
> >> President
> >> Indicium Resources, Inc.
> >>
> >> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> >> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> >> > Hello,
> >> >
> >> > My understanding is that modifying data in system tables in SQL Server
> >> > 2005
> >> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> >> > failing due to records existing in sys.columns, but not in sys.objects.
> >> >
> >> > I believe these orphaned rows are a result of a delete statement being ran
> >> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> >> > upgrade. This delete removed all objects from sysobjects which were owned
> >> > by
> >> > a specific user id as the user was to be removed from the system. This
> >> > delete apparently left records behind for 2 of these user tables in
> >> > syscolumns.
> >> >
> >> > I have gone through the results of the dbcc checkdb and every
> >> > inconsistency
> >> > is in reference to these 2 object IDs existing in sys.columns, but not
> >> > sys.objects. In SQL Server 2000 I would just remove these records from
> >> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
> >> > checkdb to fail, I'd like to see what options are available for removing
> >> > these records. Any help would be greatly appreciated.
> >> >
> >> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> >> > dbcc checkdb with physical_only comes back clean. I assume this is due to
> >> > a
> >> > physical_only not performing a catalog check. Would this inconsistency
> >> > cause
> >> > a database restore to fail?
> >> >
> >> > Thanks,
> >> > Greg
> >>
> >>
> >>
>|||Well, thanks for the comments David, although not very helpful. I wasn't
actually the person who originally deleted these records, but I am left to
figure this out after the checkdb came back with errors.
From what I understand, this was done to try and drop 20,000+ objects that
were owned by a particular user id as this user was to be dropped.
"David Portas" wrote:
> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> >
> > If it's not possible to delete these rows, it looks like the only option to
> > correct the inconsistency within these system tables is to export all of the
> > user objects/data into a new database. I can understand the reasoning behind
> > Microsoft taking away the ability to mess around with system tables. But,
> > considering that this database is just over 900GB, exporting seems to be an
> > exceedingly painful solution compared to just deleting all rows from a table
> > where object_id = object_id. I guess the other option is to just live with
> > the dbcc checkdb errors every week and keep track of the output to make sure
> > that there aren't any additional errors. Hopefully there's another way.
> >
> Exporting the data would indeed be a painful solution but maybe it is
> the only way, I really don't know. Obviously this demonstrates why it
> was an incredibly bad idea to do what you did in the first place. Why
> would you ever resort to deleting data directly from sysobjects? Doing
> that with critical data (especially with 900GB of critical data!)
> seems like a pretty irresponsible action on the part of someone.
> I mention this in case you are still looking for the right solution
> for whatever it is you were trying to achieve - as well as to satisfy
> my own surprise and curiosity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||On 24 Mar, 14:38, gj111 <g...@.discussions.microsoft.com> wrote:
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
>
Then it was perhaps due to ignorance and inexperience rather than pure
recklessness. Surely the quick, easy and safe way would have been to
script some DROP statements from the catalogue to do the same job. Or
to use sp_changeobjectowner.
As you are the person who has to do the mopping up, you might wonder
what other horrors you are going to discover. Good luck!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
You might already be familiar with this technique, but for the benefit of other readers:
These type of operations are pretty straight forward to automate by generating the SQL statements
and copy the results into a query window and execute it:
SELECT 'DROP TABLE dbo.[' + name + ']'
FROM sysobjects
WHERE uid = USER_ID('dbo')
AND type = 'U'
Above is for 2000. Similar can be done for 2005 using schema and catalog views.
Or, one can use a cursor and built the DROP command in a variable which you then execute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only option to
>> > correct the inconsistency within these system tables is to export all of the
>> > user objects/data into a new database. I can understand the reasoning behind
>> > Microsoft taking away the ability to mess around with system tables. But,
>> > considering that this database is just over 900GB, exporting seems to be an
>> > exceedingly painful solution compared to just deleting all rows from a table
>> > where object_id = object_id. I guess the other option is to just live with
>> > the dbcc checkdb errors every week and keep track of the output to make sure
>> > that there aren't any additional errors. Hopefully there's another way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||I would recommend taking the original suggestion of contacting Microsoft
support. They do have ways of modifying the system tables but unless you
really know what you're doing, modifying deleting these rows may make the
problem worse. If nothing else, there are a lot of disk pages allocated to
those 20,000 tables that are no longer addressable and will need to be
cleaned up.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only
>> > option to
>> > correct the inconsistency within these system tables is to export all
>> > of the
>> > user objects/data into a new database. I can understand the reasoning
>> > behind
>> > Microsoft taking away the ability to mess around with system tables.
>> > But,
>> > considering that this database is just over 900GB, exporting seems to
>> > be an
>> > exceedingly painful solution compared to just deleting all rows from a
>> > table
>> > where object_id = object_id. I guess the other option is to just live
>> > with
>> > the dbcc checkdb errors every week and keep track of the output to make
>> > sure
>> > that there aren't any additional errors. Hopefully there's another
>> > way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||I discussed this with Greg offline and the solution I offered (which is
unfortunately undocumented) seems to have done the trick without any
size-of-data operations. It only worked because the corruption is totally
benign in this case - so its not something that I can publicize (although
you're welcome to email me through the blog link below)
Best way to cope with this once its happened is to call Product Support and
have them help you (or email me if you don't need an instantaneous
response). Best way to avoid it is to educate your DBAs not to mess with the
system tables...
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only
>> > option to
>> > correct the inconsistency within these system tables is to export all
>> > of the
>> > user objects/data into a new database. I can understand the reasoning
>> > behind
>> > Microsoft taking away the ability to mess around with system tables.
>> > But,
>> > considering that this database is just over 900GB, exporting seems to
>> > be an
>> > exceedingly painful solution compared to just deleting all rows from a
>> > table
>> > where object_id = object_id. I guess the other option is to just live
>> > with
>> > the dbcc checkdb errors every week and keep track of the output to make
>> > sure
>> > that there aren't any additional errors. Hopefully there's another
>> > way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||Thanks Roger. I'll definitely get with support. I'm also in Orlando this
week, maybe I'll get lucky and get a chance to pick someone's brain.
Thanks,
Greg
"Roger Wolter[MSFT]" wrote:
> I would recommend taking the original suggestion of contacting Microsoft
> support. They do have ways of modifying the system tables but unless you
> really know what you're doing, modifying deleting these rows may make the
> problem worse. If nothing else, there are a lot of disk pages allocated to
> those 20,000 tables that are no longer addressable and will need to be
> cleaned up.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> > Well, thanks for the comments David, although not very helpful. I wasn't
> > actually the person who originally deleted these records, but I am left to
> > figure this out after the checkdb came back with errors.
> >
> > From what I understand, this was done to try and drop 20,000+ objects that
> > were owned by a particular user id as this user was to be dropped.
> >
> > "David Portas" wrote:
> >
> >> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> >> >
> >> > If it's not possible to delete these rows, it looks like the only
> >> > option to
> >> > correct the inconsistency within these system tables is to export all
> >> > of the
> >> > user objects/data into a new database. I can understand the reasoning
> >> > behind
> >> > Microsoft taking away the ability to mess around with system tables.
> >> > But,
> >> > considering that this database is just over 900GB, exporting seems to
> >> > be an
> >> > exceedingly painful solution compared to just deleting all rows from a
> >> > table
> >> > where object_id = object_id. I guess the other option is to just live
> >> > with
> >> > the dbcc checkdb errors every week and keep track of the output to make
> >> > sure
> >> > that there aren't any additional errors. Hopefully there's another
> >> > way.
> >> >
> >>
> >> Exporting the data would indeed be a painful solution but maybe it is
> >> the only way, I really don't know. Obviously this demonstrates why it
> >> was an incredibly bad idea to do what you did in the first place. Why
> >> would you ever resort to deleting data directly from sysobjects? Doing
> >> that with critical data (especially with 900GB of critical data!)
> >> seems like a pretty irresponsible action on the part of someone.
> >>
> >> I mention this in case you are still looking for the right solution
> >> for whatever it is you were trying to achieve - as well as to satisfy
> >> my own surprise and curiosity.
> >>
> >> --
> >> David Portas, SQL Server MVP
> >>
> >> Whenever possible please post enough code to reproduce your problem.
> >> Including CREATE TABLE and INSERT statements usually helps.
> >> State what version of SQL Server you are using and specify the content
> >> of any error messages.
> >>
> >> SQL Server Books Online:
> >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> >> --
> >>
> >>
>sql
Delete data from sys.columns in SQL Server 2005
Hello,
My understanding is that modifying data in system tables in SQL Server 2005
isn't allowed. Currently, I have an issue where a full dbcc checkdb is
failing due to records existing in sys.columns, but not in sys.objects.
I believe these orphaned rows are a result of a delete statement being ran
against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
upgrade. This delete removed all objects from sysobjects which were owned b
y
a specific user id as the user was to be removed from the system. This
delete apparently left records behind for 2 of these user tables in
syscolumns.
I have gone through the results of the dbcc checkdb and every inconsistency
is in reference to these 2 object IDs existing in sys.columns, but not
sys.objects. In SQL Server 2000 I would just remove these records from
syscolumns and I believe I'd be good. Since this is causing the full dbcc
checkdb to fail, I'd like to see what options are available for removing
these records. Any help would be greatly appreciated.
Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
dbcc checkdb with physical_only comes back clean. I assume this is due to a
physical_only not performing a catalog check. Would this inconsistency caus
e
a database restore to fail?
Thanks,
GregCheck BOL for the following options for DBCC CHECKDB:
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Also, have you actually TRIED to delete the offending rows yourself?
Although MS says not supported . . . you never know! Attempt on a TEST
server first! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> Hello,
> My understanding is that modifying data in system tables in SQL Server
> 2005
> isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> failing due to records existing in sys.columns, but not in sys.objects.
> I believe these orphaned rows are a result of a delete statement being ran
> against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> upgrade. This delete removed all objects from sysobjects which were owned
> by
> a specific user id as the user was to be removed from the system. This
> delete apparently left records behind for 2 of these user tables in
> syscolumns.
> I have gone through the results of the dbcc checkdb and every
> inconsistency
> is in reference to these 2 object IDs existing in sys.columns, but not
> sys.objects. In SQL Server 2000 I would just remove these records from
> syscolumns and I believe I'd be good. Since this is causing the full dbcc
> checkdb to fail, I'd like to see what options are available for removing
> these records. Any help would be greatly appreciated.
> Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> dbcc checkdb with physical_only comes back clean. I assume this is due to
> a
> physical_only not performing a catalog check. Would this inconsistency
> cause
> a database restore to fail?
> Thanks,
> Greg|||Thanks for the response. I have tried setting 'allow updates' to 1 and
deleting these rows from sys.columns, but it errors out with "Ad hoc updates
to system catalogs are not allowed. I'd prefer not to try any of the checkd
b
repair options as I'm not sure whether this removes just the specific rows
that I'd like to delete or whole pages with other data that I don't want to
remove.
If it's not possible to delete these rows, it looks like the only option to
correct the inconsistency within these system tables is to export all of the
user objects/data into a new database. I can understand the reasoning behin
d
Microsoft taking away the ability to mess around with system tables. But,
considering that this database is just over 900GB, exporting seems to be an
exceedingly painful solution compared to just deleting all rows from a table
where object_id = object_id. I guess the other option is to just live with
the dbcc checkdb errors every week and keep track of the output to make sure
that there aren't any additional errors. Hopefully there's another way.
Thanks,
Greg
"TheSQLGuru" wrote:
> Check BOL for the following options for DBCC CHECKDB:
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Also, have you actually TRIED to delete the offending rows yourself?
> Although MS says not supported . . . you never know! Attempt on a TEST
> server first! :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
>
>|||Allow updates is indeed a dummy config in 2005. It is only there for the com
mand not to error, it
will not allow updates of the system tables. I would suggest using a backup
on which you test DBCC
with repair option. I'm not sure whether DBCC repair will attempt repair sys
tem table
inconsistencies. I understand it can be a hassle considering the database si
ze. You could open a
case with MS Support, but to be honest, I wouldn't expect them to have much
more to suggest.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I have tried setting 'allow updates' to 1 and
> deleting these rows from sys.columns, but it errors out with "Ad hoc updat
es
> to system catalogs are not allowed. I'd prefer not to try any of the chec
kdb
> repair options as I'm not sure whether this removes just the specific rows
> that I'd like to delete or whole pages with other data that I don't want t
o
> remove.
> If it's not possible to delete these rows, it looks like the only option t
o
> correct the inconsistency within these system tables is to export all of t
he
> user objects/data into a new database. I can understand the reasoning beh
ind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be a
n
> exceedingly painful solution compared to just deleting all rows from a tab
le
> where object_id = object_id. I guess the other option is to just live wit
h
> the dbcc checkdb errors every week and keep track of the output to make su
re
> that there aren't any additional errors. Hopefully there's another way.
> Thanks,
> Greg
> "TheSQLGuru" wrote:
>|||On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> If it's not possible to delete these rows, it looks like the only option t
o
> correct the inconsistency within these system tables is to export all of t
he
> user objects/data into a new database. I can understand the reasoning beh
ind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be a
n
> exceedingly painful solution compared to just deleting all rows from a tab
le
> where object_id = object_id. I guess the other option is to just live wit
h
> the dbcc checkdb errors every week and keep track of the output to make su
re
> that there aren't any additional errors. Hopefully there's another way.
>
Exporting the data would indeed be a painful solution but maybe it is
the only way, I really don't know. Obviously this demonstrates why it
was an incredibly bad idea to do what you did in the first place. Why
would you ever resort to deleting data directly from sysobjects? Doing
that with critical data (especially with 900GB of critical data!)
seems like a pretty irresponsible action on the part of someone.
I mention this in case you are still looking for the right solution
for whatever it is you were trying to achieve - as well as to satisfy
my own surprise and curiosity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the feedback Tibor. I think you're probably right.
"Tibor Karaszi" wrote:
> Allow updates is indeed a dummy config in 2005. It is only there for the c
ommand not to error, it
> will not allow updates of the system tables. I would suggest using a backu
p on which you test DBCC
> with repair option. I'm not sure whether DBCC repair will attempt repair s
ystem table
> inconsistencies. I understand it can be a hassle considering the database
size. You could open a
> case with MS Support, but to be honest, I wouldn't expect them to have muc
h more to suggest.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
>|||Well, thanks for the comments David, although not very helpful. I wasn't
actually the person who originally deleted these records, but I am left to
figure this out after the checkdb came back with errors.
From what I understand, this was done to try and drop 20,000+ objects that
were owned by a particular user id as this user was to be dropped.
"David Portas" wrote:
> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> Exporting the data would indeed be a painful solution but maybe it is
> the only way, I really don't know. Obviously this demonstrates why it
> was an incredibly bad idea to do what you did in the first place. Why
> would you ever resort to deleting data directly from sysobjects? Doing
> that with critical data (especially with 900GB of critical data!)
> seems like a pretty irresponsible action on the part of someone.
> I mention this in case you are still looking for the right solution
> for whatever it is you were trying to achieve - as well as to satisfy
> my own surprise and curiosity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
You might already be familiar with this technique, but for the benefit of ot
her readers:
These type of operations are pretty straight forward to automate by generati
ng the SQL statements
and copy the results into a query window and execute it:
SELECT 'DROP TABLE dbo.[' + name + ']'
FROM sysobjects
WHERE uid = USER_ID('dbo')
AND type = 'U'
Above is for 2000. Similar can be done for 2005 using schema and catalog vie
ws.
Or, one can use a cursor and built the DROP command in a variable which you
then execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>|||On 24 Mar, 14:38, gj111 <g...@.discussions.microsoft.com> wrote:
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
>
Then it was perhaps due to ignorance and inexperience rather than pure
recklessness. Surely the quick, easy and safe way would have been to
script some DROP statements from the catalogue to do the same job. Or
to use sp_changeobjectowner.
As you are the person who has to do the mopping up, you might wonder
what other horrors you are going to discover. Good luck!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I would recommend taking the original suggestion of contacting Microsoft
support. They do have ways of modifying the system tables but unless you
really know what you're doing, modifying deleting these rows may make the
problem worse. If nothing else, there are a lot of disk pages allocated to
those 20,000 tables that are no longer addressable and will need to be
cleaned up.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>
My understanding is that modifying data in system tables in SQL Server 2005
isn't allowed. Currently, I have an issue where a full dbcc checkdb is
failing due to records existing in sys.columns, but not in sys.objects.
I believe these orphaned rows are a result of a delete statement being ran
against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
upgrade. This delete removed all objects from sysobjects which were owned b
y
a specific user id as the user was to be removed from the system. This
delete apparently left records behind for 2 of these user tables in
syscolumns.
I have gone through the results of the dbcc checkdb and every inconsistency
is in reference to these 2 object IDs existing in sys.columns, but not
sys.objects. In SQL Server 2000 I would just remove these records from
syscolumns and I believe I'd be good. Since this is causing the full dbcc
checkdb to fail, I'd like to see what options are available for removing
these records. Any help would be greatly appreciated.
Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
dbcc checkdb with physical_only comes back clean. I assume this is due to a
physical_only not performing a catalog check. Would this inconsistency caus
e
a database restore to fail?
Thanks,
GregCheck BOL for the following options for DBCC CHECKDB:
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Also, have you actually TRIED to delete the offending rows yourself?
Although MS says not supported . . . you never know! Attempt on a TEST
server first! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> Hello,
> My understanding is that modifying data in system tables in SQL Server
> 2005
> isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> failing due to records existing in sys.columns, but not in sys.objects.
> I believe these orphaned rows are a result of a delete statement being ran
> against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> upgrade. This delete removed all objects from sysobjects which were owned
> by
> a specific user id as the user was to be removed from the system. This
> delete apparently left records behind for 2 of these user tables in
> syscolumns.
> I have gone through the results of the dbcc checkdb and every
> inconsistency
> is in reference to these 2 object IDs existing in sys.columns, but not
> sys.objects. In SQL Server 2000 I would just remove these records from
> syscolumns and I believe I'd be good. Since this is causing the full dbcc
> checkdb to fail, I'd like to see what options are available for removing
> these records. Any help would be greatly appreciated.
> Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> dbcc checkdb with physical_only comes back clean. I assume this is due to
> a
> physical_only not performing a catalog check. Would this inconsistency
> cause
> a database restore to fail?
> Thanks,
> Greg|||Thanks for the response. I have tried setting 'allow updates' to 1 and
deleting these rows from sys.columns, but it errors out with "Ad hoc updates
to system catalogs are not allowed. I'd prefer not to try any of the checkd
b
repair options as I'm not sure whether this removes just the specific rows
that I'd like to delete or whole pages with other data that I don't want to
remove.
If it's not possible to delete these rows, it looks like the only option to
correct the inconsistency within these system tables is to export all of the
user objects/data into a new database. I can understand the reasoning behin
d
Microsoft taking away the ability to mess around with system tables. But,
considering that this database is just over 900GB, exporting seems to be an
exceedingly painful solution compared to just deleting all rows from a table
where object_id = object_id. I guess the other option is to just live with
the dbcc checkdb errors every week and keep track of the output to make sure
that there aren't any additional errors. Hopefully there's another way.
Thanks,
Greg
"TheSQLGuru" wrote:
> Check BOL for the following options for DBCC CHECKDB:
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Also, have you actually TRIED to delete the offending rows yourself?
> Although MS says not supported . . . you never know! Attempt on a TEST
> server first! :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
>
>|||Allow updates is indeed a dummy config in 2005. It is only there for the com
mand not to error, it
will not allow updates of the system tables. I would suggest using a backup
on which you test DBCC
with repair option. I'm not sure whether DBCC repair will attempt repair sys
tem table
inconsistencies. I understand it can be a hassle considering the database si
ze. You could open a
case with MS Support, but to be honest, I wouldn't expect them to have much
more to suggest.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I have tried setting 'allow updates' to 1 and
> deleting these rows from sys.columns, but it errors out with "Ad hoc updat
es
> to system catalogs are not allowed. I'd prefer not to try any of the chec
kdb
> repair options as I'm not sure whether this removes just the specific rows
> that I'd like to delete or whole pages with other data that I don't want t
o
> remove.
> If it's not possible to delete these rows, it looks like the only option t
o
> correct the inconsistency within these system tables is to export all of t
he
> user objects/data into a new database. I can understand the reasoning beh
ind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be a
n
> exceedingly painful solution compared to just deleting all rows from a tab
le
> where object_id = object_id. I guess the other option is to just live wit
h
> the dbcc checkdb errors every week and keep track of the output to make su
re
> that there aren't any additional errors. Hopefully there's another way.
> Thanks,
> Greg
> "TheSQLGuru" wrote:
>|||On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> If it's not possible to delete these rows, it looks like the only option t
o
> correct the inconsistency within these system tables is to export all of t
he
> user objects/data into a new database. I can understand the reasoning beh
ind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be a
n
> exceedingly painful solution compared to just deleting all rows from a tab
le
> where object_id = object_id. I guess the other option is to just live wit
h
> the dbcc checkdb errors every week and keep track of the output to make su
re
> that there aren't any additional errors. Hopefully there's another way.
>
Exporting the data would indeed be a painful solution but maybe it is
the only way, I really don't know. Obviously this demonstrates why it
was an incredibly bad idea to do what you did in the first place. Why
would you ever resort to deleting data directly from sysobjects? Doing
that with critical data (especially with 900GB of critical data!)
seems like a pretty irresponsible action on the part of someone.
I mention this in case you are still looking for the right solution
for whatever it is you were trying to achieve - as well as to satisfy
my own surprise and curiosity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the feedback Tibor. I think you're probably right.
"Tibor Karaszi" wrote:
> Allow updates is indeed a dummy config in 2005. It is only there for the c
ommand not to error, it
> will not allow updates of the system tables. I would suggest using a backu
p on which you test DBCC
> with repair option. I'm not sure whether DBCC repair will attempt repair s
ystem table
> inconsistencies. I understand it can be a hassle considering the database
size. You could open a
> case with MS Support, but to be honest, I wouldn't expect them to have muc
h more to suggest.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
>|||Well, thanks for the comments David, although not very helpful. I wasn't
actually the person who originally deleted these records, but I am left to
figure this out after the checkdb came back with errors.
From what I understand, this was done to try and drop 20,000+ objects that
were owned by a particular user id as this user was to be dropped.
"David Portas" wrote:
> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> Exporting the data would indeed be a painful solution but maybe it is
> the only way, I really don't know. Obviously this demonstrates why it
> was an incredibly bad idea to do what you did in the first place. Why
> would you ever resort to deleting data directly from sysobjects? Doing
> that with critical data (especially with 900GB of critical data!)
> seems like a pretty irresponsible action on the part of someone.
> I mention this in case you are still looking for the right solution
> for whatever it is you were trying to achieve - as well as to satisfy
> my own surprise and curiosity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
You might already be familiar with this technique, but for the benefit of ot
her readers:
These type of operations are pretty straight forward to automate by generati
ng the SQL statements
and copy the results into a query window and execute it:
SELECT 'DROP TABLE dbo.[' + name + ']'
FROM sysobjects
WHERE uid = USER_ID('dbo')
AND type = 'U'
Above is for 2000. Similar can be done for 2005 using schema and catalog vie
ws.
Or, one can use a cursor and built the DROP command in a variable which you
then execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>|||On 24 Mar, 14:38, gj111 <g...@.discussions.microsoft.com> wrote:
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
>
Then it was perhaps due to ignorance and inexperience rather than pure
recklessness. Surely the quick, easy and safe way would have been to
script some DROP statements from the catalogue to do the same job. Or
to use sp_changeobjectowner.
As you are the person who has to do the mopping up, you might wonder
what other horrors you are going to discover. Good luck!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I would recommend taking the original suggestion of contacting Microsoft
support. They do have ways of modifying the system tables but unless you
really know what you're doing, modifying deleting these rows may make the
problem worse. If nothing else, there are a lot of disk pages allocated to
those 20,000 tables that are no longer addressable and will need to be
cleaned up.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>
Monday, March 19, 2012
Defragment Heap Tables
Hi guru,
I've been new company for only a month and started analysing Index
Fragmentation.
After I ran DBCC DBREINDEX and capture data into permanent table, I 've
seen lots of tables with no indexes. These tables showed:
Very low scan density,
High extent fragmentation
High Avg. Bytes Free per Page
What are the best strategies to defragment tables with no indexes?
I'm planning to make a rule that each table must have a clustered index
and this index must be created on the best column (highest
selectivity).
Please help.
Thanks,
Silaphet,kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:
> I've been new company for only a month and started analysing Index
> Fragmentation.
> After I ran DBCC DBREINDEX and capture data into permanent table, I 've
> seen lots of tables with no indexes. These tables showed:
> Very low scan density,
> High extent fragmentation
> High Avg. Bytes Free per Page
> What are the best strategies to defragment tables with no indexes?
Create a clustered index on them. If the index is absolute undesired,
drop the index once your done.
> I'm planning to make a rule that each table must have a clustered index
> and this index must be created on the best column (highest
> selectivity).
Yes, that is a good rule. Heap tables with deletions can easily become
very fragmented.
As for which column to cluster on, you may want to find a column
that grows monotonically, if all you want to do is avoid fragmentation.
Of course, adding indexes to improve queries is a good idea too!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Defragment all indexes in a database?
How can I defragment all the indexes in my databases using dbcc indexdefrag?Have you read this?
http://msdn.microsoft.com/library/d...
0o9.asp
ML|||This defrags only 1 index at a time. I would like to defrag all the indexes
in the database with one command
"ML" wrote:
> Have you read this?
> http://msdn.microsoft.com/library/d...r />
_30o9.asp
>
> ML|||Google the usage for the undocumented SP called sp_MSforeachtable. It can be
used to execute a command for each table in a database.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:039DB153-5857-483C-8BDF-97DB1AD6B3AE@.microsoft.com...
> This defrags only 1 index at a time. I would like to defrag all the
> indexes
> in the database with one command
> "ML" wrote:
>|||Here, I hope this helps.
http://milambda.blogspot.com/2005/0...in-current.html
ML|||I work for a software house which has developed database defragmentation sof
tware for SQL Server. It can defrag more than one index at a time and has sc
heduling facilities built in.
If anyone is interested in beta-testing it, I would like to hear from you as
it is close to launch. My company is willing to offer a free copy of the so
ftware to anyone who can provide genuine and useful feedback. Please send me
a private message from the members' section if you are interested.
Regards,
Martin
[QUOTE]Originally posted by Mike
How can I defragment all the indexes in my databases using dbcc indexdefrag? [/QUOTE
]
http://msdn.microsoft.com/library/d...
0o9.asp
ML|||This defrags only 1 index at a time. I would like to defrag all the indexes
in the database with one command
"ML" wrote:
> Have you read this?
> http://msdn.microsoft.com/library/d...r />
_30o9.asp
>
> ML|||Google the usage for the undocumented SP called sp_MSforeachtable. It can be
used to execute a command for each table in a database.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:039DB153-5857-483C-8BDF-97DB1AD6B3AE@.microsoft.com...
> This defrags only 1 index at a time. I would like to defrag all the
> indexes
> in the database with one command
> "ML" wrote:
>|||Here, I hope this helps.
http://milambda.blogspot.com/2005/0...in-current.html
ML|||I work for a software house which has developed database defragmentation sof
tware for SQL Server. It can defrag more than one index at a time and has sc
heduling facilities built in.
If anyone is interested in beta-testing it, I would like to hear from you as
it is close to launch. My company is willing to offer a free copy of the so
ftware to anyone who can provide genuine and useful feedback. Please send me
a private message from the members' section if you are interested.
Regards,
Martin
[QUOTE]Originally posted by Mike
How can I defragment all the indexes in my databases using dbcc indexdefrag? [/QUOTE
]
Sunday, March 11, 2012
defrag system tables indexes
does anyone know how could i defrag system tables indexes.. because dbcc
indexdefrag or rebuild can't be used, thksnikolakg wrote:
> does anyone know how could i defrag system tables indexes.. because dbcc
> indexdefrag or rebuild can't be used, thks
You shouldn't have to mess with the system table indexes. They're small
enough that defragging really isn't going to help them, and they're
static enough that they shouldn't fragment much, if at all, anyway.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i ran dbcc showcontig and the scan density for system tables was too low
12-15%.
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" ÎγγÏ?αÏ?ε:
> nikolakg wrote:
> > does anyone know how could i defrag system tables indexes.. because dbcc
> > indexdefrag or rebuild can't be used, thks
> You shouldn't have to mess with the system table indexes. They're small
> enough that defragging really isn't going to help them, and they're
> static enough that they shouldn't fragment much, if at all, anyway.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> i ran dbcc showcontig and the scan density for system tables was too low
> 12-15%.
>
How many pages are in the indexes you're looking at?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
syscolumns syscolumns 244 12200 12.810
sysdepends ncsysdepends1 56 2027 13.462
syscolumns ncsyscolumns 93 12200 13.483
sysindexes sysindexes 187 1063 13.793
sysdepends sysdepends 57 2027 14.815
syscomments syscomments 926 1505 16.885
sysobjects sysobjects 27 1680 17.391
sysobjects ncsysobjects 14 1680 20.000
sysproperties sysproperties 6 65 20.000
sysobjects ncsysobjects2 5 1680 25.000
this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
(and some more columns)
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" ÎγγÏ?αÏ?ε:
> nikolakg wrote:
> > i ran dbcc showcontig and the scan density for system tables was too low
> > 12-15%.
> >
> How many pages are in the indexes you're looking at?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> syscolumns syscolumns 244 12200 12.810
> sysdepends ncsysdepends1 56 2027 13.462
> syscolumns ncsyscolumns 93 12200 13.483
> sysindexes sysindexes 187 1063 13.793
> sysdepends sysdepends 57 2027 14.815
> syscomments syscomments 926 1505 16.885
> sysobjects sysobjects 27 1680 17.391
> sysobjects ncsysobjects 14 1680 20.000
> sysproperties sysproperties 6 65 20.000
> sysobjects ncsysobjects2 5 1680 25.000
>
> this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> (and some more columns)
>
Less than 1000 pages, don't worry about them.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||thank you!
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" ÎγγÏ?αÏ?ε:
> nikolakg wrote:
> > OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> > syscolumns syscolumns 244 12200 12.810
> > sysdepends ncsysdepends1 56 2027 13.462
> > syscolumns ncsyscolumns 93 12200 13.483
> > sysindexes sysindexes 187 1063 13.793
> > sysdepends sysdepends 57 2027 14.815
> > syscomments syscomments 926 1505 16.885
> > sysobjects sysobjects 27 1680 17.391
> > sysobjects ncsysobjects 14 1680 20.000
> > sysproperties sysproperties 6 65 20.000
> > sysobjects ncsysobjects2 5 1680 25.000
> >
> >
> > this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> > (and some more columns)
> >
> Less than 1000 pages, don't worry about them.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||We have an issue because text data from sysIndexes is sitting right at the
end of our primary datafile and this stops us truncating the end off the
file.
We have done a load of housekeeping and could reclaim a greate deal of disk
space.
We cannot do a shrink/movc data pages due to log shipping across our WAN.
DBCC extentinfo (Production, sysIndexes, -1)
Paul
indexdefrag or rebuild can't be used, thksnikolakg wrote:
> does anyone know how could i defrag system tables indexes.. because dbcc
> indexdefrag or rebuild can't be used, thks
You shouldn't have to mess with the system table indexes. They're small
enough that defragging really isn't going to help them, and they're
static enough that they shouldn't fragment much, if at all, anyway.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i ran dbcc showcontig and the scan density for system tables was too low
12-15%.
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" ÎγγÏ?αÏ?ε:
> nikolakg wrote:
> > does anyone know how could i defrag system tables indexes.. because dbcc
> > indexdefrag or rebuild can't be used, thks
> You shouldn't have to mess with the system table indexes. They're small
> enough that defragging really isn't going to help them, and they're
> static enough that they shouldn't fragment much, if at all, anyway.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> i ran dbcc showcontig and the scan density for system tables was too low
> 12-15%.
>
How many pages are in the indexes you're looking at?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
syscolumns syscolumns 244 12200 12.810
sysdepends ncsysdepends1 56 2027 13.462
syscolumns ncsyscolumns 93 12200 13.483
sysindexes sysindexes 187 1063 13.793
sysdepends sysdepends 57 2027 14.815
syscomments syscomments 926 1505 16.885
sysobjects sysobjects 27 1680 17.391
sysobjects ncsysobjects 14 1680 20.000
sysproperties sysproperties 6 65 20.000
sysobjects ncsysobjects2 5 1680 25.000
this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
(and some more columns)
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" ÎγγÏ?αÏ?ε:
> nikolakg wrote:
> > i ran dbcc showcontig and the scan density for system tables was too low
> > 12-15%.
> >
> How many pages are in the indexes you're looking at?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> syscolumns syscolumns 244 12200 12.810
> sysdepends ncsysdepends1 56 2027 13.462
> syscolumns ncsyscolumns 93 12200 13.483
> sysindexes sysindexes 187 1063 13.793
> sysdepends sysdepends 57 2027 14.815
> syscomments syscomments 926 1505 16.885
> sysobjects sysobjects 27 1680 17.391
> sysobjects ncsysobjects 14 1680 20.000
> sysproperties sysproperties 6 65 20.000
> sysobjects ncsysobjects2 5 1680 25.000
>
> this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> (and some more columns)
>
Less than 1000 pages, don't worry about them.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||thank you!
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" ÎγγÏ?αÏ?ε:
> nikolakg wrote:
> > OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> > syscolumns syscolumns 244 12200 12.810
> > sysdepends ncsysdepends1 56 2027 13.462
> > syscolumns ncsyscolumns 93 12200 13.483
> > sysindexes sysindexes 187 1063 13.793
> > sysdepends sysdepends 57 2027 14.815
> > syscomments syscomments 926 1505 16.885
> > sysobjects sysobjects 27 1680 17.391
> > sysobjects ncsysobjects 14 1680 20.000
> > sysproperties sysproperties 6 65 20.000
> > sysobjects ncsysobjects2 5 1680 25.000
> >
> >
> > this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> > (and some more columns)
> >
> Less than 1000 pages, don't worry about them.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||We have an issue because text data from sysIndexes is sitting right at the
end of our primary datafile and this stops us truncating the end off the
file.
We have done a load of housekeeping and could reclaim a greate deal of disk
space.
We cannot do a shrink/movc data pages due to log shipping across our WAN.
DBCC extentinfo (Production, sysIndexes, -1)
Paul
defrag system objects/tables
Hi,
I used the DBCC SHOWCONTIG on a database and it shows :
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 2817.5
- Avg. Page Density (full)................: 65.19%
so i tried to defrag this particular table
using DBCC INDEX command
but got this error DBCC INDEXDEFRAG cannot be used on system table indexes
as you might have noticed the logical/exten scan fragmentation is quite high ,
in this case how can i do a defrag across the whole database including those
system tables/objects ? and do i need to do it offline/online ?
apreciate ur advice
tks & rdgs
Message posted via http://www.droptable.com
hi,
maxzsim via droptable.com wrote:
> Hi,
> I used the DBCC SHOWCONTIG on a database and it shows :
> DBCC SHOWCONTIG scanning 'sysobjects' table...
> Table: 'sysobjects' (1); index ID: 1, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 4
> - Extents Scanned.......................: 4
as you already have been reported, you can not "reindex" system tables...
anyway the report you got is not that drammatic...
you have 4 pages in mixed extents as the object does not fill a full extent
as well...
so keep your house keeping tasks on user objects only...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
I used the DBCC SHOWCONTIG on a database and it shows :
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 2817.5
- Avg. Page Density (full)................: 65.19%
so i tried to defrag this particular table
using DBCC INDEX command
but got this error DBCC INDEXDEFRAG cannot be used on system table indexes
as you might have noticed the logical/exten scan fragmentation is quite high ,
in this case how can i do a defrag across the whole database including those
system tables/objects ? and do i need to do it offline/online ?
apreciate ur advice
tks & rdgs
Message posted via http://www.droptable.com
hi,
maxzsim via droptable.com wrote:
> Hi,
> I used the DBCC SHOWCONTIG on a database and it shows :
> DBCC SHOWCONTIG scanning 'sysobjects' table...
> Table: 'sysobjects' (1); index ID: 1, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 4
> - Extents Scanned.......................: 4
as you already have been reported, you can not "reindex" system tables...
anyway the report you got is not that drammatic...
you have 4 pages in mixed extents as the object does not fill a full extent
as well...
so keep your house keeping tasks on user objects only...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Subscribe to:
Posts (Atom)