Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,552,617 hits
  • Select GETDATE()

    January 2019
    M T W T F S S
    « Apr    
     123456
    78910111213
    14151617181920
    21222324252627
    28293031  
  • Advertisements

Archive for the ‘SQL Server’ Category

Information : Checkpoint in SQL Server Cluster Resources

Posted by blakhani on April 17, 2015


If you have searched for checkpoint in SQL Server and landed on this page, then you might get disappointed. This blog is NOT about the checkpoint background process which executes in SQL Server. This blog is about the checkpoint which is windows cluster piece.

Checkpoint Manager is the component of Cluster Service which monitors the changes in the Resources on a Cluster. In simple words, checkpoint is a process in cluster which synchronize the registry key values between the nodes. SQL Server startup depends on the registry key values. By default there are three registry keys: –d for path of master.mdf, –l is for the location of mastlog.ldf fie and –e is for the location of SQL Server ERRORLOG file. More startup parameters can be added based on requirement. For example if you are troubleshooting deadlock then you may add –T1222 as startup parameter. In case of cluster, you make change to registry key by using SQL Server Configuration Manager (a.k.a. SSCM). Once value is saved in SSCM, it would be saved in registry. Once failover is performed, the same change would be applied to other node. This is done by cluster checkpoint.

What would happen if checkpoints are not defined? The changes made on active node would not propagate to other nodes during failover. Another interesting point which you must note is that if any change is made to registry key when resource is offline, the changed would be reverted back. When the resource goes offline, the values are saved into registry.

Since this is windows piece, there are windows commands and PowerShell cmdlets available to view and edit them. In earlier version of windows, cluster.exe was used to view and edit the checkpoints and in newer version of operating systems where admins loves PowerShell, there are cmdlets exposed. Here is the list of commands to add checkpoint

Old Version (if you have cluster.exe)

cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Cluster"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\MSSQLServer"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Replication"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Providers"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerSCP"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\CPE"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerAgent"

PoweShell

Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Cluster"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\MSSQLServer"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Replication"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Providers"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerSCP"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\CPE"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerAgent"

Here are the command to get list of checkpoint (my Virtual Server Name is RajaSQLServer).

cluster res "SQL Network Name (RajaSQLServer)" /checkpoints

Here is the PowerShell command

Get-ClusterCheckpoint

You would notice the difference in registry key values as compared to the command. This is because of the fact that the command is for SQL 2014 that’s why we are seeing “MSSQL12”. The exact value can be fetched from registry.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in Checkpoint, Cluster, SQL Server | Tagged: , , , | 1 Comment »

    Solution – SQL Server Backup Failing with EXCEPTION_ACCESS_VIOLATION

    Posted by blakhani on March 25, 2015


    Recently someone posted on Facebook group about a problem. He informed that whenever the backups are taken in SQL Server 2014 instance, it’s failing with error as below.

    Msg 3013, Level 16, State 1, Line 4
    BACKUP DATABASE is terminating abnormally.

    When we open ERRORLOG we saw below

    ***Stack Dump being sent to D:\MSSQL\LOG\SQLDump0089.txt
    SqlDumpExceptionHandler: Process 1144 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
    * ******************************************************************************* 

    * BEGIN STACK DUMP:
    *   01/01/15 18:05:25 spid 1144
    * Private server build.
    *
    *
    *   Exception Address = 00007FF85B652D45 Module(sqlmin+00000000009D2D45)
    *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
    *   Access Violation occurred reading address 00007FFA0C208980
    * Input Buffer 116 bytes –
    *             backup database test to disk=’c:\temp\test.bak’

    As we can see that Access Violation is occurring while running backup database command. Whenever there is a dump generated, there is a MDMP file is also generated. Here is the stack when we analyze the dump. Yon can refer http://mssqlwiki.com/2012/10/16/sql-server-exception_access_violation-and-sql-server-assertion link to know more about identifying stack.

    sqlmin!PerfmonManager::AddInstance+0x4e0
    sqlmin!BackupPerfmonCounter::AddInstance+0x1f
    sqlmin!BackupDevicePerfmonCounter::Init+0x13c
    sqlmin!BackupFileDesc::InitPerfCounters+0xc                          <<Performance Counters Initialization.
    sqlmin!BackupMediaIo::Initialize+0x29
    sqlmin!BackupMedium::CreateDeviceObject+0x224
    sqlmin!BackupMedium::Open+0x27
    sqlmin!BackupStream::OpenForBackup+0x27
    sqlmin!BackupStream::ThreadMainRoutine+0x160
    sqlmin!BackupThread::ThreadBase+0x51
    sqlmin!SubprocEntrypoint+0xa7f                                       << This is the child thread of main backup thread.
    sqldk!SOS_Task::Param::Execute+0x21e
    sqldk!SOS_Scheduler::RunTask+0xa8
    sqldk!SOS_Scheduler::ProcessTasks+0x279
    sqldk!SchedulerManager::WorkerEntryPoint+0x24c
    sqldk!SystemThread::RunWorker+0x8f
    sqldk!SystemThreadDispatcher::ProcessWorker+0x3ab
    sqldk!SchedulerManager::ThreadEntryPoint+0x226
    kernel32!BaseThreadInitThunk+0xd
    ntdll!RtlUserThreadStart+0x1d

    Here is the version of SQL Server.

    Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    This is identified as an issue with SQL Server 2014 RTM version. Here is the KB article – http://support.microsoft.com/en-us/kb/2973444 (FIX: "SQL Server performance counters are disabled" when you move the SQL Server resource in SQL Server 2014)

    Fix: Cumulative Update 2 for SQL Server 2014 or any later version of SQL Server 2014.

    If you are running higher version and above fix then you need to find out why performance monitor is not showing SQL Server related counters. The way to verify it would be to use DMV

    select * from sys.dm_os_performance_counters
    

     

    You should see many counters specifically look for “Backup/Restore Throughput/sec”. In SQL 2014 this is a new performance counter added to get backup/restore speed and that missing counter (Backup/Restore Throughput/sec) which is causing backup to fail.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in EXCEPTION_ACCESS_VIOLATION, SQL Server, SQL Server 2014 | Tagged: , , | 13 Comments »

    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 AlwaysOnPaperback, Kindle
  • Posted in SQL Server, Tips and Tricks, Trace Flag | Tagged: | Leave a Comment »