When i tried to create column, getting error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

When i tried to create column, getting error

Author

Topic

Fancy
Starting Member

8 Posts

Posted - 2008-09-14 : 16:24:00

Hi all,
first of all, sorry for my bad English.
I tried to create a column on a table(tblEntry) then i got a error. And column didnt created.

it says:

'tblEntry' table
- Unable to modify table.  
Possible schema corruption. Run DBCC CHECKCATALOG.
A severe error occurred on the current command.  The results, if any, should be discarded.

When i tried the dbcc checkdb command on database, it gives that error:

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (rule_object_id=128) of row (object_id=1045578763,column_id=18) in sys.columns does not have a matching row (object_id=128) in sys.objects.

from that log:

DBCC results for '3harf'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (rule_object_id=128) of row (object_id=1045578763,column_id=18) in sys.columns does not have a matching row (object_id=128) in sys.objects.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1001 rows in 8 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 123 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 148 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 1001 rows in 9 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 123 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 148 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 15 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 157 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 275 rows in 5 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 1035 rows in 16 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 2 rows in 2 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 379 rows in 9 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 603 rows in 6 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 518 rows in 119 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 6 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 128 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 304 rows in 2 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'tblSeyirDefteri'.
There are 45 rows in 15 pages for object "tblSeyirDefteri".
DBCC results for 'tblUserCV'.
There are 609 rows in 12 pages for object "tblUserCV".
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object "dtproperties".
DBCC results for 'tblAyarlar'.
There are 1 rows in 1 pages for object "tblAyarlar".
DBCC results for 'Theme'.
There are 2 rows in 1 pages for object "Theme".
DBCC results for 'tblOylar'.
There are 108923 rows in 625 pages for object "tblOylar".
DBCC results for 'tblUyelikDavetiye'.
There are 8 rows in 1 pages for object "tblUyelikDavetiye".
DBCC results for 'tblCezalar'.
There are 0 rows in 0 pages for object "tblCezalar".
DBCC results for 'tblGonderilmisMesajlar'.
There are 102436 rows in 1885 pages for object "tblGonderilmisMesajlar".
DBCC results for 'tblistatistik'.
There are 670 rows in 9 pages for object "tblistatistik".
DBCC results for 'tblGelenKutusu'.
There are 96628 rows in 1711 pages for object "tblGelenKutusu".
DBCC results for 'UserStat'.
There are 1 rows in 1 pages for object "UserStat".
DBCC results for 'tblBaslikEtikerleri'.
There are 87 rows in 1 pages for object "tblBaslikEtikerleri".
DBCC results for 'tblImhaEntry'.
There are 69 rows in 1 pages for object "tblImhaEntry".
DBCC results for 'tblitirazlar'.
There are 78 rows in 2 pages for object "tblitirazlar".
DBCC results for 'tblBasliklarS'.
There are 87 rows in 1 pages for object "tblBasliklarS".
DBCC results for 'tblLamers'.
There are 0 rows in 0 pages for object "tblLamers".
DBCC results for 'tblFavoriBasliklar'.
There are 2295 rows in 101 pages for object "tblFavoriBasliklar".
DBCC results for 'tblBugunSay'.
There are 10220 rows in 101 pages for object "tblBugunSay".
DBCC results for 'Users'.
There are 1 rows in 1 pages for object "Users".
DBCC results for 'tblUkte'.
There are 403 rows in 87 pages for object "tblUkte".
DBCC results for 'tblDuyuru'.
There are 7 rows in 1 pages for object "tblDuyuru".
DBCC results for 'tblEntry'.
There are 578070 rows in 13684 pages for object "tblEntry".
DBCC results for 'tblEntryS'.
There are 13370 rows in 269 pages for object "tblEntryS".
DBCC results for 'Error'.
There are 28 rows in 5 pages for object "Error".
DBCC results for 'tblKanki'.
There are 1398 rows in 17 pages for object "tblKanki".
DBCC results for 'tblThemalar'.
There are 21 rows in 1 pages for object "tblThemalar".
DBCC results for 'DeletedTitle'.
There are 4 rows in 1 pages for object "DeletedTitle".
DBCC results for 'tblRates'.
There are 320832 rows in 7362 pages for object "tblRates".
DBCC results for 'tblIDKaydi'.
There are 1 rows in 1 pages for object "tblIDKaydi".
DBCC results for 'tblUserKaydiS'.
There are 0 rows in 0 pages for object "tblUserKaydiS".
DBCC results for 'tblUserKaydi'.
There are 8560 rows in 924 pages for object "tblUserKaydi".
DBCC results for 'tblOnlineZiyaretci'.
There are 226805 rows in 1184 pages for object "tblOnlineZiyaretci".
DBCC results for 'tblUkteOK'.
There are 228 rows in 16 pages for object "tblUkteOK".
DBCC results for 'tblBasliklar'.
There are 131439 rows in 3093 pages for object "tblBasliklar".
DBCC results for 'tblSSS'.
There are 30 rows in 1 pages for object "tblSSS".
DBCC results for 'tblUserStats'.
There are 159491 rows in 1358 pages for object "tblUserStats".
DBCC results for 'tblYanki'.
There are 33 rows in 1 pages for object "tblYanki".
DBCC results for 'tblYasak_Listesi'.
There are 39 rows in 1 pages for object "tblYasak_Listesi".
DBCC results for 'tblFormGun'.
There are 31 rows in 1 pages for object "tblFormGun".
DBCC results for 'sys.queue_messages_2059154381'.
There are 0 rows in 0 pages for object "sys.queue_messages_2059154381".
DBCC results for 'tblFormAy'.
There are 12 rows in 1 pages for object "tblFormAy".
DBCC results for 'sys.queue_messages_2091154495'.
There are 0 rows in 0 pages for object "sys.queue_messages_2091154495".
DBCC results for 'tblFormUlke'.
There are 234 rows in 1 pages for object "tblFormUlke".
DBCC results for 'tblFormYil'.
There are 174 rows in 1 pages for object "tblFormYil".
DBCC results for 'sys.queue_messages_2123154609'.
There are 0 rows in 0 pages for object "sys.queue_messages_2123154609".
CHECKDB found 0 allocation errors and 1 consistency errors in database '3harf'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

please help me

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-15 : 04:21:51

Have a look at this blog post here. It describes how this error came about and what can be done to fix it.
[url]http://www.sqlskills.com/blogs/paul/2008/06/12/TechEdDemoUsingTheSQL2005DedicatedAdminConnectionToFixMsg8992CorruptSystemTables.aspx[/url]

I'm guessing this DB was upgraded from SQL 2000? If so, probably someone sometime did a direct update of the system tables sometime in the past deleting the rule that had an ID of 128. Be very, very careful with the fix in Paul's blog. It's very easy to do more damage than you're fixing. It might also be an idea to wait for Paul Randal to see this post, as he'll be able to tell you more about this error than I can.

What you're probably going to need to do is find the column 'chk' in the system table 'sys.syscolpars', and update that to 0 where id = 1045578763. Make sure that you have a backup before you start.

You may also want to start regularly running CheckDB so that you can see corruptions very soon after they occur. The best way to fix corruptions is to restore a clean backup.

--
Gail Shaw
SQL Server MVP

Fancy
Starting Member

8 Posts

Posted - 2008-09-15 : 18:06:26

Hi Gila,
thanks for your helpful answer.
Yes, I upgraded from 2000 directly. But I has a problem with HDD at my last service provider.
I changed my provider than errors are no more.

Can you suggest your solution with complete tsql?
I'm newbie about that.

Thanks again.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-16 : 05:58:27

Is there anyone experienced in SQL you can ask? Messing around with the system tables isn't something a newbie should be trying to do. Mess up and lots of things will break

Step 1. Take a backup! (This is not an optional step)

Follow the steps Paul's blog to get the server in single user mode and a connection via the DAC. Note, you need to be the system administrator to do this and no one will be able to connect until you are done. You'll also need to be working on the server (remote desktop's fine)

Make sure that you're in the correct database (USE 3harf)
Double check that you're in the right database. (do a select name from sys.tables and make sure you're seeing the correct tables for that DB)

SELECT id, colid, name, chk from sys.syscolpars where id = 1045578763 and colid = 18

You should see the chk having a value of 128 and the name been the name of the column in question. If it doesn't look correct (the column name doesn't exist in the table tblEntry or the chk is not 128), don't go any further

begin transaction
update sys.syscolpars SET chk = 0 where id = 1045578763 and colid = 18

At this point check that only 1 row was affected. Rerun the previous select and make sure that chk is now 0. If all looks good
COMMIT TRANSACTION

You'll get a warning about the system tables having been modified. Disconnect from SQL and shut down SQL Server. You should now be able to bring it back up as a service (via the services app or using NET START)
Connect to it normally and run a checkDB (DBCC CHECKDB(<database name>) WITH NO_INFOMSGS)

--
Gail Shaw
SQL Server MVP

Fancy
Starting Member

8 Posts

Posted - 2008-09-17 : 08:07:17

Thanks a lot Gila,
It works now.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-17 : 12:00:48

Good to hear.

The CheckDB came back clean with no errors?

--
Gail Shaw
SQL Server MVP

Fancy
Starting Member

8 Posts

Posted - 2008-09-18 : 07:21:46

Yes,
and table is working now.
I added a column. :)
Now i can continue to make my project :)
Thanks