Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped
to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/pr...oads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodin...ions/books.mspx
========================================
==========
"Prasad" <ekke_nikhil@.yahoo.co.uk> bl
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by
connecting through the
dedicated administrator connection (DAC), looking at the source code from th
e view sys.databases and
then do a select directly against the physical system table that the view sy
s.databases uses.
I don't know what you want to achieve in the end, but best bet is probably t
o disregard objects that
lives in the database with id 32767.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.g
bl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005
, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of
the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea
to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databas
es ?
> I also wanted information as to how do I query the system tables, it giv
es me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Some tables are flagged as user tables even though they are shipped with the
product. The backup
history tables are such examples.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.g
bl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing a
s told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a
System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>sql
Showing posts with label object. Show all posts
Showing posts with label object. Show all posts
Thursday, March 29, 2012
Database Id prb !
Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
--
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
=================================================="Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by connecting through the
dedicated administrator connection (DAC), looking at the source code from the view sys.databases and
then do a select directly against the physical system table that the view sys.databases uses.
I don't know what you want to achieve in the end, but best bet is probably to disregard objects that
lives in the database with id 32767.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databases ?
> I also wanted information as to how do I query the system tables, it gives me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Some tables are flagged as user tables even though they are shipped with the product. The backup
history tables are such examples.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing as told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
>> any database.
>> - Breaking changes to database engine features in SQL Server 2005
>> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
>> [2] To query a system table, you will need to prefix the table with the
>> appropriate database that you are going to query. E.g.,
>> SELECT * FROM msdb..backupfile
>> - Querying the SQL Server System Catalog
>> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
>> - Querying the SQL Server System Catalog FAQ
>> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
>> --
>> Martin Poon
>> Microsoft MVP - SQL Server
>> ----
>> - SQL Server 2005 books online
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> - SQL Server 2000 books online
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> ==================================================>> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
>> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
>> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
>> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs to
>> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>>
>
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
--
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
=================================================="Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by connecting through the
dedicated administrator connection (DAC), looking at the source code from the view sys.databases and
then do a select directly against the physical system table that the view sys.databases uses.
I don't know what you want to achieve in the end, but best bet is probably to disregard objects that
lives in the database with id 32767.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databases ?
> I also wanted information as to how do I query the system tables, it gives me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Some tables are flagged as user tables even though they are shipped with the product. The backup
history tables are such examples.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing as told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
>> any database.
>> - Breaking changes to database engine features in SQL Server 2005
>> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
>> [2] To query a system table, you will need to prefix the table with the
>> appropriate database that you are going to query. E.g.,
>> SELECT * FROM msdb..backupfile
>> - Querying the SQL Server System Catalog
>> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
>> - Querying the SQL Server System Catalog FAQ
>> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
>> --
>> Martin Poon
>> Microsoft MVP - SQL Server
>> ----
>> - SQL Server 2005 books online
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> - SQL Server 2000 books online
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> ==================================================>> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
>> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
>> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
>> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs to
>> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>>
>
Sunday, March 25, 2012
Database getting slower for every BLOB object
Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
>
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
>
Database getting slower for every BLOB object
Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.
How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.
|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.
How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.
|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
Database getting slower for every BLOB object
Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
> >Hi,
> >
> >I have a table in my database storing BLOBS (images), and I have
> >noticed something strange. For every record that goes into the table,
> >the next insert takes longer to perfrom. It used to be that inserting
> >a small blob used to take about .25 of a second, but now its upto a
> >second.
> >
> >I create a test database (copy of my live) and created a test
> >application that inserted the same images over and over again and this
> >is my results:
> >
> >
> >(starting from 1750 images):
> >
> >Inserting next 250 images = 267 seconds
> >Inserting next 250 images = 284 seconds
> >Inserting next 250 images = 296 seconds
> >Inserting next 250 images = 307 seconds
> >Inserting next 250 images = 319 seconds
> >and so on.
> >
> >Is this typical of SQL Server (2005)? On Oracle, after the first 250
> >items were added, it kind of settles down and the insert time repains
> >fairly constant, no matter how many images I insert.
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
> >Hi,
> >
> >I have a table in my database storing BLOBS (images), and I have
> >noticed something strange. For every record that goes into the table,
> >the next insert takes longer to perfrom. It used to be that inserting
> >a small blob used to take about .25 of a second, but now its upto a
> >second.
> >
> >I create a test database (copy of my live) and created a test
> >application that inserted the same images over and over again and this
> >is my results:
> >
> >
> >(starting from 1750 images):
> >
> >Inserting next 250 images = 267 seconds
> >Inserting next 250 images = 284 seconds
> >Inserting next 250 images = 296 seconds
> >Inserting next 250 images = 307 seconds
> >Inserting next 250 images = 319 seconds
> >and so on.
> >
> >Is this typical of SQL Server (2005)? On Oracle, after the first 250
> >items were added, it kind of settles down and the insert time repains
> >fairly constant, no matter how many images I insert.
Sunday, February 19, 2012
Database Design - Boolean Fields
I am designing a table where the object(s) that the table represents could have hundreds of boolean attributes.
Which method of design would you chose for this scenario:
- Keep the booleans in the original object's table, potential for hundreds of nulls in a rowCreate a 2 more tables, one that has the boolean value names & ID. Another that relates an object (in original table) to a boolean value name/ID. No nulls, lots of joining
So second method would probably normalize it, but I would suffer a performance cost, whereas the 1st method would be the easiest/quickest for joins but tons of null records.
Thanks
Ben
Can you post some sample data so I can "see" what you mean..
|||For example, say I have a table of homes. The homes table will have general attributes that all home objects share (address, sq ft, price) but then there are optional boolean fields that are attributes of homes (Is TwoStory, HasGasStove, HasAlarmSystem).
So would you store these boolean fields in the homes table, or create a table of attributes and a relationship table?
The first choice would have the potential for many null values in the table for each record. Most likely this table would not be normalized.
The second option would normalize the table, but suffer from the performance penalty of the joining 3 tables (tbl_homes, tbl_homeattributes, tbl_relationship_homes_attributes) to get the information. There would not be any null values though.
This situation could apply to any object that has boolean attributes: clothes, cars, computers, buildings.
Thanks
Ben
I would create an Attributes lookup table with an AttributeId mapped to an attribute
AttribId AttribName
---------
1 IsTwoStory
2 HasGasStove
3 HasAlarmSystem
Then there would be a cross-reference table with a Home mapped to all its atributes.
HomeId AttribId
------
1 1
1 2
2 1
2 2
2 3
3 1
this way, if any new attributes get added in future you just add them to the attributes table and if there are homes that match that attribute you add them to the cross ref table. Similarly if existing attributes get removed or if a home loses some attributes you just update the cross ref table appropriately. This approach will give you a properly normalized structure with best scalability.
Tuesday, February 14, 2012
Database Corruption (Please help!)
Hi,
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
Any help would be greatly appreciated.
Leila
Leila
First of all , go to the ERROR.LOG and see what is going on?
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>
|||I found this link but I don't know how to drop the index!
http://support.microsoft.com/kb/106122
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Leila
> First of all , go to the ERROR.LOG and see what is going on?
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>
|||Leila
There are pretty good examples telling how to do that
"Leila" <Leilas@.hotpop.com> wrote in message
news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I found this link but I don't know how to drop the index!
> http://support.microsoft.com/kb/106122
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>
|||But I didn't find any example about dropping an index in this article
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Leila
> There are pretty good examples telling how to do that
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>
|||It does not drop the index, it fixes the index ( perhaps by droping it)
:-)))) Do you have a good backup of the database?
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
> But I didn't find any example about dropping an index in this article
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>
|||We discovered this problem a little late. This exists in the database since
10 days ago. If we restore the backup before the corruption, 10 days of
users work will lost!
That article mentions that you can drop the index of system tables but I
couldn't find anything!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eDZm7edIHHA.3424@.TK2MSFTNGP02.phx.gbl...
> It does not drop the index, it fixes the index ( perhaps by droping it)
> :-)))) Do you have a good backup of the database?
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||This option does not help
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> You might be able to do it if you sp_configure 'allow updates'. Not sure,
> though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
|||Leila (Leilas@.hotpop.com) writes:
> I created a nonclustered index on sysobjects table.
Uh-uh, never do that again, promise? :-)
> After that, when I create a new object and sysobjects is modified, the
> database does not work properly. I used DBCC CHECKDB to repair it. It
> works but as soon as a new object is created, again database get
> corrupted (CHECKDB reports error). I cannot drop this index with DROP
> INDEX command. I'm wondered if an index on system table cannot be
> dropped, why it can be created? If it is allowed to create index, why it
> corrupts the database?
It seems that you need to enable "Allow updates" to create an index on a
system table. With this setting all bets are off anyway.
I would recommend that you create a new database from scripts and bulk
data over.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
Any help would be greatly appreciated.
Leila
Leila
First of all , go to the ERROR.LOG and see what is going on?
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>
|||I found this link but I don't know how to drop the index!
http://support.microsoft.com/kb/106122
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Leila
> First of all , go to the ERROR.LOG and see what is going on?
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>
|||Leila
There are pretty good examples telling how to do that
"Leila" <Leilas@.hotpop.com> wrote in message
news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I found this link but I don't know how to drop the index!
> http://support.microsoft.com/kb/106122
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>
|||But I didn't find any example about dropping an index in this article
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Leila
> There are pretty good examples telling how to do that
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>
|||It does not drop the index, it fixes the index ( perhaps by droping it)
:-)))) Do you have a good backup of the database?
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
> But I didn't find any example about dropping an index in this article
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>
|||We discovered this problem a little late. This exists in the database since
10 days ago. If we restore the backup before the corruption, 10 days of
users work will lost!
That article mentions that you can drop the index of system tables but I
couldn't find anything!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eDZm7edIHHA.3424@.TK2MSFTNGP02.phx.gbl...
> It does not drop the index, it fixes the index ( perhaps by droping it)
> :-)))) Do you have a good backup of the database?
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||This option does not help
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> You might be able to do it if you sp_configure 'allow updates'. Not sure,
> though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
|||Leila (Leilas@.hotpop.com) writes:
> I created a nonclustered index on sysobjects table.
Uh-uh, never do that again, promise? :-)
> After that, when I create a new object and sysobjects is modified, the
> database does not work properly. I used DBCC CHECKDB to repair it. It
> works but as soon as a new object is created, again database get
> corrupted (CHECKDB reports error). I cannot drop this index with DROP
> INDEX command. I'm wondered if an index on system table cannot be
> dropped, why it can be created? If it is allowed to create index, why it
> corrupts the database?
It seems that you need to enable "Allow updates" to create an index on a
system table. With this setting all bets are off anyway.
I would recommend that you create a new database from scripts and bulk
data over.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com
Database Corruption (Please help!)
Hi,
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
Any help would be greatly appreciated.
LeilaLeila
First of all , go to the ERROR.LOG and see what is going on?
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||I found this link but I don't know how to drop the index!
http://support.microsoft.com/kb/106122
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Leila
> First of all , go to the ERROR.LOG and see what is going on?
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>|||Leila
There are pretty good examples telling how to do that
"Leila" <Leilas@.hotpop.com> wrote in message
news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I found this link but I don't know how to drop the index!
> http://support.microsoft.com/kb/106122
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>|||But I didn't find any example about dropping an index in this article
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Leila
> There are pretty good examples telling how to do that
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>|||It does not drop the index, it fixes the index ( perhaps by droping it)
:-)))) Do you have a good backup of the database?
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
> But I didn't find any example about dropping an index in this article
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>|||We discovered this problem a little late. This exists in the database since
10 days ago. If we restore the backup before the corruption, 10 days of
users work will lost!
That article mentions that you can drop the index of system tables but I
couldn't find anything!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eDZm7edIHHA.3424@.TK2MSFTNGP02.phx.gbl...
> It does not drop the index, it fixes the index ( perhaps by droping it)
> :-)))) Do you have a good backup of the database?
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
>|||You might be able to do it if you sp_configure 'allow updates'. Not sure, th
ough.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||This option does not help
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> You might be able to do it if you sp_configure 'allow updates'. Not sure,
> though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...|||Leila (Leilas@.hotpop.com) writes:
> I created a nonclustered index on sysobjects table.
Uh-uh, never do that again, promise? :-)
> After that, when I create a new object and sysobjects is modified, the
> database does not work properly. I used DBCC CHECKDB to repair it. It
> works but as soon as a new object is created, again database get
> corrupted (CHECKDB reports error). I cannot drop this index with DROP
> INDEX command. I'm wondered if an index on system table cannot be
> dropped, why it can be created? If it is allowed to create index, why it
> corrupts the database?
It seems that you need to enable "Allow updates" to create an index on a
system table. With this setting all bets are off anyway.
I would recommend that you create a new database from scripts and bulk
data over.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
Any help would be greatly appreciated.
LeilaLeila
First of all , go to the ERROR.LOG and see what is going on?
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||I found this link but I don't know how to drop the index!
http://support.microsoft.com/kb/106122
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Leila
> First of all , go to the ERROR.LOG and see what is going on?
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>|||Leila
There are pretty good examples telling how to do that
"Leila" <Leilas@.hotpop.com> wrote in message
news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I found this link but I don't know how to drop the index!
> http://support.microsoft.com/kb/106122
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>|||But I didn't find any example about dropping an index in this article
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Leila
> There are pretty good examples telling how to do that
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>|||It does not drop the index, it fixes the index ( perhaps by droping it)
:-)))) Do you have a good backup of the database?
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
> But I didn't find any example about dropping an index in this article
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>|||We discovered this problem a little late. This exists in the database since
10 days ago. If we restore the backup before the corruption, 10 days of
users work will lost!
That article mentions that you can drop the index of system tables but I
couldn't find anything!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eDZm7edIHHA.3424@.TK2MSFTNGP02.phx.gbl...
> It does not drop the index, it fixes the index ( perhaps by droping it)
> :-)))) Do you have a good backup of the database?
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
>|||You might be able to do it if you sp_configure 'allow updates'. Not sure, th
ough.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||This option does not help
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> You might be able to do it if you sp_configure 'allow updates'. Not sure,
> though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...|||Leila (Leilas@.hotpop.com) writes:
> I created a nonclustered index on sysobjects table.
Uh-uh, never do that again, promise? :-)
> After that, when I create a new object and sysobjects is modified, the
> database does not work properly. I used DBCC CHECKDB to repair it. It
> works but as soon as a new object is created, again database get
> corrupted (CHECKDB reports error). I cannot drop this index with DROP
> INDEX command. I'm wondered if an index on system table cannot be
> dropped, why it can be created? If it is allowed to create index, why it
> corrupts the database?
It seems that you need to enable "Allow updates" to create an index on a
system table. With this setting all bets are off anyway.
I would recommend that you create a new database from scripts and bulk
data over.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Database Corruption (Please help!)
Hi,
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
I found this link:
http://support.microsoft.com/kb/106122
Although it mentions SP_FixIndex can drop such indexes but there are no description about it.
Any help would be greatly appreciated.
LeilaToday's lesson: "Don't f*** with system tables."|||Any solution?!..|||You may be able to drop the index if you run the SET OPTION command to allow changed to system objects.
Make sure you execute the command to disallow changes when you are done!|||It doesn't allow to drop index from system tables|||Did you try the SET OPTION command?
Otherwise, create a new blank database and copy your production tables to it, and then drop the old database with its corrupted system table.
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
I found this link:
http://support.microsoft.com/kb/106122
Although it mentions SP_FixIndex can drop such indexes but there are no description about it.
Any help would be greatly appreciated.
LeilaToday's lesson: "Don't f*** with system tables."|||Any solution?!..|||You may be able to drop the index if you run the SET OPTION command to allow changed to system objects.
Make sure you execute the command to disallow changes when you are done!|||It doesn't allow to drop index from system tables|||Did you try the SET OPTION command?
Otherwise, create a new blank database and copy your production tables to it, and then drop the old database with its corrupted system table.
Database Corruption (Please help!)
Hi,
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
Any help would be greatly appreciated.
LeilaLeila
First of all , go to the ERROR.LOG and see what is going on?
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||I found this link but I don't know how to drop the index!
http://support.microsoft.com/kb/106122
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Leila
> First of all , go to the ERROR.LOG and see what is going on?
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can be
>> created? If it is allowed to create index, why it corrupts the database?
>> Any help would be greatly appreciated.
>> Leila
>|||Leila
There are pretty good examples telling how to do that
"Leila" <Leilas@.hotpop.com> wrote in message
news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I found this link but I don't know how to drop the index!
> http://support.microsoft.com/kb/106122
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>|||But I didn't find any example about dropping an index in this article
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Leila
> There are pretty good examples telling how to do that
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon
>> as a new object is created, again database get corrupted (CHECKDB
>> reports error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>|||It does not drop the index, it fixes the index ( perhaps by droping it)
:-)))) Do you have a good backup of the database?
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
> But I didn't find any example about dropping an index in this article
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> There are pretty good examples telling how to do that
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon
>> as a new object is created, again database get corrupted (CHECKDB
>> reports error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>>
>|||We discovered this problem a little late. This exists in the database since
10 days ago. If we restore the backup before the corruption, 10 days of
users work will lost!
That article mentions that you can drop the index of system tables but I
couldn't find anything!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eDZm7edIHHA.3424@.TK2MSFTNGP02.phx.gbl...
> It does not drop the index, it fixes the index ( perhaps by droping it)
> :-)))) Do you have a good backup of the database?
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
>> But I didn't find any example about dropping an index in this article
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> There are pretty good examples telling how to do that
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when
>> I create a new object and sysobjects is modified, the database does
>> not work properly. I used DBCC CHECKDB to repair it. It works but as
>> soon as a new object is created, again database get corrupted
>> (CHECKDB reports error). I cannot drop this index with DROP INDEX
>> command.
>> I'm wondered if an index on system table cannot be dropped, why it
>> can be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>>
>>
>|||You might be able to do it if you sp_configure 'allow updates'. Not sure, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||This option does not help :(
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...
> You might be able to do it if you sp_configure 'allow updates'. Not sure,
> though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can be
>> created? If it is allowed to create index, why it corrupts the database?
>> Any help would be greatly appreciated.
>> Leila|||Leila (Leilas@.hotpop.com) writes:
> I created a nonclustered index on sysobjects table.
Uh-uh, never do that again, promise? :-)
> After that, when I create a new object and sysobjects is modified, the
> database does not work properly. I used DBCC CHECKDB to repair it. It
> works but as soon as a new object is created, again database get
> corrupted (CHECKDB reports error). I cannot drop this index with DROP
> INDEX command. I'm wondered if an index on system table cannot be
> dropped, why it can be created? If it is allowed to create index, why it
> corrupts the database?
It seems that you need to enable "Allow updates" to create an index on a
system table. With this setting all bets are off anyway.
I would recommend that you create a new database from scripts and bulk
data over.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
Any help would be greatly appreciated.
LeilaLeila
First of all , go to the ERROR.LOG and see what is going on?
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||I found this link but I don't know how to drop the index!
http://support.microsoft.com/kb/106122
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Leila
> First of all , go to the ERROR.LOG and see what is going on?
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can be
>> created? If it is allowed to create index, why it corrupts the database?
>> Any help would be greatly appreciated.
>> Leila
>|||Leila
There are pretty good examples telling how to do that
"Leila" <Leilas@.hotpop.com> wrote in message
news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I found this link but I don't know how to drop the index!
> http://support.microsoft.com/kb/106122
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>|||But I didn't find any example about dropping an index in this article
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Leila
> There are pretty good examples telling how to do that
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon
>> as a new object is created, again database get corrupted (CHECKDB
>> reports error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>|||It does not drop the index, it fixes the index ( perhaps by droping it)
:-)))) Do you have a good backup of the database?
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
> But I didn't find any example about dropping an index in this article
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> There are pretty good examples telling how to do that
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon
>> as a new object is created, again database get corrupted (CHECKDB
>> reports error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>>
>|||We discovered this problem a little late. This exists in the database since
10 days ago. If we restore the backup before the corruption, 10 days of
users work will lost!
That article mentions that you can drop the index of system tables but I
couldn't find anything!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eDZm7edIHHA.3424@.TK2MSFTNGP02.phx.gbl...
> It does not drop the index, it fixes the index ( perhaps by droping it)
> :-)))) Do you have a good backup of the database?
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
>> But I didn't find any example about dropping an index in this article
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> There are pretty good examples telling how to do that
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when
>> I create a new object and sysobjects is modified, the database does
>> not work properly. I used DBCC CHECKDB to repair it. It works but as
>> soon as a new object is created, again database get corrupted
>> (CHECKDB reports error). I cannot drop this index with DROP INDEX
>> command.
>> I'm wondered if an index on system table cannot be dropped, why it
>> can be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>>
>>
>|||You might be able to do it if you sp_configure 'allow updates'. Not sure, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||This option does not help :(
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...
> You might be able to do it if you sp_configure 'allow updates'. Not sure,
> though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can be
>> created? If it is allowed to create index, why it corrupts the database?
>> Any help would be greatly appreciated.
>> Leila|||Leila (Leilas@.hotpop.com) writes:
> I created a nonclustered index on sysobjects table.
Uh-uh, never do that again, promise? :-)
> After that, when I create a new object and sysobjects is modified, the
> database does not work properly. I used DBCC CHECKDB to repair it. It
> works but as soon as a new object is created, again database get
> corrupted (CHECKDB reports error). I cannot drop this index with DROP
> INDEX command. I'm wondered if an index on system table cannot be
> dropped, why it can be created? If it is allowed to create index, why it
> corrupts the database?
It seems that you need to enable "Allow updates" to create an index on a
system table. With this setting all bets are off anyway.
I would recommend that you create a new database from scripts and bulk
data over.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com
Subscribe to:
Posts (Atom)