Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,581,945 hits
  • Select GETDATE()

    March 2015
    M T W T F S S
    « Feb   Apr »
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  
  • Advertisements

Archive for March 25th, 2015

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
  • Advertisements

    Posted in EXCEPTION_ACCESS_VIOLATION, SQL Server, SQL Server 2014 | Tagged: , , | 13 Comments »