Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Error : Your SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.). Please uninstall then re-run setup to correct this problem

Posted by blakhani on December 16, 2014


While working for my next blog I made some stupid changes and wanted to start SQL from command line. I have been using net start command to start SQL Services from command line but today I ran the executable directly. The advantage of this method is that information which goes to ERRORLOG is also shown on command line. I browsed to Binn folder and typed executable name sqlservr.exe and hit enter. I was welcomed with Error message as below.

E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn>sqlservr.exe
2014-12-16 05:14:43.93 Server      Multiple instances of SQL server are installed on this computer. Renter the command, specifying the -s parameter with the name of the instance that you want to start.

Since my machine has multiple instances, the error message was quite clear so I added –s SQL2012 and hit enter. and this time, I got more dangerous error message

—————————
SQL Server
—————————
Your SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.).   Please uninstall then re-run setup to correct this problem
—————————
OK  
—————————

 

Well, error message as interesting information “Error getting instance ID from name” I went ahead and looked at instance name and it was SQL2014. Once I gave sqlservr.exe –sSQL2014 on command prompt, life was good and SQL Server got started.

If you get similar error, First make sure that instance name is correct. To know the instance name, refer my earlier blog What’s my SQL Server Name?

Hope this helps.
Balmukund

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Facebook blakhani
  • Posted in Error, SQL Server | Tagged: , | Leave a Comment »

    SSMS – FIX – Error – The log file is not using Unicode format

    Posted by blakhani on December 11, 2014


    While playing with SQL Server Agent Log setting, I changed some setting and found that I was not able to read SQLAgent Logs any more. Here is the error which I was getting in SSMS.

    I followed my own blog and executed T-SQL to read the Agent log and got same error.

    Msg 22004, Level 16, State 1, Line 13
    The log file is not using Unicode format.

    I looked around into the properties which I changed and one of them was “Write OEM error log”. As per MSDN documentation, this option writes the error log file as a non-Unicode file. This reduces the amount of disk space consumed by the log file. However, messages that include Unicode data may be more difficult to read when this option is enabled.

    And that’s precisely the error message says – “it’s not in unicode”.

    There are two ways to fix the error.

    Method 1 (UI Based)

    • Connect to Object Explorer to SSMS > Expand “SQL Server Agent” >  Right Click on “Error Logs” and choose “Configure”
    • Over there uncheck the box “Write OEM error log”

    Method 2 (T-SQL Based)

    Execute below query

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_set_sqlagent_properties @oem_errorlog=0
    GO
    
    
    

    Both of the methods have same effect. Once done, restart SQL Agent service so that now the file is written in unicode format. You should be able to view the log in the viewer now.

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server, SQL Server Agent, SQL Server Management Studio, SSMS | Tagged: , , , , | Leave a Comment »

    SQL Internals : Useful Parameters for xp_readerrorlog

    Posted by blakhani on December 10, 2014


    I know that most of you have been using sp_readerrorlog for long time. While working with SSMS, I noticed that we have options to put filters on various parameter. I became curious to know what is done in the background.

    If we look at code of sp_readerrorlog using sp_helptext system procedure, this is the output.

    create proc sys.sp_readerrorlog(
        @p1        int = 0,
        @p2        int = NULL,
        @p3        nvarchar(4000) = NULL,
        @p4        nvarchar(4000) = NULL)
    as
    begin
    
        if (not is_srvrolemember(N'securityadmin') = 1)
        begin
           raiserror(15003,-1,-1, N'securityadmin')
           return (1)
        end
        
        if (@p2 is NULL)
            exec sys.xp_readerrorlog @p1
        else
            exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
    end
    
    
    

    Which means that under the cover, xp_readerrorlog is being used by sp_readerrorlog. Coming back to SSMS, here is the snip of information taken from profiler while launching Log File Viewer. I have done some formatting to make it look proper.

    -- Below is the way SSMS Reads the ErrorLog. Can be found via profiler
    
    Declare @InstanceName nvarchar(4000),
            @ArchiveID int,
            @Filter1Text nvarchar(4000),
            @Filter2Text nvarchar(4000),
            @FirstEntry datetime,
            @LastEntry datetime
    
    Select  @InstanceName=NULL,
            @ArchiveID=0,
            @Filter1Text='recovery',
            @Filter2Text='mydb',
            @FirstEntry='2014-11-22 07:14:46.930',
            @LastEntry='2014-11-22 07:14:50.020'
    
    EXEC master.dbo.xp_readerrorlog @ArchiveID, 1, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

     

    That gives us a lot of hint about the parameters of xp_readerrorlog. Here is the explanation.

    Parameter Name Usage
    @ArchiveID Extension of the file which we would like to read.

    0 = ERRORLOG/SQLAgent.out

    1 = ERRORLOG.1/SQLAgent.1  and so on

    @LogType 1 for SQL Server ERRORLOG (ERRORLOG.*)

    2 for SQL Agent Logs (SQLAgent.*)
    @FilterText1 First Text filter on data
    @FilterText2 Another Text filter on data. Output would be after applying both filters, if specified
    @FirstEntry Start Date Filter on Date time in the log
    @LastEntry End Date Filter on Date time in the log
    @SortOrder ‘asc’ or ‘desc’ for sorting the data based on time in log.

     

    Sample with explanation

    I have customized the script so that you can copy paste in your environment and put the values.

    Declare @InstanceName nvarchar(4000),
            @LogType int,
            @ArchiveID int,
            @Filter1Text nvarchar(4000),
            @Filter2Text nvarchar(4000),
            @FirstEntry datetime,
            @LastEntry datetime
    
    Select  @InstanceName=NULL,          -- Don't know :)
            @LogType = 2,                -- File Type (1 = ERRORLOG OR 2 = SQLAgent)
            @ArchiveID=3,                -- File Extension (0 = Current i.e. ERRORLOG or SQLAgent.out, 1 = ERRORLOG.1 or SQLAgent.1 and so on)
            @Filter1Text='Waiting',      -- First Text Filter
            @Filter2Text=NULL,           -- Second Text Filter
            @FirstEntry=NULL,            -- Start Date
            @LastEntry=NULL              -- End Date
    
    EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

    Here are few samples

    Example 1

    Read ERRORLOG and apply filter for ‘login failed’ and ‘sa’

    Declare @InstanceName nvarchar(4000),
            @LogType int,
            @ArchiveID int,
            @Filter1Text nvarchar(4000),
            @Filter2Text nvarchar(4000),
            @FirstEntry datetime,
            @LastEntry datetime
    
    Select  @InstanceName=NULL,          -- Don't know :)
            @LogType = 1,                -- 1 = ERRORLOG
            @ArchiveID=0,                -- File Extension 0 
            @Filter1Text='Login failed', -- First Text Filter
            @Filter2Text='sa',           -- Second Text Filter
            @FirstEntry=NULL,            -- Start Date
            @LastEntry=NULL              -- End Date
    
    EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

    Here is the output

     

    Example 2

    In above example, let’s apply date time filer as well. We will filter records only for 05-Dec-2014.

    Declare @InstanceName nvarchar(4000),
            @LogType int,
            @ArchiveID int,
            @Filter1Text nvarchar(4000),
            @Filter2Text nvarchar(4000),
            @FirstEntry datetime,
            @LastEntry datetime
    
    Select  @InstanceName=NULL,                  -- Don't know :)
            @LogType = 1,                        -- 1 = ERRORLOG
            @ArchiveID=0,                        -- File Extension 0 
            @Filter1Text='Login failed',         -- First Text Filter
            @Filter2Text='sa',                   -- Second Text Filter
            @FirstEntry='2014-12-05 00:00:00',   -- Start Date
            @LastEntry='2014-12-05 23:59:59'     -- End Date
    
    EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

    As expected, the output is similar to Example 1 but with date filter applied.

    Example 3

    In this example, we are trying to read Read SQLAgent.3 (i.e. LogType = 2 and ArchiveID = 3) and output should be descending date time.

    Declare @InstanceName nvarchar(4000),
            @LogType int,
            @ArchiveID int,
            @Filter1Text nvarchar(4000),
            @Filter2Text nvarchar(4000),
            @FirstEntry datetime,
            @LastEntry datetime
    
    Select  @InstanceName=NULL,    -- Don't know :)
            @LogType = 2,          -- 2 = SQLAgent
            @ArchiveID=3,          -- File Extension 3 
            @Filter1Text=NULL,     -- First Text Filter
            @Filter2Text=NULL,     -- Second Text Filter
            @FirstEntry=NULL,      -- Start Date
            @LastEntry=NULL        -- End Date
    
    EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'DESC', @InstanceName

    Here is the output.

    Hope this gives you an understanding of neat way of reading SQL Server ERRORLOG and SQLAgent logs.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in Internals, SQL Server | Tagged: , , , | Leave a Comment »

     
    Follow

    Get every new post delivered to your Inbox.

    Join 886 other followers