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 system. Show all posts
Showing posts with label system. 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 tables that have no clustered index
Hi!
We have several SQL Servers in a system that replicates
information in non-realtime between them using MSMQ and
Biztalk and to guarantee uniqueness it uses GUID:s as
primary and foreign keys. When planning this solution we
were recommended by Microsoft to use only non-clustered
indexes on these tables.
Since there are a lot of inserts and updates to the data
in this system we have now got a lot of really
fragmentated tables but since we have no clustered indexes
DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
solve this problem?
I have searched the newsgroups (and of course "Inside sQL
Server 2000", Hi Kalen! Any suggestions..?) but found no
answers that works. After reading a post here I tried both
DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
hardly effects the terrible scan density (DBCC SHOWCONTIG)
for these tables. The only other solution I have read
about is to use BCP to export and import the tables but
this quite complicated solution can't really be included
in our weekly maintenance job which is what we want.
Please, anyone, suggestions? I can't be the only one with
this problem..?
- AllanRead in "BOL - DBCC DBREINDEX" after reindexing, update
your statistics manually after the reindex.
Greg
>--Original Message--
>Hi!
>We have several SQL Servers in a system that replicates
>information in non-realtime between them using MSMQ and
>Biztalk and to guarantee uniqueness it uses GUID:s as
>primary and foreign keys. When planning this solution we
>were recommended by Microsoft to use only non-clustered
>indexes on these tables.
>Since there are a lot of inserts and updates to the data
>in this system we have now got a lot of really
>fragmentated tables but since we have no clustered
indexes
>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
>solve this problem?
>I have searched the newsgroups (and of course "Inside sQL
>Server 2000", Hi Kalen! Any suggestions..?) but found no
>answers that works. After reading a post here I tried
both
>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
>hardly effects the terrible scan density (DBCC
SHOWCONTIG)
>for these tables. The only other solution I have read
>about is to use BCP to export and import the tables but
>this quite complicated solution can't really be included
>in our weekly maintenance job which is what we want.
>Please, anyone, suggestions? I can't be the only one with
>this problem..?
>- Allan
>.
>|||There's no easy way to reorg (or perhaps "compact" is a better word as the data isn't sorted in any
way) for a heap. Two ways I can think of:
Create a clustered index and drop it.
Export/import of the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
news:03d101c38386$33166d70$a301280a@.phx.gbl...
> As far as I have understood (and tested) neither DBCC
> DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> have clustered indexes on these tables the data is not
> stored on the leaf level of the index but in a heap. What
> I want to know is how to defragment this heap..
> >--Original Message--
> >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> >your statistics manually after the reindex.
> >
> >Greg
> >
> >>--Original Message--
> >>Hi!
> >>
> >>We have several SQL Servers in a system that replicates
> >>information in non-realtime between them using MSMQ and
> >>Biztalk and to guarantee uniqueness it uses GUID:s as
> >>primary and foreign keys. When planning this solution we
> >>were recommended by Microsoft to use only non-clustered
> >>indexes on these tables.
> >>
> >>Since there are a lot of inserts and updates to the data
> >>in this system we have now got a lot of really
> >>fragmentated tables but since we have no clustered
> >indexes
> >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> to
> >>solve this problem?
> >>
> >>I have searched the newsgroups (and of course "Inside
> sQL
> >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> >>answers that works. After reading a post here I tried
> >both
> >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> it
> >>hardly effects the terrible scan density (DBCC
> >SHOWCONTIG)
> >>for these tables. The only other solution I have read
> >>about is to use BCP to export and import the tables but
> >>this quite complicated solution can't really be included
> >>in our weekly maintenance job which is what we want.
> >>
> >>Please, anyone, suggestions? I can't be the only one
> with
> >>this problem..?
> >>
> >>- Allan
> >>
> >>.
> >>
> >.
> >|||Yes Tibor has it correct. That's one of the reasons I suggest that most
tables have a clustered index.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23W979hBhDHA.616@.TK2MSFTNGP11.phx.gbl...
> There's no easy way to reorg (or perhaps "compact" is a better word as the
data isn't sorted in any
> way) for a heap. Two ways I can think of:
> Create a clustered index and drop it.
> Export/import of the data.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
> news:03d101c38386$33166d70$a301280a@.phx.gbl...
> > As far as I have understood (and tested) neither DBCC
> > DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> > have clustered indexes on these tables the data is not
> > stored on the leaf level of the index but in a heap. What
> > I want to know is how to defragment this heap..
> >
> > >--Original Message--
> > >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> > >your statistics manually after the reindex.
> > >
> > >Greg
> > >
> > >>--Original Message--
> > >>Hi!
> > >>
> > >>We have several SQL Servers in a system that replicates
> > >>information in non-realtime between them using MSMQ and
> > >>Biztalk and to guarantee uniqueness it uses GUID:s as
> > >>primary and foreign keys. When planning this solution we
> > >>were recommended by Microsoft to use only non-clustered
> > >>indexes on these tables.
> > >>
> > >>Since there are a lot of inserts and updates to the data
> > >>in this system we have now got a lot of really
> > >>fragmentated tables but since we have no clustered
> > >indexes
> > >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> > to
> > >>solve this problem?
> > >>
> > >>I have searched the newsgroups (and of course "Inside
> > sQL
> > >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> > >>answers that works. After reading a post here I tried
> > >both
> > >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> > it
> > >>hardly effects the terrible scan density (DBCC
> > >SHOWCONTIG)
> > >>for these tables. The only other solution I have read
> > >>about is to use BCP to export and import the tables but
> > >>this quite complicated solution can't really be included
> > >>in our weekly maintenance job which is what we want.
> > >>
> > >>Please, anyone, suggestions? I can't be the only one
> > with
> > >>this problem..?
> > >>
> > >>- Allan
> > >>
> > >>.
> > >>
> > >.
> > >
>
We have several SQL Servers in a system that replicates
information in non-realtime between them using MSMQ and
Biztalk and to guarantee uniqueness it uses GUID:s as
primary and foreign keys. When planning this solution we
were recommended by Microsoft to use only non-clustered
indexes on these tables.
Since there are a lot of inserts and updates to the data
in this system we have now got a lot of really
fragmentated tables but since we have no clustered indexes
DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
solve this problem?
I have searched the newsgroups (and of course "Inside sQL
Server 2000", Hi Kalen! Any suggestions..?) but found no
answers that works. After reading a post here I tried both
DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
hardly effects the terrible scan density (DBCC SHOWCONTIG)
for these tables. The only other solution I have read
about is to use BCP to export and import the tables but
this quite complicated solution can't really be included
in our weekly maintenance job which is what we want.
Please, anyone, suggestions? I can't be the only one with
this problem..?
- AllanRead in "BOL - DBCC DBREINDEX" after reindexing, update
your statistics manually after the reindex.
Greg
>--Original Message--
>Hi!
>We have several SQL Servers in a system that replicates
>information in non-realtime between them using MSMQ and
>Biztalk and to guarantee uniqueness it uses GUID:s as
>primary and foreign keys. When planning this solution we
>were recommended by Microsoft to use only non-clustered
>indexes on these tables.
>Since there are a lot of inserts and updates to the data
>in this system we have now got a lot of really
>fragmentated tables but since we have no clustered
indexes
>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
>solve this problem?
>I have searched the newsgroups (and of course "Inside sQL
>Server 2000", Hi Kalen! Any suggestions..?) but found no
>answers that works. After reading a post here I tried
both
>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
>hardly effects the terrible scan density (DBCC
SHOWCONTIG)
>for these tables. The only other solution I have read
>about is to use BCP to export and import the tables but
>this quite complicated solution can't really be included
>in our weekly maintenance job which is what we want.
>Please, anyone, suggestions? I can't be the only one with
>this problem..?
>- Allan
>.
>|||There's no easy way to reorg (or perhaps "compact" is a better word as the data isn't sorted in any
way) for a heap. Two ways I can think of:
Create a clustered index and drop it.
Export/import of the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
news:03d101c38386$33166d70$a301280a@.phx.gbl...
> As far as I have understood (and tested) neither DBCC
> DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> have clustered indexes on these tables the data is not
> stored on the leaf level of the index but in a heap. What
> I want to know is how to defragment this heap..
> >--Original Message--
> >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> >your statistics manually after the reindex.
> >
> >Greg
> >
> >>--Original Message--
> >>Hi!
> >>
> >>We have several SQL Servers in a system that replicates
> >>information in non-realtime between them using MSMQ and
> >>Biztalk and to guarantee uniqueness it uses GUID:s as
> >>primary and foreign keys. When planning this solution we
> >>were recommended by Microsoft to use only non-clustered
> >>indexes on these tables.
> >>
> >>Since there are a lot of inserts and updates to the data
> >>in this system we have now got a lot of really
> >>fragmentated tables but since we have no clustered
> >indexes
> >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> to
> >>solve this problem?
> >>
> >>I have searched the newsgroups (and of course "Inside
> sQL
> >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> >>answers that works. After reading a post here I tried
> >both
> >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> it
> >>hardly effects the terrible scan density (DBCC
> >SHOWCONTIG)
> >>for these tables. The only other solution I have read
> >>about is to use BCP to export and import the tables but
> >>this quite complicated solution can't really be included
> >>in our weekly maintenance job which is what we want.
> >>
> >>Please, anyone, suggestions? I can't be the only one
> with
> >>this problem..?
> >>
> >>- Allan
> >>
> >>.
> >>
> >.
> >|||Yes Tibor has it correct. That's one of the reasons I suggest that most
tables have a clustered index.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23W979hBhDHA.616@.TK2MSFTNGP11.phx.gbl...
> There's no easy way to reorg (or perhaps "compact" is a better word as the
data isn't sorted in any
> way) for a heap. Two ways I can think of:
> Create a clustered index and drop it.
> Export/import of the data.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
> news:03d101c38386$33166d70$a301280a@.phx.gbl...
> > As far as I have understood (and tested) neither DBCC
> > DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> > have clustered indexes on these tables the data is not
> > stored on the leaf level of the index but in a heap. What
> > I want to know is how to defragment this heap..
> >
> > >--Original Message--
> > >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> > >your statistics manually after the reindex.
> > >
> > >Greg
> > >
> > >>--Original Message--
> > >>Hi!
> > >>
> > >>We have several SQL Servers in a system that replicates
> > >>information in non-realtime between them using MSMQ and
> > >>Biztalk and to guarantee uniqueness it uses GUID:s as
> > >>primary and foreign keys. When planning this solution we
> > >>were recommended by Microsoft to use only non-clustered
> > >>indexes on these tables.
> > >>
> > >>Since there are a lot of inserts and updates to the data
> > >>in this system we have now got a lot of really
> > >>fragmentated tables but since we have no clustered
> > >indexes
> > >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> > to
> > >>solve this problem?
> > >>
> > >>I have searched the newsgroups (and of course "Inside
> > sQL
> > >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> > >>answers that works. After reading a post here I tried
> > >both
> > >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> > it
> > >>hardly effects the terrible scan density (DBCC
> > >SHOWCONTIG)
> > >>for these tables. The only other solution I have read
> > >>about is to use BCP to export and import the tables but
> > >>this quite complicated solution can't really be included
> > >>in our weekly maintenance job which is what we want.
> > >>
> > >>Please, anyone, suggestions? I can't be the only one
> > with
> > >>this problem..?
> > >>
> > >>- Allan
> > >>
> > >>.
> > >>
> > >.
> > >
>
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:
> 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:
> 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.7
93
> 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_INDEX
ES
> (and some more columns)
>
Less than 1000 pages, don't worry about them.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||thank you!
Ο χρ?στη? "Tracy McKibben" _γγραψε:
> nikolakg wrote:
> Less than 1000 pages, don't worry about them.
> http://www.microsoft.com/technet/pr...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:
> 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:
> 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.7
93
> 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_INDEX
ES
> (and some more columns)
>
Less than 1000 pages, don't worry about them.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||thank you!
Ο χρ?στη? "Tracy McKibben" _γγραψε:
> nikolakg wrote:
> Less than 1000 pages, don't worry about them.
> http://www.microsoft.com/technet/pr...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 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
Defrag data disk
Hi,
The disks on which my databases are stored is heavily
fragmented. This because of wrong database grow settings.
Can I use the Windows system tool: Disk Defragmenter to
defragment my disk or will SQL not like this?
Thanks,
Jeroen.I believe you can, so long as the block size <= 4KB.
You will need to stop sql server if you want the data files defragemented
"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
> Thanks,
> Jeroen.|||"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
>
Also your disks may appear more fragmented than they really are.
If you have a few large files that are in 2 fragments, you disk can report
as being 90% fragmented without this being a big deal.
David
The disks on which my databases are stored is heavily
fragmented. This because of wrong database grow settings.
Can I use the Windows system tool: Disk Defragmenter to
defragment my disk or will SQL not like this?
Thanks,
Jeroen.I believe you can, so long as the block size <= 4KB.
You will need to stop sql server if you want the data files defragemented
"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
> Thanks,
> Jeroen.|||"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
>
Also your disks may appear more fragmented than they really are.
If you have a few large files that are in 2 fragments, you disk can report
as being 90% fragmented without this being a big deal.
David
Friday, March 9, 2012
Defining Alert for System Errors
Hi,
I want to define alert for system error messages but it seems that we cannot
alter these messages like SQL Server 2000 to be logged in Windows events.
For example error #208, is it possible?
Thanks in advance,
LeilaNo, we cannot change whether system errors are written to eventlog or not in
2005, I'm afraid. I
guess you have to look for some external utility which monitors the eventlog
. The good news is that
the messages in EventLog now has the same Event number as the SQL Server err
or number (which makes
it easier to write code that reads the eventlog and acts on certain errors).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> Hi,
> I want to define alert for system error messages but it seems that we cann
ot alter these messages
> like SQL Server 2000 to be logged in Windows events. For example error #20
8, is it possible?
> Thanks in advance,
> Leila
>[/vbcol]|||Whay if an application is upgraded from SQL Server 2000 to 2005! The
backward compatibility is missed!? It was very easy to define this alert in
previous version..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> No, we cannot change whether system errors are written to eventlog or not
> in 2005, I'm afraid. I guess you have to look for some external utility
> which monitors the eventlog. The good news is that the messages in
> EventLog now has the same Event number as the SQL Server error number
> (which makes it easier to write code that reads the eventlog and acts on
> certain errors).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
>|||I didn't check what happens with the "always log to eventlog" for system err
or on an upgraded SQL
Server, but most probably, this setting will not be upgraded (since the syst
em part of sys.messages
isn't really a table anymore), so that will be lost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:ee5LeBmOHHA.324@.TK2MSFTNGP06.phx.gbl...[vb
col=seagreen]
> Whay if an application is upgraded from SQL Server 2000 to 2005! The backw
ard compatibility is
> missed!? It was very easy to define this alert in previous version..
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
>[/vbcol]
I want to define alert for system error messages but it seems that we cannot
alter these messages like SQL Server 2000 to be logged in Windows events.
For example error #208, is it possible?
Thanks in advance,
LeilaNo, we cannot change whether system errors are written to eventlog or not in
2005, I'm afraid. I
guess you have to look for some external utility which monitors the eventlog
. The good news is that
the messages in EventLog now has the same Event number as the SQL Server err
or number (which makes
it easier to write code that reads the eventlog and acts on certain errors).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> Hi,
> I want to define alert for system error messages but it seems that we cann
ot alter these messages
> like SQL Server 2000 to be logged in Windows events. For example error #20
8, is it possible?
> Thanks in advance,
> Leila
>[/vbcol]|||Whay if an application is upgraded from SQL Server 2000 to 2005! The
backward compatibility is missed!? It was very easy to define this alert in
previous version..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> No, we cannot change whether system errors are written to eventlog or not
> in 2005, I'm afraid. I guess you have to look for some external utility
> which monitors the eventlog. The good news is that the messages in
> EventLog now has the same Event number as the SQL Server error number
> (which makes it easier to write code that reads the eventlog and acts on
> certain errors).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
>|||I didn't check what happens with the "always log to eventlog" for system err
or on an upgraded SQL
Server, but most probably, this setting will not be upgraded (since the syst
em part of sys.messages
isn't really a table anymore), so that will be lost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:ee5LeBmOHHA.324@.TK2MSFTNGP06.phx.gbl...[vb
col=seagreen]
> Whay if an application is upgraded from SQL Server 2000 to 2005! The backw
ard compatibility is
> missed!? It was very easy to define this alert in previous version..
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
>[/vbcol]
Defining Alert for System Errors
Hi,
I want to define alert for system error messages but it seems that we cannot
alter these messages like SQL Server 2000 to be logged in Windows events.
For example error #208, is it possible?
Thanks in advance,
Leila
Whay if an application is upgraded from SQL Server 2000 to 2005! The
backward compatibility is missed!? It was very easy to define this alert in
previous version..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> No, we cannot change whether system errors are written to eventlog or not
> in 2005, I'm afraid. I guess you have to look for some external utility
> which monitors the eventlog. The good news is that the messages in
> EventLog now has the same Event number as the SQL Server error number
> (which makes it easier to write code that reads the eventlog and acts on
> certain errors).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
>
I want to define alert for system error messages but it seems that we cannot
alter these messages like SQL Server 2000 to be logged in Windows events.
For example error #208, is it possible?
Thanks in advance,
Leila
Whay if an application is upgraded from SQL Server 2000 to 2005! The
backward compatibility is missed!? It was very easy to define this alert in
previous version..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> No, we cannot change whether system errors are written to eventlog or not
> in 2005, I'm afraid. I guess you have to look for some external utility
> which monitors the eventlog. The good news is that the messages in
> EventLog now has the same Event number as the SQL Server error number
> (which makes it easier to write code that reads the eventlog and acts on
> certain errors).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
>
Wednesday, March 7, 2012
Defining Alert for System Errors
Hi,
I want to define alert for system error messages but it seems that we cannot
alter these messages like SQL Server 2000 to be logged in Windows events.
For example error #208, is it possible?
Thanks in advance,
LeilaNo, we cannot change whether system errors are written to eventlog or not in 2005, I'm afraid. I
guess you have to look for some external utility which monitors the eventlog. The good news is that
the messages in EventLog now has the same Event number as the SQL Server error number (which makes
it easier to write code that reads the eventlog and acts on certain errors).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want to define alert for system error messages but it seems that we cannot alter these messages
> like SQL Server 2000 to be logged in Windows events. For example error #208, is it possible?
> Thanks in advance,
> Leila
>|||Whay if an application is upgraded from SQL Server 2000 to 2005! The
backward compatibility is missed!? It was very easy to define this alert in
previous version..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> No, we cannot change whether system errors are written to eventlog or not
> in 2005, I'm afraid. I guess you have to look for some external utility
> which monitors the eventlog. The good news is that the messages in
> EventLog now has the same Event number as the SQL Server error number
> (which makes it easier to write code that reads the eventlog and acts on
> certain errors).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I want to define alert for system error messages but it seems that we
>> cannot alter these messages like SQL Server 2000 to be logged in Windows
>> events. For example error #208, is it possible?
>> Thanks in advance,
>> Leila
>|||I didn't check what happens with the "always log to eventlog" for system error on an upgraded SQL
Server, but most probably, this setting will not be upgraded (since the system part of sys.messages
isn't really a table anymore), so that will be lost.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:ee5LeBmOHHA.324@.TK2MSFTNGP06.phx.gbl...
> Whay if an application is upgraded from SQL Server 2000 to 2005! The backward compatibility is
> missed!? It was very easy to define this alert in previous version..
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
>> No, we cannot change whether system errors are written to eventlog or not in 2005, I'm afraid. I
>> guess you have to look for some external utility which monitors the eventlog. The good news is
>> that the messages in EventLog now has the same Event number as the SQL Server error number (which
>> makes it easier to write code that reads the eventlog and acts on certain errors).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I want to define alert for system error messages but it seems that we cannot alter these
>> messages like SQL Server 2000 to be logged in Windows events. For example error #208, is it
>> possible?
>> Thanks in advance,
>> Leila
>>
>
I want to define alert for system error messages but it seems that we cannot
alter these messages like SQL Server 2000 to be logged in Windows events.
For example error #208, is it possible?
Thanks in advance,
LeilaNo, we cannot change whether system errors are written to eventlog or not in 2005, I'm afraid. I
guess you have to look for some external utility which monitors the eventlog. The good news is that
the messages in EventLog now has the same Event number as the SQL Server error number (which makes
it easier to write code that reads the eventlog and acts on certain errors).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want to define alert for system error messages but it seems that we cannot alter these messages
> like SQL Server 2000 to be logged in Windows events. For example error #208, is it possible?
> Thanks in advance,
> Leila
>|||Whay if an application is upgraded from SQL Server 2000 to 2005! The
backward compatibility is missed!? It was very easy to define this alert in
previous version..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> No, we cannot change whether system errors are written to eventlog or not
> in 2005, I'm afraid. I guess you have to look for some external utility
> which monitors the eventlog. The good news is that the messages in
> EventLog now has the same Event number as the SQL Server error number
> (which makes it easier to write code that reads the eventlog and acts on
> certain errors).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I want to define alert for system error messages but it seems that we
>> cannot alter these messages like SQL Server 2000 to be logged in Windows
>> events. For example error #208, is it possible?
>> Thanks in advance,
>> Leila
>|||I didn't check what happens with the "always log to eventlog" for system error on an upgraded SQL
Server, but most probably, this setting will not be upgraded (since the system part of sys.messages
isn't really a table anymore), so that will be lost.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:ee5LeBmOHHA.324@.TK2MSFTNGP06.phx.gbl...
> Whay if an application is upgraded from SQL Server 2000 to 2005! The backward compatibility is
> missed!? It was very easy to define this alert in previous version..
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eEExMvlOHHA.4244@.TK2MSFTNGP04.phx.gbl...
>> No, we cannot change whether system errors are written to eventlog or not in 2005, I'm afraid. I
>> guess you have to look for some external utility which monitors the eventlog. The good news is
>> that the messages in EventLog now has the same Event number as the SQL Server error number (which
>> makes it easier to write code that reads the eventlog and acts on certain errors).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message news:Ov$2kalOHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I want to define alert for system error messages but it seems that we cannot alter these
>> messages like SQL Server 2000 to be logged in Windows events. For example error #208, is it
>> possible?
>> Thanks in advance,
>> Leila
>>
>
Friday, February 24, 2012
Default value of a GUID field?!
Fix your system clock, Leon!
Howse ya do dat?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uFe4J$Q%23GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Fix your system clock, Leon!
>
Howse ya do dat?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uFe4J$Q%23GHA.1172@.TK2MSFTNGP03.phx.gbl...
> Fix your system clock, Leon!
>
Friday, February 17, 2012
default vales
Hi,
How can I find table name, column name of all columns having default value
''
Is there any system table store the defalt values.
ThanksTry,
select table_name, column_default
from information_schema.columns
where column_default = '('''')'
HTH
Prasad
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>|||Include the column name as below,
Try,
select table_name, column_name, column_default
from information_schema.columns
where column_default = '('''')'
HTH
Prasad
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>|||Binu,
SELECT column_name,table_name,column_default
FROM information_schema.columns
WHERE column_default = '('''')'
OR column_default = '(space(0))'
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>
How can I find table name, column name of all columns having default value
''
Is there any system table store the defalt values.
ThanksTry,
select table_name, column_default
from information_schema.columns
where column_default = '('''')'
HTH
Prasad
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>|||Include the column name as below,
Try,
select table_name, column_name, column_default
from information_schema.columns
where column_default = '('''')'
HTH
Prasad
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>|||Binu,
SELECT column_name,table_name,column_default
FROM information_schema.columns
WHERE column_default = '('''')'
OR column_default = '(space(0))'
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>
Default SqlServer database creation path
Hi,
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.
Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.
Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS 
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT
SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]
|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||
The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.
The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:
HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot
and
HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath
The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...
If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.
You can find the registry key for your SQL 2005 instance here:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>
...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>
Now go to:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup
to get SQLDataRoot and SQLPath...
then go to:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer
to get DefaultData and DefaultLog...
Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.
|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:
master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT
But fails for
master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures',
N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT
it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths
Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1
Subscribe to:
Posts (Atom)