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