Integrity Check (DBCC CHECKDB) produces the error
The In-row data USED page count for object “my_table”, index ID 9, partition ID 2609137898356736, alloc unit ID 2609137898356736 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘my_table’ (object ID 1157579162).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘my_database’.
Database was just moved/upgraded from SQL Server 2000. SQL Server 2000 used to update the page space used metadata. After SQL Server 2005 the part of the migration/upgrade plan was to run DBCC UPDATEUSAGE. If you didn’t the error will appear when you run DBCC CHECKDB.
The output message above tell us that there is 1 consistency error in the database “my_database” and is a logical inconsistency. The error indicates that there are pages and row counts inaccuracies for that table and this type of errors can be fixed by running DBCC UPDATEUSAGE command.
HOW TO FIX:
Execute the DBCC UPDATEUSAGE(my_database) command in the context of your database you will see the following message indicating that were fixed the inaccuracies.
DBCC UPDATEUSAGE: Usage counts updated for table ‘my_table’ (index ‘my_table’ , partition 1): RSVD pages (In-row Data): changed from … to … pages.DBCC execution completed. If DBCC printed error messages, contact your system administrator
After the page counts have been corrected your SQL Server will be back to normal. You can run DBCC CHECKDB after and it should not return any error messages.
I come across the Error Message while trying to run a linked server query for SQL Server 2000 on SQL Server 2008 & SQL server 2008 R2.
The error stated “Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server ‘LinkedServerName’”
After little Google search I came across two possible solution to fix this error message. Apparently that is well know issue when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server.
Main Cause of this issue:
- Problem may occur because the system stored procedures were not upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4.
- May not have manually upgraded the system stored procedures after installing SQL Server 2000 SP3 or SQL Server 2000 SP4.
- Whenever MDAC is upgraded to a newer version during SQL Server SP3 upgrade, one needs to run INSTCAT.SQL on all SQL Servers to which it connects to, including the Server that is being upgraded. I don’t think I ever done this.
Possible resolution Solution 1:
- Log on to the computer (SQL server 2000 server) by using a Windows account, which is a member of the SQL Server sysadmin fixed server role.
- Back up the master database
- Click Run, type cmd.exe, and then click OK.
- At the command prompt, type one of the following commands, and then press ENTER
osql -E -S <LinkedServerName> -i <Location>\instcat.sql (For windows account with default SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName > -i <Location>\instcat.sql (For SQL account with default SQL server) OR osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql (For windows account with Named Instance of SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName >\<InstanceName> -i <Location>\instcat.sql(For SQL account with Named Instance of SQL server)
- Test to see that, you can run all distributed queries
Possible resolution Solution 2:
- Create the following stored procedure on your linked SQL Server 2000 instance
CREATE PROCEDURE sp_tables_info_rowset_64
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
2. GRANT EXECUTE the procedure to the Public in the MASTER database
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC
I actually tried both suggested resolutions, however only Solution 2 successfully worked for me