Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Solution – Getting Error while running rebuild index on CRM database – Unable to process object ID <ID> (object <Name> because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references."

Posted by blakhani on February 11, 2015


While rebuilding all indexes on a CRM database, one of my friend reported below error.

Msg 2547, Level 16, State 1, Line 15
Unable to process object ID 2007014231 (object "AttributeTypes") because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references."

First let’s understand the meaning of the error. The error message clearly says that error is due to the fact that we are running DBCC command for a synonym which is not possible. we need to run that on base object. Here is the simple repro of the error.

use tempdb
go
if object_id('TestTable') is not null
drop table TestTable
go
if object_id('TestTableSyn') is not null
drop synonym TestTableSyn
go
create table TestTable (i int)
go
CREATE SYNONYM [dbo].[TestTableSyn] FOR [dbo].TestTable
GO
dbcc DBREINDEX('TestTableSyn')
go
--dbcc CHECKTABLE('TestTableSyn')
--go

You would get below error in the result.

Msg 2547, Level 16, State 1, Line 15

Unable to process object ID 2007014231 (object "AttributeTypes") because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references."

 

Let’s have a look at script what he was using.

USE DBName   --Enter the name of the database you want to reindex 

DECLARE @TableName varchar(255) 

DECLARE TableCursor CURSOR FOR 
SELECT table_name FROM information_schema.tables 
WHERE table_type = 'base table' 

OPEN TableCursor 

FETCH NEXT FROM TableCursor INTO @TableName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
PRINT 'Reindexing ' + @TableName 
DBCC DBREINDEX(@TableName,' ',90) 
FETCH NEXT FROM TableCursor INTO @TableName 
END 
CLOSE TableCursor 

Do you see any problem? It looks good but notice that schema name is not considered in the script. I executed below and that would explain the cause.

Select * from sys.objects where name = 'AttributeTypes'

Looking at output, we can tell that there are two objects with same name in two different schemas (1 and 5). Schema 1 would be dbo and 5 was Metadata. In the script which he was using, the schema name was not considered. Due to this DBCC DBREINDEX is running for dbo.AttributeTypes which is synonym (2007014231)and due to that we are seeing error.

I provided below modified script.

DECLARE @TableName SYSNAME
        ,@schema_name SYSNAME
        ,@fullname VARCHAR(8000)
DECLARE TableCursor CURSOR
FOR
SELECT  TABLE_NAME
        ,TABLE_SCHEMA
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName ,@schema_name
WHILE @@FETCH_STATUS = 0
BEGIN
        SET @fullname = @schema_name + '.' + @TableName
        PRINT @fullname
        DBCC DBREINDEX (@fullname,' ',90)
        FETCH NEXT FROM TableCursor INTO @TableName ,@schema_name

END
CLOSE TableCursor
DEALLOCATE TableCursor
go

I tested the script and it worked fine for CRM database. Same issue was blogged by my colleague John over here and it has some more suggestions to try.

On a side note, ideally you should use Maintenance plan available with SQL Server to rebuild the indexes.

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in Error, SQL Server | Tagged: , , , | 3 Comments »

    Solution : SQL Server Configuration Manager – Cannot connect to WMI provider – Provider load failure [0x80041013]

    Posted by blakhani on February 3, 2015


    One of my friend had something wrong with SQL Server installation. He did multiple things like repair, registry cleanup etc. I helped him in cleaning up stuff and reinstalling SQL Server. Things were looking good but there was one pending issue.

    As soon as he was trying to launch SQL Server Configuration Manager, he was seeing below error.

    —————————
    SQL Server Configuration Manager
    —————————
    Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
    Provider load failure [0x80041013]
    —————————
    OK  
    —————————

     

    Another variation of same error is The specified module could not be found. [0x8007007e] – This clearly indicates that some dll is not registered.

    He found this KB http://support.microsoft.com/kb/980142 but he was on SQL 2008 SP3 so this was not applicable. Since there was many things done by him, I was almost sure that this is due to either missing DLL or some WMI provider related files. Below were the steps performed to fix the issue.

    • We need to make sure that we have sqlmgmproviderxpsp2up.mof file present and complied. Refer my earlier blog for using mofcomp to compile the mof files.
    • Make sure we have sqlmgmprovider.dll file in “shared” folder. The location of folder would be dependent on SQL version. On my machine it is located under “C:\Program Files\Microsoft SQL Server\100\Shared” The folder 100 is for SQL 2008. Refer below table for version and name mapping.  

    Microsoft SQL Server 2014

    120

    Microsoft SQL Server 2012

    110

    Microsoft SQL Server 2008 R2

    100

    Microsoft SQL Server 2008

    100

    Microsoft SQL Server 2005

    90

    • If file is present in the folder, please register the DLL using command (regsvr32.exe "C:\Program Files\Microsoft SQL Server\110\Shared\sqlmgmprovider.dll")
    • Make sure we have file “svrenumapi.dll” in the same folder. If file exists, register it using regsrv32.exe as pointed earlier. In SQL 2014 the file is called as svrenumapi12.dll
    • Make sure that file framedyn.dll exists in “C:\WINDOWS\system32” folder. If not, pick it from C:\WINDOWS\system32\wbem folder and keep it to system32.
    • Check your environment variables. the PATH variable has C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem

    If none of the above is working then you should download Process Monitor and capture data while launching SQL Server Configuration Manager. Look for “Name not found” and that might give you some hint. Comment below if you found any other solution

    Posted in Error, SQL Server | Tagged: , , , , , | 1 Comment »

    Solution : Suspect Database – SQL Server detected a DTC/KTM in-doubt transaction with UOW .Please resolve it following the guideline for Troubleshooting DTC Transactions.

    Posted by blakhani on January 27, 2015


    While testing application which uses distributed transaction, I caused a failure of SQL Server Database. When I performed failover I found that database didn’t recover and failed to come online. It was shown as “Suspect” in SQL Server Management Studio. I checked ERRORLOG and found below messages.

    2015-01-22 22:50:33.570    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 0% complete (approximately 24 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
    2015-01-22 22:50:33.990    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 2% complete (approximately 24 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
    2015-01-22 22:50:34.080    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 0% complete (approximately 130 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
    2015-01-22 22:50:34.180    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 11% complete (approximately 5 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
    2015-01-22 22:50:35.480    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 44% complete (approximately 2 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
    2015-01-22 22:50:37.720    spid15s    10704 transactions rolled forward in database ‘MyBadDatabase’ (5). This is an informational message only. No user action is required.
    2015-01-22 22:50:37.820    spid7s    Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2015-01-22 22:50:37.850    spid15s    CHECKDB for database ‘MyBadDatabase’ finished without errors on 2012-01-08 23:41:18.113 (local time). This is an informational message only; no user action is required.
    2015-01-22 22:50:38.870    spid7s    Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2015-01-22 22:52:22.990    spid7s    SQL Server detected a DTC/KTM in-doubt transaction with UOW  {6BAC37B8-6515-4FC1-972B-C059D1D5133E}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
    2015-01-22 22:52:22.990    spid7s    Error: 3437, Severity: 21, State: 3.
    2015-01-22 22:52:22.990    spid7s    An error occurred while recovering database ‘MyBadDatabase’. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:743395420). Fix MS DTC, and run recovery again.
    2015-01-22 22:52:22.990    spid7s    Error: 3414, Severity: 21, State: 2.
    2015-01-22 22:52:22.990    spid7s    An error occurred during recovery, preventing the database ‘MyBadDatabase’ (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

     

    As we can see that database was trying to recover. Phase 2 of roll forward is complete and Phase 3 (Rollback phase) encountered an error. The error is cause due to the fact that my bad application uses MSDTC and I caused things to break in MSDTC itself. Ideal way to fix the problem would be to follow KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;306366 (PRB: Errors 3437, 3414, and "Could Not Connect to DTC" Occur When You Start SQL Server)

    In my case, I have restarted DTC and I was not able to find UOW anywhere which I need to kill. When I checked sys.dm_tran_locks for column request_owner_guid for any GUID that is non ZERO. But I didn’t find any non Zero (6BAC37B8-6515-4FC1-972B-C059D1D5133E). Here are the steps I did to fix my database.

    sp_configure 'show advanced options', 1 
    GO 
    RECONFIGURE 
    GO 
    sp_configure 'in-doubt xact resolution', 2 --- presume abort 
    GO 
    RECONFIGURE 
    GO 
    
    

    Error log showed the change.

    2015-01-23 00:45:53.850 spid86       Configuration option ‘in-doubt xact resolution’ changed from 0 to 2. Run the RECONFIGURE statement to install.

    In your case, you need to check with application vendor/team to find what needs to be done with In-doubt transactions. Please use appropriate value (I have used 2) and then perform recovery of database.

    dbcc dbrecover(MyBadDatabase)
    

     

    Once above steps are performed, I was able to get database recovered. Here is the snippet from errorlog.

    2015-01-23 00:46:10.090 spid57       Bypassing recovery for database ID 5. This is an informational message only. No user action is required.

    2015-01-23 00:46:10.100 spid57       Starting up database ‘MyBadDatabase’.

    2015-01-23 00:47:28.570 spid57       Recovery of database ‘MyBadDatabase’ (5) is 0% complete (approximately 130 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

    2015-01-23 00:47:28.670 spid57       Recovery of database ‘MyBadDatabase’ (5) is 1% complete (approximately 7 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

    2015-01-23 00:47:29.630 spid57       Recovery of database ‘MyBadDatabase’ (5) is 29% complete (approximately 2 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

    2015-01-23 00:47:34.800 spid57       1 transactions rolled back in database ‘MyBadDatabase’ (5). This is an informational message only. No user action is required. 2015-01-23 00:47:34.800 spid57       Recovery is writing a checkpoint in database ‘MyBadDatabase’ (5). This is an informational message only. No user action is required. 2015-01-23 00:47:34.820 spid57       Recovery completed for database MyBadDatabase (database ID 5) in 6 second(s) (analysis 20 ms, redo 3142 ms, undo 3056 ms.) This is an informational message only. No user action is required.

    2015-01-23 00:47:34.860 spid57       CHECKDB for database ‘MyBadDatabase’ finished without errors on 2012-01-08 23:41:18.113 (local time). This is an informational message only; no user action is required.

    Hopefully this would bring database online if it was caused due to DTC problem. There might be various causes for suspect database and this blog just covers one of them.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server, SQL Server 2012, SQL Server 2014 | Leave a Comment »

     
    Follow

    Get every new post delivered to your Inbox.

    Join 926 other followers