Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,777,800 hits
  • Select GETDATE()

    July 2020
    M T W T F S S
     12345
    6789101112
    13141516171819
    20212223242526
    2728293031  

Archive for the ‘SQL Server’ Category

Restore ERROR : This backup cannot be restored using WITH STANDBY because a database upgrade is needed

Posted by blakhani on May 21, 2015


One of my colleague in office sent an email to find the cause of the error. He wanted to configure Log-Shipping with secondary on Standby mode. Rather than “reply to all”, I generally ping the person directly to tell the possible reason of error. Here is what we discussed (let’s assume that his name is Manoj – M)

B: Hi Manoj
M: Hello
B: Saw you email to DL about log shipping problem.
M: Oh yeah. Thanks for reaching out.
B: No problem.
B: Are you restoring database from lower version to higher version of SQL?
M: No, both are 2008.
B: That’s doesn’t sound right. Can you please run Select @@version command in Management Studio on both the server and share the output?
M: Sure. Give me a minute to connect and run the query.
B: Sure

<1 min pause>

M: There you go

Source:
Microsoft SQL Server 2008 (SP1) – 10.0.2573.0 (X64)
Feb 4 2011 11:27:06
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Destination:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

 

B: Okay. that is the problem. You are taking backup from SQL 2008 and restoring on SQL 2008 R2 in standby mode.
M: Both are not 2008?
B: No, destination is 2008 “R2” which is the next release after 2008.
M: Oh.. I thought its like a service pack on top of SQL 2008.
B: Unfortunately, its not a service pack. It’s fresh release having some enhancements.
M: Okay. So error message is correct.
B: Yeah. If you want to use secondary for reporting then you have to use standby mode. This can only be done on destination which has same version of SQL. Or you need to upgrade current primary server.
M: Hmm.. I got it now. Let me check with my DBA team and management to decide the next action.
B: Sure Manoj. Is there any other clarification you might need?
M: No Balmukund. I am good at this point. You have been very helpful. Appreciate it.
B: My pleasure. Bye for now.
M: Bye!

In summary, Here is what you would get in SSMS if we try to restore a backup from lower version of SQL to higher version of SQL in standby mode.

TITLE: Microsoft SQL Server Management Studio
——————————
SQL Server Management Studio restore database ‘DabbaWala’
——————————
ADDITIONAL INFORMATION:
Restore failed for Server ‘Destination’.  (SqlManagerUI)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
——————————
System.Data.SqlClient.SqlError: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

The error is raised because during restore from lower to higher version the database version needs an upgrade to match with system table (or any other) change in higher version. Here is the query to see the version I am talking about.

Select name, version from sys.sysdatabases

This version upgrade is part of recovery process and we can’t pause recovery in middle and view the database state (that’s what is done in standby mode). So, the only way to restore that would be to use “with recovery” or “with norecovery” option.

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error, Logshipping, Restore, SQL Server | Tagged: , , | 2 Comments »

    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
  • Posted in Checkpoint, Cluster, SQL Server | Tagged: , , , | 3 Comments »

    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 »