DBCC CHECKDB failed due to invalid object id

This anomaly persist due to deleting objects that are not removed from system tables properly due to some reasons which is in fact ignored in legacy SQL version (2000) however catch up by DBCC in later versions (Most probably after migration or upgrade).

Error Description:

Error 3854: [Microsoft][ODBC SQL Server Driver][SQL Server]Attribute (parent_object_id=1977058079) of row (object_id=1993058136) in sys.objects has a matching row (object_id=1977058079) in sys.objects (type=S ) that is invalid

Workarounds:

1) Ignore the errors, though database consistency check will not able to complete.

2) Export the data to new database using import/export utility.

3) Remove the anomaly from system base table, as system table (sysschobjs) is not directly accessible so DAC access is required, steps as below.

- Enable configuration (allow update and DAC)
- Restart SQL in single user mode with –m
- As both objects (1977058079, 1993058136) are in same table along with syskeys table so delete the child record 1993058136.
- delete  FROM sys.sysschobjs WHERE ID=1993058136;
- remove –m parameter and restart SQL in normal model
- disable allow update and DAC

How to find 3-party DLLs loaded in SQL Server

There are cases where heap memory get corrupted and SQL Server generally does not use heap, for this kind of case, it is possibly caused by third party DLL which did not manage heap well.

ListDlls tool is one of solution, which will help us get all loaded DLL in SQL Server, it will help us figure out if there is any 3rd-party DLLs loaded in SQL Server. You could download the tool here,

https://technet.microsoft.com/en-us/sysinternals/bb896656.aspx

And the command to use it is,

1.Please open a command prompt with run as administrator. And then switch to the folder of ListDlls.exe
2.Then run “ListDlls -v sqlservr.exe” will list all loaded DLLs of SQL Server.

SQL Server configuration manager is not opening

SQL server configuration manager relies on WMI provide. I found that the WMI provider failed to load with error 0x80080005 (Server execution failed).

I failed to rebuild wmi database.

wmiprvse /regserver     --> No entry point error

mofcomp hang

DCOM security issue or wmiprvse.exe failed to work.

Workaround:

1) Check DCOM permission.

2) Replace wmiprvse.exe with the one copied from good server.

C:\Windows\System32\wbem\wmiprvse.exe

3) Successfully rebuild the WMI database.

4) Then we re-compiled the SQL server WMI MOF file by using below command:

mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof"

5) Refer below updates if you encounter error while compiling.

After that, the SQL configuration manager opens successfully.

Updates: 

Recently encounter another issue while recompiling WMI MOF and it gave below error.

When I checked the consistency of WMI repository, it shows inconsistent.

As a workaround, I stopped the WMI service and salvage the WMI repository as below.

After that it shows me that it is consistent now, at this point recompilation of MOF works fine.

Note: This command (Winmgmt /salvagerepository) will take the content of the inconsistent repository and merge it into the rebuilt repository if it is readable.

If the above doesn’t work, then run:

Winmgmt /resetrepository

Reference:

https://blogs.technet.microsoft.com/askperf/2009/04/13/wmi-rebuilding-the-wmi-repository/

How to handle orphan transactions in SQL Server

-2 blocking means Orphaned distributed transactions, in this transaction become in-doubt and MSDTC lost control over it, these transactions may or may not be listed in MSDTC (depend upon issues occurred).



In order to find the session that is handling request for this UOW (Unit of Work), refer to below query that will lead to session id which can provide detailed information of activity. There are few ways to release the transaction, one from SQL (Use KILL <UOW> command) and another from MSDTC, right click on UOW and select resolve. I would suggest to cross verify with session_id and UOW before resolving in MSDTC.

 select distinct request_session_id from sys.dm_tran_locks where request_owner_guid='{8241D0EE-364B-4315-A293-D46F9659CAEF}’

 If MSDTC do not list any transaction we can still find with below query. Let me know if any questions.

 select request_owner_guid from sys.dm_tran_locks where request_session_id = -2