Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,153,598 hits

  • Select GETDATE()

    December 2025
    M T W T F S S
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  

Archive for the ‘SQL Server’ Category

« Previous Entries
Next Entries »

Help : Unable to start SQL Service after installing Service Pack or Cumulative Upgrade or Hotfix

Posted by blakhani on March 10, 2015


This is one of the common issue which a DBA might have seen. Let’s first understand what happens whenever an upgrade is performed for SQL Server. During installation of patch the binaries are updated, resource database is replaced. During the restart, after successful completion, the upgrade scripts are run. This is the time when Login would fail with below error.

2015-01-05 10:45:03.23 Logon       Error: 18401, Severity: 14, State: 1.
2015-01-05 10:45:03.23 Logon       Login failed for user ‘NT AUTHORITY\SYSTEM’. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: <local machine>]

This error is normal for sometime till upgrade scripts have completed execution. It has been observed that sometimes, due to system configuration, the script don’t run successfully. If upgrade script has not run, SQL would shutdown automatically and below would be the message in SQL ERRORLOGs.

2015-01-05 10:45:07.94 spid9s      Error: 3417, Severity: 21, State: 3.
2015-01-05 10:45:07.94 spid9s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Above message is very scary and suggest you to do something which you may not want. Now, If you are into situation where upgrade is not complete, there are few things you should try. Very first thing is look at ERRORLOG and locate a place where you would find some error during script upgrade. Here are few sample errors which you might see.

Error 1 – CREATE DATABASE failed

2015-01-05 10:45:02.55 spid9s      Database ‘master’ is upgrading script ‘sqlagent90_sysdbupg.sql’ from level 0 to level 2.
2015-01-05 10:45:07.94 spid9s      Error: 5133, Severity: 16, State: 1.
2015-01-05 10:45:07.94 spid9s      Directory lookup for the file "F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 3(The system cannot find the path specified.).
2015-01-05 10:45:07.94 spid9s      Error: 1802, Severity: 16, State: 1.
2015-01-05 10:45:07.94 spid9s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2015-01-05 10:45:07.94 spid9s      Error: 912, Severity: 21, State: 2.
2015-01-05 10:45:07.94 spid9s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent90_sysdbupg.sql’ encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2015-01-05 10:45:07.94 spid9s      Error: 3417, Severity: 21, State: 3.
2015-01-05 10:45:07.94 spid9s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Okay. Problem here is that F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA folder is missing as we are getting error “The system cannot find the path specified”. The database default path is stored in registry and this is explained here. All we need to do is change the registry to correct value and start SQL Services.

Error 2 – affinity mask related error

2015-01-01 00:00:06.79 spid7s      Database ‘master’ is upgrading script ‘sqlagent100_msdb_upgrade.sql’ from level 0 to level 3.
2015-01-01 00:00:06.79 spid7s      —————————————-
2015-01-01 00:00:06.79 spid7s      Starting execution of PREINSTMSDB100.SQL
2015-01-01 00:00:06.79 spid7s      —————————————-
..
2015-01-01 00:00:06.97 spid7s      Setting database option COMPATIBILITY_LEVEL to 100 for database msdb.
2015-01-01 00:00:07.01 spid7s      Configuration option ‘allow updates’ changed from 1 to 1. Run the RECONFIGURE statement to install.
2015-01-01 00:00:07.01 spid7s      Configuration option ‘allow updates’ changed from 1 to 1. Run the RECONFIGURE statement to install.
2015-01-01 00:00:07.01 spid7s      Error: 5833, Severity: 16, State: 1.
2015-01-01 00:00:07.01 spid7s      The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server.
2015-01-01 00:00:07.01 spid7s      Error: 912, Severity: 21, State: 2.
2015-01-01 00:00:07.01 spid7s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 5833, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2015-01-01 00:00:07.02 spid7s      Error: 3417, Severity: 21, State: 3.
2015-01-01 00:00:07.02 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

To fix above problem, we can start the Instance by skipping the post upgrade script by adding trace flag –T902 to the startup parameters. Steps to enable trace flag can be found in this KB Once SQL is started and we should be able to connect to SQL Server normally. Then connect via SSMS and check the checkbox “Automatically set processor affinity mask for all processors” under instance properties. This could have also been archived using the below TSQL. 

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
GO

Once configuration change as been done, we need to remove the trace flag and then start SQL normally. Post restart, we need to check SQL Server ERRORLOG and make sure we have below line.

Recovery is complete. This is an informational message only. No user action is required

This message during upgrade scenario means that post upgrade script have been executed successfully.

If you have encountered any error during upgrade, please comment and let me know.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in SQL Server, Tips and Tricks, Trace Flag | Tagged: | Leave a Comment »

    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: , , , , , | 3 Comments »

    « Previous Entries
    Next Entries »