Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about the repair methods for the damage of sql server system tables sysobjects, sysindexes and syscolumns, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Three important system tables in sql server database

Sysobjects: each object created in the database (constraints, default values, logs, rules, stored procedures, and so on) has a row in the table.

Sysindexes: each index and table in the database has a row in the table.

Syscolumns: each column in each table and view has a row in the table, and each parameter in the stored procedure has a row in the table.

These three tables are associated with the ID (table ID) field. Once these three system tables are corrupted, the corresponding database objects will not be accessible, which is equivalent to the "file allocation table" in DOS.

Symptoms of system table damage

Using DBCC CHECKDB to carry any parameters cannot repair the database, that is to say, DBCC CHECKDB has no effect on this account set at all.

The following operations cannot be performed:

Select * from sysobjects or select * from sysindexes or select * from syscolumns

Unable to navigate the library with SQL server DTS or other SQL scripting library tools, failed to guide the library halfway, reported that the connection was broken; failed to read and latch the page; in enterprise manager or query analyzer, some user data tables cannot be accessed.

Treatment method

Dealing with such a database is divided into two major steps:

Step 1: deal with accessible data tables

1) find out which tables are inaccessible, that is, which records in the system tables are corrupted

2) use SQL server DTS to import accessible user data tables into a new DataBase.

When importing the library, you cannot select the data tables that cannot be accessed in fold (1).

Step 2: deal with inaccessible data tables:

1) find out the ID of the error record in the system table

2) according to "ID of error record", delete the record of sysobjects, sysindexes, syscolumns table error

3) rebuild the system table record according to "ID of error record"

4) when the reconstruction is complete, if the table is accessible, import the table into the new DataBase separately using DTS.

Note: rebuilding the system table may not be successful, for example, due to the DISK I _ user O error, if it is only an error in the disk sector where the system table is saved, then rebuilding the system table can retrieve the data. If there is an error in the disk sector where the user data table is saved, even rebuilding the system table will not solve the problem. If important user data tables cannot be imported into the library, such as t_Voucher, IcStockbill, ICSale, etc., you can try it using the method in step 2.

Example:

A sql svr database with the entity name: AIS20030529181217

Check with DBCC CHECKDB, report (with DBCC CHECKDB with any parameter is the following prompt):

Server: message 8966, level 16, status 1, line 1

Failed to read and latch the page (1purl 29262) (with latch type SH). SYSOBJECTS failed.

DBCC execution completed. If DBCC outputs an error message, contact your system administrator.

Execute select * from sysobjects, the report is as follows:

Server: message 644, level 21, status 3, line 1

The index entry for RID '16243a6d19100' (index ID 0, database' AIS20030529181217') could not be found in the index page (1AIS20030529181217' 29262).

Connection interruption

But executing select * from sysindexes and select * from syscolumns is normal.

This means that only the sysobjects table is corrupted, while sysindexes and syscolumns are fine.

Processing steps:

Step 1: deal with accessible data tables

(1.1) find out which tables are not accessible

Create a new sql svr database with the database entity name AisNew. Go to query Analyzer and execute the following SQL:

-*

Use AIS20030529181217

DECLARE @ TbName VARCHAR (80)

DECLARE FindErrTable SCROLL CURSOR FOR

Select name from AisNew.dbo. Sysobjects where xtype='u' order by name

OPEN FindErrTable

FETCH FindErrTable INTO @ TbName

WHILE @ @ FETCH_STATUS-1

BEGIN

Print @ TbName

Exec ('select top 1 * from' + @ TbName)

FETCH FindErrTable INTO @ TbName

END

PRINT 'Scan Complate...'

CLOSE FindErrTable

DEALLOCATE FindErrTable

-*

The final lines of the report given by this SQL are executed as follows:

...

T_voucher

Server: message 644, level 21, status 3, line 1

The index entry for RID '161dd201a100' (index ID 0, database' AIS20030529181217') could not be found in the index page (1AIS20030529181217' 29262).

Connection interruption

From the above report, we can know that there is an error in the corresponding record of the T_voucher table in the sysobjects table, resulting in the inaccessibility of T_voucher. Modify the SQL above: mask the T _ voucher table in the recordset that declares the cursor. That is:

...

DECLARE FindErrTable SCROLL CURSOR FOR

Select name from AisNew.dbo. Sysobjects where xtype='u' and name! = 'tasking voucher'

Order by name

...

After the modification, continue to execute this SQL. Over and over again, you can constantly report which tables in sysobjects are not accessible.

(1.2) Import Library

Use the SQL DTS tool to import data tables that can be accessed in AIS20030529181217 into AisNew.

Step 2: deal with inaccessible data tables:

(2.1) find out the ID of the error record in the system table

-get the ID of TVoucher table in AIS20030529181217 in sysobjects:

SELECT id FROM AIS20030529181217.dbo.sysobjects WHERE name=' tours voucher'

= "123

(note: usually even if the sysobjects table is damaged, you can't do a select * from sysobjects query, but you can do a select ID,name from sysobjects query. If the select ID,name from sysobjects query cannot be executed, you can compare the table with the same name in the AisNew and AIS20030529181217 databases: syscolumns. According to the number of fields occupied by T_voucher in the AisNew.dbo.syscolumns table and the names of each field, the corresponding record of T_voucher is found in AIS20030529181217.dbo.syscolumns, and the ID assigned by T_voucher in the system table of AIS20030529181217 database is obtained.)

-get the ID of TVoucher table in AisNew in sysobjects:

SELECT id FROM AisNew.dbo.sysobjects WHERE name=' tours voucher'

= "456

(2.2) Delete the error record in the system table in AIS20030529181217:

DELETE AIS20030529181217.dbo.sysobjects WHERE id=123

DELETE AIS20030529181217.dbo.sysindexes WHERE id=123

DELETE AIS20030529181217.dbo.syscolumns WHERE id=123

(2.3) rebuild system table records

-rebuild the records corresponding to the T_voucher table in the AIS20030529181217.dbo.sysobjects table:

INSERT INTO AIS20030529181217.dbo.sysobjects

(name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid)

SELECT

'tincture vouchercurbages relegation 123 department xtypewrence uidrecy inforestatus repositure baseband schematrainverrect replinfore parentobjrecrdatepedcatid

FROM AisNew.dbo. Sysobjects WHERE id=456

-rebuild the records corresponding to the t_voucher table in the AIS20030529181217.dbo.sysindexes table:

INSERT INTO AIS20030529181217.dbo.sysindexes

(id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,name,statblob)

SELECT

123,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,name,statblob

FROM AisNew.dbo.sysindexes WHERE id=456

-rebuild the records corresponding to the t_voucher table in the AIS20030529181217.dbo.syscolumns table:

INSERT INTO AIS20030529181217.dbo.syscolumns

(name,id,xtype,typestat,xusertype,length,xprec,xscale,colid,xoffset,bitpos,reserved,colstat,cdefault,domain,number,colorderby,autoval,offset,collationid,language)

SELECT

Name,123,xtype,typestat,xusertype,length,xprec,xscale,colid,xoffset,bitpos,reserved,colstat,cdefault,domain,number,colORDERBY,autoval,offset,collationid,language

FROM AisNew.dbo.syscolumns WHERE id=456

(2.4) Import the t_voucher_b table into the new DataBase separately with DTS

After the above operations, the t_voucher_b table and the original t_voucher table in AIS20030529181217 share the same ID.

See if you can execute a SELECT * FROM t_voucher_b query-

If possible, then t_voucher_b must inherit all the data in the original t _ voucher table. Reuse INSERT INTO AisNew.dbo.T_voucher FROM AIS20030529181217.dbo.t_voucher_b

Or DTS imports data from t_voucher_b into AisNew.

If you execute the SELECT * FROM t_voucher_b query and still report an error, this table is completely dead.

(2.5) other "inaccessible data tables" are handled in the same way as above, repeat step (2.1) ~ (2.4).

After reading the above, do you have any further understanding of the repair methods for the damage of sysobjects, sysindexes and syscolumns in sql server system tables? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.