Windows Debugger ( Windbg) – Analyze – “sqldumperlibraryinvoke” – 2018-June ( Session 4 )

Background

Let us start analyzing some of our mdmp files.

Process

Outline

  1. Launch windbg
  2. Open dump file
  3. Download Symbols Library
  4. Analyze

Open Dump  File

Image

bucket_20180627_0933AM.png

Text


Loading Dump File [E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLDump0057.mdmp]
User Mini Dump File with Full Memory: Only application data is available

Comment: 'Stack Trace'
Comment: 'Location: bckioreq.cpp:2037
Expression: GetBytesRequested () == GetBytesTransferred ()
SPID: 53
Process ID: 1336'
Symbol search path is: srv*

Explanation

  1. Error
    • Comment: ‘Stack Trace’Comment: ‘Stack Trace’Comment: ‘Location: bckioreq.cpp:2037Expression: GetBytesRequested () == GetBytesTransferred ()SPID: 53

 

Analyze

Syntax


!analyze -v

Output


0:050> !analyze -v
*******************************************************************************
*                                                                             *
*                        Exception Analysis                                   *
*                                                                             *
*******************************************************************************

WARNING: Thread 1b2484 exited during dump writing (open status NTSTATUS 0xC000000B)
WARNING: Thread 1b2484 exited during dump writing (open status NTSTATUS 0xC000000B)
WARNING: Teb 76 pointer is NULL - defaulting to 00000000`7ffde000
WARNING: 00000000`7ffde000 does not appear to be a TEB
WARNING: Teb 76 pointer is NULL - defaulting to 00000000`7ffde000
WARNING: 00000000`7ffde000 does not appear to be a TEB
WARNING: Teb 76 pointer is NULL - defaulting to 00000000`7ffde000
WARNING: 00000000`7ffde000 does not appear to be a TEB
WARNING: Teb 76 pointer is NULL - defaulting to 00000000`7ffde000
WARNING: 00000000`7ffde000 does not appear to be a TEB
WARNING: Teb 76 pointer is NULL - defaulting to 00000000`7ffde000
WARNING: 00000000`7ffde000 does not appear to be a TEB
WARNING: Teb 76 pointer is NULL - defaulting to 00000000`7ffde000
WARNING: 00000000`7ffde000 does not appear to be a TEB
WARNING: Teb 76 pointer is NULL - defaulting to 00000000`7ffde000
WARNING: 00000000`7ffde000 does not appear to be a TEB
GetUrlPageData2 (WinHttp) failed: 12002.

KEY_VALUES_STRING: 1

TIMELINE_ANALYSIS: 1

Timeline: !analyze.Start
    Name: 
    Time: 2018-06-27T16:13:27.633Z
    Diff: 1083451633 mSec

Timeline: Dump.Current
    Name: 
    Time: 2018-06-15T03:15:56.0Z
    Diff: 0 mSec

Timeline: Process.Start
    Name: 
    Time: 2018-06-11T19:49:21.0Z
    Diff: 285995000 mSec

Timeline: OS.Boot
    Name: 
    Time: 2018-06-11T19:48:34.0Z
    Diff: 286042000 mSec

DUMP_CLASS: 2

DUMP_QUALIFIER: 400

CONTEXT:  (.ecxr)
rax=000000800194ab60 rbx=0000000080004005 rcx=000000800194a900
rdx=0000008024699cd0 rsi=0000000000008060 rdi=0000000000000000
rip=00007ff94c7b8eac rsp=000000800194ad30 rbp=00000001b069d19a
 r8=0000000000000001  r9=0000000000000000 r10=00007ff942a5da38
r11=00000000fffffffe r12=00007ff93f6d3600 r13=00000001b069d19a
r14=00007ff93ee4f4d0 r15=00007ff66d0fbd80
iopl=0         nv up ei pl nz na po nc
cs=0033  ss=002b  ds=002b  es=002b  fs=0053  gs=002b             efl=00000206
KERNELBASE!RaiseException+0x68:
00007ff9`4c7b8eac 488b8c24c0000000 mov     rcx,qword ptr [rsp+0C0h] ss:00000080`0194adf0=000009b980e74c0b
Resetting default scope

FAULTING_IP:
sqllang!SQLDumperLibraryInvoke+1f6
00007ff9`3de802c6 448bf8          mov     r15d,eax

EXCEPTION_RECORD:  (.exr -1)
ExceptionAddress: 00007ff93de802c6 (sqllang!SQLDumperLibraryInvoke+0x00000000000001f6)
   ExceptionCode: 000042ac
  ExceptionFlags: 00000000
NumberParameters: 0

DEFAULT_BUCKET_ID:  APPLICATION_FAULT

PROCESS_NAME:  sqlservr.exe

ERROR_CODE: (NTSTATUS) 0x42ac - 

EXCEPTION_CODE: (Win32) 0x42ac (17068) - 

EXCEPTION_CODE_STR:  42ac

WATSON_BKT_PROCSTAMP:  58145622

WATSON_BKT_PROCVER:  2015.130.4001.0

PROCESS_VER_PRODUCT:  Microsoft SQL Server

WATSON_BKT_MODULE:  sqllang.dll

WATSON_BKT_MODSTAMP:  581456b1

WATSON_BKT_MODOFFSET:  5602c6

WATSON_BKT_MODVER:  2015.130.4001.0

MODULE_VER_PRODUCT:  Microsoft SQL Server

BUILD_VERSION_STRING:  6.3.9600.17415 (winblue_r4.141028-1500)

MODLIST_WITH_TSCHKSUM_HASH:  a60dd8f5be5ecf77c0efc2309e59f0c75e9e3d93

MODLIST_SHA1_HASH:  df74a5b9ff12b3cb21e79eddd897492af85c05d4

COMMENT:  Stack Trace

NTGLOBALFLAG:  0

PROCESS_BAM_CURRENT_THROTTLED: 0

PROCESS_BAM_PREVIOUS_THROTTLED: 0

APPLICATION_VERIFIER_FLAGS:  0

PRODUCT_TYPE:  3

SUITE_MASK:  272

DUMP_FLAGS:  8000c07

DUMP_TYPE:  3

MISSING_CLR_SYMBOL: 0

ANALYSIS_SESSION_HOST:  HRDB

ANALYSIS_SESSION_TIME:  06-27-2018 09:13:27.0633

ANALYSIS_VERSION: 10.0.17134.12 amd64fre

MANAGED_CODE: 1

MANAGED_ENGINE_MODULE:  clr

MANAGED_ANALYSIS_PROVIDER:  SOS

THREAD_ATTRIBUTES:
OS_LOCALE:  ENU

PROBLEM_CLASSES: 

    ID:     [0n316]
    Type:   [APPLICATION_FAULT]
    Class:  Primary
    Scope:  DEFAULT_BUCKET_ID (Failure Bucket ID prefix)
            BUCKET_ID
    Name:   Add
    Data:   Omit
    PID:    [Unspecified]
    TID:    [Unspecified]
    Frame:  [0]

BUGCHECK_STR:  APPLICATION_FAULT

PRIMARY_PROBLEM_CLASS:  APPLICATION_FAULT

LAST_CONTROL_TRANSFER:  from 00007ff66d0b9c9c to 00007ff94c7b8eac

STACK_TEXT:
00000080`0194ad30 00007ff6`6d0b9c9c : 00000080`0194ae80 00000000`00000000 00000080`0194aeb0 00000080`0194aea0 : KERNELBASE!RaiseException+0x68
00000080`0194ae10 00007ff9`3de802c6 : 00007ff6`6d0fbd80 00000000`00000002 00000000`00000000 00000000`00000002 : sqlservr!CDmpDump::Dump+0x4c
00000080`0194ae50 00007ff9`3e97c411 : 00007ff6`6d0fbd80 00000000`00008060 00000000`00000000 00007ff9`4c7b1616 : sqllang!SQLDumperLibraryInvoke+0x1f6
00000080`0194ae90 00007ff9`3e97ce94 : 00000000`00029164 00000080`0194ccb0 00000000`00000000 00007ff9`3fcdfe30 : sqllang!SQLLangDumperLibraryInvoke+0x161
00000080`0194af40 00007ff9`3e94cd0b : 00000081`8ed00e20 00000080`0194ccb0 00000081`8ed00e20 00000081`8ed00160 : sqllang!CImageHelper::DoMiniDump+0x475
00000080`0194b150 00007ff9`3e9872a9 : 00000000`00000000 00000080`0194ccb0 0fffffff`fffffff0 00000080`000000bf : sqllang!stackTrace+0x9db
00000080`0194cb70 00007ff9`3cc1d714 : 00000000`00000000 ffffffff`fffffffe 00000001`00000003 ffffffff`fffffffe : sqllang!utassert_fail_imp+0x60c
00000080`0194d370 00007ff9`414eacf4 : 00000000`00000000 00007ff9`4c7b22d4 00000000`0040008a 00000000`00000000 : sqldk!utassert_fail+0x2e4
00000080`0194d860 00007ff9`414eb8f7 : 00000080`0194d9a8 00000081`3edb55d0 00000081`3edb55d0 00000000`00000200 : sqlmin!BackupIoRequest::WaitForCompletionInternal+0x254
00000080`0194d8f0 00007ff9`415682c0 : 00000081`3edb55d0 00007ff9`3fed1390 00000081`3edb52b0 ffffffff`ffffff00 : sqlmin!BackupIoRequest::Quiesce+0x47
00000080`0194d990 00007ff9`414c10f3 : 00000081`3edb5450 00000000`00000000 00000081`303a12b0 00000081`303a12c8 : sqlmin!BackupMediaIoRequest::Cleanup+0x80
00000080`0194da70 00007ff9`4155f39d : 00000081`303a1260 00000081`9f706160 00000082`21496e80 00000081`303a1880 : sqlmin!BackupBufferSet::Empty+0x63
00000080`0194daa0 00007ff9`4155f2c0 : 00000081`303a1260 00000081`303a1260 00000082`21496e80 00000081`8ed00160 : sqlmin!BackupMediaIo::ReleaseResources+0x7d
00000080`0194dad0 00007ff9`41516259 : 00000081`303a1260 00000082`21496e80 00000081`0f260160 00007ff9`3cbc26d4 : sqlmin!BackupMediaIo::Discard+0x180
00000080`0194dba0 00007ff9`415220b4 : 00000081`303a0200 00000081`303a0270 00000081`ffffffff 00000080`0194dca8 : sqlmin!BackupStream::ThreadMainRoutine+0x109
00000080`0194dc50 00007ff9`4000f7a9 : 00000081`303a0270 00007ff9`419c6758 00000082`21496e80 00000000`00000000 : sqlmin!BackupThread::ThreadBase+0x54
00000080`0194dcf0 00007ff9`3cbc5bfd : 00000080`0194f600 00000081`9f70ace8 ffffffff`ffffffff 00000000`00000000 : sqlmin!SubprocEntrypoint+0x10ab
00000080`0194f550 00007ff9`3cbc58f5 : 00000081`9f70ace8 00000081`9f70aca8 00000081`9f70ad30 ffffffff`00000000 : sqldk!SOS_Task::Param::Execute+0x231
00000080`0194fb50 00007ff9`3cbc554d : 00000081`8b980040 00000080`0194fc39 00000081`8b980040 00000081`9f706160 : sqldk!SOS_Scheduler::RunTask+0xaa
00000080`0194fbc0 00007ff9`3cbed7c8 : 00000000`00000000 00000081`9f706160 0000002b`6e565d04 00007ff9`3cbedf00 : sqldk!SOS_Scheduler::ProcessTasks+0x3cd
00000080`0194fca0 00007ff9`3cbedb10 : 00000081`9f706160 0000002b`6e565d04 00000081`9f706160 0000002b`6e565d04 : sqldk!SchedulerManager::WorkerEntryPoint+0x2a1
00000080`0194fd70 00007ff9`3cbedcd7 : 00000081`9f706160 00000080`0194fe10 00000082`260803c0 00000080`245bf180 : sqldk!SystemThread::RunWorker+0x8f
00000080`0194fda0 00007ff9`3cbed9f8 : 00000082`26080370 00000000`00000000 00000000`00000000 00000082`260802b0 : sqldk!SystemThreadDispatcher::ProcessWorker+0x2de
00000080`0194fe50 00007ff9`4f2f13d2 : 00000000`00000000 00000000`00000000 00000080`245bf180 00000080`245bf180 : sqldk!SchedulerManager::ThreadEntryPoint+0x1d8
00000080`0194ff00 00007ff9`4f5754f4 : 00007ff9`4f2f13b0 00000000`00000000 00000000`00000000 00000000`00000000 : kernel32!BaseThreadInitThunk+0x22
00000080`0194ff30 00000000`00000000 : 00000000`00000000 00000000`00000000 00000000`00000000 00000000`00000000 : ntdll!RtlUserThreadStart+0x34

THREAD_SHA1_HASH_MOD_FUNC:  feb32110b670587fc13057de8d7ebfe838958940

THREAD_SHA1_HASH_MOD_FUNC_OFFSET:  6aaf83029e4b6c46c389ee612fcb39920fcdcf5d

THREAD_SHA1_HASH_MOD:  f5856987294060b75593249d28208b1df5bb661e

FOLLOWUP_IP:
sqllang!SQLDumperLibraryInvoke+1f6
00007ff9`3de802c6 448bf8          mov     r15d,eax

FAULT_INSTR_CODE:  48f88b44

SYMBOL_STACK_INDEX:  2

SYMBOL_NAME:  sqllang!SQLDumperLibraryInvoke+1f6

FOLLOWUP_NAME:  MachineOwner

MODULE_NAME: sqllang

IMAGE_NAME:  sqllang.dll

DEBUG_FLR_IMAGE_TIMESTAMP:  581456b1

STACK_COMMAND:  ~50s ; .ecxr ; kb

BUCKET_ID:  APPLICATION_FAULT_sqllang!SQLDumperLibraryInvoke+1f6

FAILURE_EXCEPTION_CODE:  42ac

FAILURE_IMAGE_NAME:  sqllang.dll

BUCKET_ID_IMAGE_STR:  sqllang.dll

FAILURE_MODULE_NAME:  sqllang

BUCKET_ID_MODULE_STR:  sqllang

FAILURE_FUNCTION_NAME:  SQLDumperLibraryInvoke

BUCKET_ID_FUNCTION_STR:  SQLDumperLibraryInvoke

BUCKET_ID_OFFSET:  1f6

BUCKET_ID_MODTIMEDATESTAMP:  581456b1

BUCKET_ID_MODCHECKSUM:  258ccd4

BUCKET_ID_MODVER_STR:  2015.130.4001.0

BUCKET_ID_PREFIX_STR:  APPLICATION_FAULT_

FAILURE_PROBLEM_CLASS:  APPLICATION_FAULT

FAILURE_SYMBOL_NAME:  sqllang.dll!SQLDumperLibraryInvoke

FAILURE_BUCKET_ID:  APPLICATION_FAULT_42ac_sqllang.dll!SQLDumperLibraryInvoke

WATSON_STAGEONE_URL:  http://watson.microsoft.com/StageOne/sqlservr.exe/2015.130.4001.0/58145622/sqllang.dll/2015.130.4001.0/581456b1/42ac/005602c6.htm?Retriage=1

TARGET_TIME:  2018-06-15T03:15:56.000Z

OSBUILD:  9200

OSSERVICEPACK:  17415

SERVICEPACK_NUMBER: 0

OS_REVISION: 0

OSPLATFORM_TYPE:  x64

OSNAME:  Windows 8

OSEDITION:  Windows 8 Server TerminalServer SingleUserTS

USER_LCID:  0

OSBUILD_TIMESTAMP:  2014-10-28 19:45:30

BUILDDATESTAMP_STR:  141028-1500

BUILDLAB_STR:  winblue_r4

BUILDOSVER_STR:  6.3.9600.17415

ANALYSIS_SESSION_ELAPSED_TIME:  748d

ANALYSIS_SOURCE:  UM

FAILURE_ID_HASH_STRING:  um:application_fault_42ac_sqllang.dll!sqldumperlibraryinvoke

FAILURE_ID_HASH:  {ab1e43e2-da46-c388-f696-a374d74f9a5d}

Followup:     MachineOwner
---------

Explanation

  1. Exited during dump writing (open status NTSTATUS 0xC000000B)
    • problems experienced during database dumping ( backup )
    • NTSTATUS 0xC000000B

 

Summary

Issues encountered during backup.

Review database backup strategy and resource availability.

SQL Server – Backup Type

Background

Quite familiar with the three types of backup that we have in SQL Server.

Database Backup Types

The database backup types are full, differential, and log.

Metadata

msdb.dbo.backupset

To track backups we can look in the [msdb].[dbo].[backupset] table.

Something Broke?

Today I was trying to do a restore and noticed that that the type column in the [msdb].[dbo].[backupset] table read ‘F‘.

backupset.type ( Legacy )

Here is the traditional mapping of backup operation to type.

Backup Operation Backup Type ( backupset.type)
Database D
Differential I
Transaction L

File Groups Backup

To better support Very Large Databases ( VLDB ), Microsoft added a couple of targeted functionalities.

LAB

Let us play around with targeted file groups backup.

Database Properties

Database Properties – Filegroups

GUI

databaseProperties_GUI_20180625_1047PM.png

Code

sp_helpfilegroup
Sample

use [DBLab]
go

exec sp_helpfilegroup
go

Output

sp_helpfilegroup__20180625__1054PM

sys.filegroups
Sample

use [DBLab]
go

select
        [filegroup]
        = tblSFG.[name]

    , [filegroupID]
        = tblSFG.[data_space_id]

    , [isReadOnly]
        = tblSFG.[is_read_only]

    , [isDefault]
        = tblSFG.is_default

from   sys.filegroups tblSFG

order by
	tblSFG.[name] asc

Output

 sysDOTfilegroups_20180625_1108PM

Explanation
  1. isReadOnly
    • fgAudioFiles
  2. isDefault
    • PRIMARY

Backup

Database Backup – Normal

Sample


use master
go

backup database [DBLab]
	to DISK='NUL:'
	with init
	    , format
		, description='normal' 

Output

database_backup_normal_20180625__1138PM

Explanation

  1. Data files
    • DBLab, fileAudioFile_001, fileVideo_001
  2. Log
    • DBLab_log

Review Backup Type

SQL

declare @dbname sysname

set @dbname = 'DBLab';

select top 1

          backup_set_id

        , [type]

        , [database]
            = tblBS.[database_name]

        , [description]
            = tblBS.[description]

from   [msdb].[dbo].[backupset] tblBS

where tblBS.[database_name] = @dbname

order by
        backup_set_id desc

Output

msdb__dbo__backupset__20180625__1138PM

Database Backup – Read/Write File Groups

Overview

Add read-write_filegroups to implicitly skip read-only file groups.

Sample


use master
go

backup database [DBLab]
    read_write_filegroups
	to DISK='NUL:'
	with init
	, format

Output

readwritefilegroups__20180625__1148PM

Explanation

  1. Included
    • Data files
      • DBLab, fileVideo_001
    • Log
      • DBLab_log
  2. Not Included (as readonly )
    • fileAudioFile__001

Review Backup Type

Output

readwritefilegroups__backupset_20180625__1152PM

Explanation
  1. Type=P
    • Partial

Database Backup – Specific filegroups

Overview

Use the filegroup marker to explicitly tag filegroups you want included.

Sample


use master
go

backup database [DBLab]
	filegroup='PRIMARY'
	to DISK='NUL:'
	with init
	    , format
	    , description='filegroup explicit ( primary)'

Output

filegroupsTag__20180626_1203AM

Explanation

  1. Included
    • Data files
      • DBLab
    • Log
      • DBLab_log
  2. Not Included (as not part of tagged filegroup)
    • fileAudioFile__001
    • fileVideo_001

Review Backup Type

Output

filegroupsTag__20180626_1205AM

Explanation
  1. Type=F
    • File Groups

backupset.type ( Revised )

Here is a revised mapping of backup operations that includes new file groups subtleties.

Backup Operation File Group Backup Type ( backupset.type)
Database D
Differential I
Transaction L
Partial Read Write file groups only / Skip Read Only file groups P
File Groups or Files  Explicitly listed file groups F

References

  1. Microsoft

SQL Server – Review Database Restore Timeline

Background

Reviewing Database Restore timeline and wasted to share what we have thus far.

 

Metadata

SQL

/*

    a) restorehistory (Transact-SQL)
    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/restorehistory-transact-sql

*/
; with cteRestoreHistory
(
      [restoreHistoryID]
    , [databaseName]
    , [restorer]
    , [restoreDate]
    , [restoreStopAt]
    , [restoreType]
    , [backupSetID] 
    , [sequenceNbr]
)
as
(

    select 
              [restoreHistoryID]
                = tblRH.[restore_history_id]

            , [databaseName]
                = tblRH.[destination_database_name]

            , [restorer]
                = tblRH.[user_name]

            , [restoreDate]
                = tblRH.[restore_date]

            , [restoreStopAt]
                = tblRH.[stop_at]

            , [restoreType]
                = tblRH.restore_type

            , [backupSetID] 
                = tblRH.[backup_set_id] 

            , [sequenceNbr]
                = DENSE_RANK() 
                    OVER 
                    (

                        PARTITION BY 
                            tblRH.[destination_database_name]

                        ORDER BY 
                            tblRH.[restore_date] DESC
                    )

    from   msdb.[dbo].[restorehistory] tblRH


)

, cteRestoreType
(
      [restoreType]
    , [restoreTypeLiteral]
)
as
(
    select 
              'D'
            , 'Database'
    union
    select 
              'F'
            , 'File'
    union
    select 
              'G'
            , 'FileGroup'
    union
    select 
              'L'
            , 'Log'

)
select 
          [database] 
            = tblSD.[name]

        , [dbid]
             = tblSD.[database_id]

        , [restoreInitatedBy]
             = tblRH.[restorer]

        , [restoreDate]

        , [restoreType]
             = tblRH.[restoreType]

        , [restoreTypeDescription]
            = cteRT.restoreTypeLiteral

        , [backupMachineName]
            = tblBS.[machine_name]

        , [backupInitiatedBy]
            = tblBS.[user_name]

        , [databaseVersion]
            = tblBS.database_version

        , [backupFile]
            = tblBMF.physical_device_name

        , [originalBackupStartDate]
            = tblBS.backup_start_date

        , [originalBackupCompletionDate]
            = tblBS.backup_finish_date

        , [originalBackupDuration (in minutes)]
            = datediff
                (   
                      minute
                    , tblBS.backup_start_date
                    , tblBS.backup_finish_date
                )

from   sys.databases tblSD

inner join cteRestoreHistory tblRH

        on tblSD.[name] = tblRH.[databaseName]

inner join msdb..backupset tblBS

    ON tblRH.[backupSetID] = [tblBS].[backup_set_id]

INNER JOIN msdb..backupmediafamily tblBMF
    ON [tblBS].[media_set_id] = [tblBMF].[media_set_id] 

left outer join cteRestoreType cteRT
    on tblRH.[restoreType] = cteRT.restoreType


where tblRH.sequenceNbr = 1

order by

          tblSD.[name]
        , tblRH.[restoreDate] desc


Output

Sample

SQL Server – Backup – When offline filegroups exist

Background

A few months we performed a partial restore.

When we tried to backup the partially restored database we ran into potholes.

Issues

Sample SQL and Error Messages

  1. SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’ TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
    • Error Message :- “Msg 3004, Level 16, State 1, Line 1
      The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.”
    • Corrective Measures
      • SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’, READ_WRITE_FILEGROUPS TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
  2. SQL :- “backup database [QA_rbpivr1] TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’
    • Error Message :- Msg 3007, Level 16, State 1, Line 1
      The backup of the file or filegroup “rbpivrLOB_01” is not permitted because it is not online. Container state: “Vacant” (6). Restore status: 0. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.”
    • Corrective Measures
      • SQL :- “backup database [QA_rbpivr1] FILEGROUP =’PRIMARY’, FILEGROUP =’DATA’, FILEGROUP =’INDEX’, FILEGROUP =’fg_rbpivr1_memory’
        TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’ WITH INIT, FORMAT , STATS=1″

 

Remediation

To backup we have to explicitly specify each filegroup we will like backed up.

And, in doing so, omit the filegroups that are offline.

 

Code

Sample


backup database [QA_rbpivr1]    
FILEGROUP ='PRIMARY'
    , FILEGROUP ='DATA'
    , FILEGROUP ='INDEX'
    , FILEGROUP ='fg_rbpivr1_memory'  
 TO DISK  = 'Z:\SQLBackups\QA_rbpivr1.bak' 
 WITH INIT, FORMAT  , STATS=1

Stored Procedure

 


use [master]
go

if object_id('[dbo].[sp_DBBackupCustomized]') is null
begin

    exec('create procedure [dbo].[sp_DBBackupCustomized] as ')

end
go

alter procedure [dbo].[sp_DBBackupCustomized]
(
      @dbname			sysname
    , @filenameBackup	sysname = null --= 'NUL'
    , @stat				smallint = 1
    , @scriptOnly		bit = 0
    , @debugGUI			bit = 0
)
as
begin

    set nocount on;

    declare @dbid int
    declare @tblFile TABLE
    (

          [id]			int not null identity(1,1)
        , [name]		sysname not null

        , [dataSpaceID] int null

        , [filegroup]	sysname null
                            default ''

        , [type]		int not null
        , [type_desc]	sysname not null

        , [state]		int not null
        , [state_desc]	sysname not null

        , [include]	as 
                        case
                            when (
                                        ( [type] in (0, 2) )
                                    and ( [state] = 0) 
                                 )
                                    then 1
                            else 0
                        end
    )

    declare @tblFileFromDB TABLE
    (
          [id]			int not null identity(1,1)	
        , [name]		sysname
        , [fileid]		int
        , [filename]	sysname 
        , [filegroup]	sysname null
        , [size]		sysname
        , [maxsize]		sysname
        , [growth]		sysname
        , [usage]		sysname
    )

    declare @tblFileGroupFromDB TABLE
    (
          [id]				int not null identity(1,1)
        , [name]			sysname
        , [dataSpaceID]		int
        , [type]			sysname
        , [type_desc]		sysname
    )

    declare @tblFileGroupIncluded TABLE
    (
          [id]		  int not null identity(1,1)
        , [filegroup] sysname not null
    )

    declare @id int
    declare @idMax int

    declare  @cmd nvarchar(255)

    declare  @FORMAT_FILEGROUP_QUERY		nvarchar(4000);
    declare  @FORMAT_BACKUP_CMD			nvarchar(4000);
    declare  @cmdBackup			nvarchar(4000);
    declare  @statAsVarchar     varchar(10)

    declare @fileGroupName		nvarchar(200)
    declare @fileGroupSingle	nvarchar(200)
    declare @fileGroupBuffer	nvarchar(4000)

    declare @fileGroupBufferNotEmpty bit

    declare @CHAR_NEWLINE		varchar(10)
    declare @CHAR_QUOTES_SINGLE	varchar(10)
    declare @CHAR_COMMA			varchar(10)

    DECLARE @pathBackupDefault NVARCHAR(4000) 

    set @CHAR_NEWLINE = char(13) + char(10)
    set @CHAR_QUOTES_SINGLE = ''''
    set @CHAR_COMMA	  = ' , '
        
    set @fileGroupBufferNotEmpty = 0

    set @FORMAT_BACKUP_CMD=
            'backup database [%s] '
                + ' %s ' -- FILEGROUP
                + ' TO DISK '
                + ' = ''%s'' '
                + @CHAR_NEWLINE + @CHAR_NEWLINE
                + ' WITH INIT, FORMAT '
                + ' , STATS=%s'
                
    set @FORMAT_FILEGROUP_QUERY
            = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    set @dbid = db_id(@dbname);

    if (@filenameBackup is null)
    begin

       EXEC master.dbo.xp_instance_regread 
                      N'HKEY_LOCAL_MACHINE'
                    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                    , N'BackupDirectory'
                    , @pathBackupDefault OUTPUT
                    , 'no_output' 

        set @filenameBackup = @pathBackupDefault + '\' + @dbname + '.bak'

    end

    insert into @tblFile
    (

          [name]
        , [dataSpaceID]

        , [type]
        , [type_desc]

        , [state]
        , [state_desc]

    )
    select 
              tblSMF.[name]
            , tblSMF.[data_space_id]

            , tblSMF.[type]
            , tblSMF.[type_desc]

            , tblSMF.[state]	
            , tblSMF.[state_desc]

    from   sys.master_files tblSMF

    where  (

                    --( tblSMF.[state] = 0 )
                    ( tblSMF.[database_id] = @dbid )

           )	

    --has_dbaccess(@dbname)
    --print N' '
    --select @cmd = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    exec master.sys.xp_sprintf
              @cmd output
            , @FORMAT_FILEGROUP_QUERY
            , @dbname

    insert into @tblFileGroupFromDB
    (
          [name]		
        , [dataSpaceID]		
        , [type]	
        , [type_desc]	
    )
    exec (@cmd)

    update tblF
    
    set [filegroup] = isNull(tblFGFDB.[name], '')

    from   @tblFile tblF

    inner join @tblFileGroupFromDB tblFGFDB

            on tblF.[dataSpaceID] = tblFGFDB.[dataSpaceID]

    if (@debugGUI=1)
    begin

        select 
                 [src] = '@tblFile'
                , tblF.*

        from   @tblFile tblF

    end

    set @statAsVarchar = cast(@stat as varchar(10));

    set @id = 1
    set @idMax = ( select max([id]) from @tblFile)

    set @fileGroupBuffer = '';
    set @fileGroupBufferNotEmpty = 0;

    while (@id <= @idMax)
    begin

        set @fileGroupName = null

        select @fileGroupName = tblF.[filegroup]
        from   @tblFile tblF
        where  tblF.id = @id
        and    tblF.[include] = 1
        and    not exists
                    (
                        select 1
                        from   @tblFileGroupIncluded tblFGI
                        where  tblFGI.[filegroup] = tblF.[filegroup]
                    )

        if (@fileGroupName is not null)
        begin

            set @fileGroupSingle = 'FILEGROUP =' 
                                    + @CHAR_QUOTES_SINGLE 
                                    + @fileGroupName 
                                    + @CHAR_QUOTES_SINGLE;
            
            if  (@fileGroupBufferNotEmpty = 1)
            begin

                set @fileGroupBuffer = isNull(@fileGroupBuffer, '') 
                                            + @CHAR_NEWLINE
                                            + @CHAR_COMMA

            end
            else
            begin

                set @fileGroupBuffer = '  ' 
                                        + isNull(@fileGroupBuffer, '') 
                                        + @CHAR_NEWLINE

            end

            set @fileGroupBuffer = @fileGroupBuffer + @fileGroupSingle;

            set @fileGroupBufferNotEmpty = 1

            insert @tblFileGroupIncluded
            (
                [filegroup]
            )
            values
            (
                @fileGroupName
            )

        end

        set @id = @id + 1

    end

    exec master.dbo.xp_sprintf
              @cmdBackup output
            , @FORMAT_BACKUP_CMD
            , @dbname
            , @fileGroupBuffer
            , @filenameBackup
            , @statAsVarchar

    print '@cmdBackup ' + @CHAR_NEWLINE + @cmdBackup

    if (@scriptOnly =0)
    begin

        exec(@cmdBackup)

    end

end

go




Guillaume Fourrat – VSS backup of AlwaysOn Secondaries

Background

Catching up a bit on other people’s posting and wanted to share this one from Guillaume Fourrat.

Guillaume titles it “VSS backup of AlwaysOn Secondaries” and it is here.

 

VSS backup of AlwaysOn Secondaries

 

Hi Everyone,

Today I’m going to highlight one of the changes brought by SQL Server 2012 SP2, which is the way we handle VSS Backup requests on AlwaysOn Secondary Databases.

Until now, any request for a FULL database backup (VSS_BT_FULL) thru VSS against a DB that is an AlwaysOn secondary was failing by design. Our VSS Writer SQLWriter would return FAILED_AT_PREPARE_SNAPSHOT (0x800423f4 – VSS_E_WRITERERROR_NONRETRYABLE).

A copy-only VSS backup (VSS_BT_COPY) would work.

The rationale being the following: a FULL backup is actually updating the target DB (reset of differential bitmap mainly), which is not possible when the DB is read only. Furthermore, because of the failover possibilities introduced by AlwaysOn, the favored option was to use Native SQL Servers backup that could rely on SQL Server variable backup location (http://msdn.microsoft.com/en-us/library/hh245119.aspx) if needed, and be ‘alwayson –aware’.

So that could be the end of the story: against an AlwaysOn Secondary DB, either use Copy_only VSS backups or use native backups.

But of course that wouldn’t make for a very interesting blog post…

Enters HyperV…

Consider the following scenario:

Large Windows HyperV Servers, hosting many HyperV virtual Servers, some of them SQL Servers in Always On architecture.

In short: a Private Cloud.

In this context, the IT usually takes care of the infrastructure at host level, and lets users deal with whatever happens within the VMs. One of the key tasks of IT is to manage backups (eg. for disaster recovery at datacenter level, or to provide restore of single VMs).

And the mainstream way to do that is to take VSS backups of the Host Disk Volumes. Microsoft System Center DPM will do exactly that.

But VSS backups are all about taking backups that are consistent: in ‘standalone’ SQL Server context you may already know all the logic SQLWriter implements to make sure that IO against the Databases that are backed up are frozen during the snapshot operation. So, back to our HyperV context, collecting a point-in-time image of a VHD without bothering with what happens within the VM would be defeating that very purpose right?

So what happens is the following: the VSS backup is propagated to Guest VMs thru HyperV integration services. That propagation hardcodes the backup type to VSS_BT_FULL, and therefore all guest VMs are initiating a VSS backup/snapshot in their own context. The purpose is to make sure that all applications are quiesced within all running VMs at the time we take the snapshot at the host level. This will enable us to generate a consistent backup of running VMs.

But let’s now put this in the context where one of the VMs is running an AlwaysOn secondary DB: you guessed it, it’s not going to work:

clip_image002

The important thing to know here is that the error returned by SQLWriter in VM3 will actually bubble up all the way to the initial VSS backup command at Host level, and will make it fail as a whole.

So we ended up in a situation where the IT infrastructure folks would see their Host backups failing from time to time for an unknown reason, depending on whether one or more of the VM present on the Host Drive being backup up had a secondary AlwaysOn DB! It could be that the AlwaysOn AG spans different HyperV hosts and therefore that the presence of a Secondary DB on a given Host is not something static over time.

Because of the complexity of the whole call chain, and because infrastructure IT operators may not have any visibility (or understanding) of the VM content, you can imagine what kind of troubleshooting challenges this would offer… And even when the situation is understood, well, what do we do? If host level backup must be somehow manually synchronized to the applicative state of Guest VMs, the Private Cloud scenario becomes quite more complicated all of a sudden.

This is the reason why SQL Server 2012 SP2 ships a code change for SQLWriter that will implement the following:

clip_image004

As you can see, SQLWriter now detects this specific situation and changes the backup type to VSS_BT_COPYONLY. This will only happen for VSS_BT_FULL backups against AlwaysOn secondary Dbs. VSS_BT_FULL backups against primary DB will happen without change.

In this case, the VSS backup will now successfully complete in VM3 and the host-level backup success state will no longer be tied to guest VM’s AlwaysOn activity. Private Cloud scenario unlocked!

Important note: the fact that VSS backup of AlwaysOn secondaries now works does not make it the preferred solution to backup SQL Server AlwaysOn architectures. The main purpose of the SP2 change is to avoid a situation where a single SQL Server in a VM fails a complete host-level backup operation that encompassing dozens of VMs.

The resulting backup for the VM hosting SQL should be considered a Disaster Recovery one, where AlwaysOn will be removed at once at restore time, not as a way to rebuild a subset of the nodes for an AlwaysOn Availability group. And for regular databases within the VM, that backup is as good any regular VSS one.

Finally, SQL Server 2012 SP2 only contains a partial fix for this issue. Servers running case-sensitive sort orders will require SQL Server 2012 SP2 Cumulative Update 2.

HTH,

Guillaume Fourrat
SQL Server Escalation Engineer
Microsoft France

Commentary

I really like the post for the reasons listed below:

  1. It is not something about a stupid query that can not be written a different way as it is embedded in the code or generated via an ORM Tool ( Entity Framework, Hibernate )
    • That is, it can be changed and optimized
    • Furthermore, we are not handicapped by what the developer thinks is fanciful and should be upheld by all means
  2. It affects a whole lot of parties
    • 3rd Party tools vendors that utilize VSS to backup SQL Server databases
    • Corporations that do not know or care what VSS is or does, they do not have a DBA and could care less that sql server datafiles can not just be backed up like other files
  3. The Application logs an error
    • The application actually logs the error and it is the hope that support staff will capture & review the errors periodically
  4. MSFT can and has provided a fix
    • MSFT has provided a fix and that means they are aware and tracking it
    • The patch will be brought into the main line code and all future versions will benefit going forward
    • If it breaks in subsequent version, we have every right to open a ticket thru Corporate Support or as a Connect Item
  5. And, above all else this is an actual technical problem
    • It is not a process problem
    • It is not a people problem
    • It is not one handicapped by long institutional or inter-department slight or grievance

SQL Server – Database Restore – Errors – “C:\ClusterStorage\”

Background

Was reviewing SQL Server Agent logs earlier this week and found errors resulting from a database restore SSIS Package.

 

Error

Error Text

The error looks like the one below:


Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'IND_157GIC126_Data' cannot be restored to 'C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\IND_157GIC126_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'IND_157GIC126_Log' cannot be restored to 'C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\IND_157GIC126_Log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

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


Error Tabulated

Message ID Sample Explanation
 Msg 5133 Directory lookup for the file “C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf” failed with the operating system error 3(The system cannot find the path specified.). Review the designated folder name and ensure it desired.

If so create and/or grant SQL Server Engine permission.

 Msg 3156  File cannot be restored. Use with move to Identify a valid location for the file Please add the move option to the restore command
 Msg 3199  Problems were identified while planning for the RESTORE statement  Flags errored restore operations
 Msg 3013  RESTORE DATABASE is terminating abnormally.  Restore failed abnormally

 

 

 

TroubleShooting

The error is of course the fact the on the originating SQL Instance the database files are located on C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA  ( Data Folder ) and C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data   ( Log Folder ).

 

Remediate

The fix is to create the folders ahead of time

Stay on Originating Folder

Create Folders

exec master.dbo.xp_create_subdir 'C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\'
exec master.dbo.xp_create_subdir 'C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\'

 

Restore Database


restore database [IND]
from   disk = 'C:\TEMP\IND.bak'

 

Use Own Folders

Get File groups from DB Backup File

restore filelistonly
from disk = 'C:\TEMP\IND.bak'</pre>

Create Folders

exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLServer\Datafiles\'
exec master.dbo.xp_create_subdir 'F:\Microsoft\SQLServer\Logfiles\'

Use Move Filegroup


restore database [IND]
from   disk = 'C:\TEMP\IND.bak'
with
	  move 'IND_Data' to 'E:\Microsoft\SQLServer\Datafiles\IND_Data.mdf'
	, move 'IND_Log' to  'F:\Microsoft\SQLServer\Logfiles\IND_Log.ldf'
	, replace
	, stats=1

 

Ola Hallengren – DatabaseBackup – Missing Indexes

Background

I am really high on Ola Hallengren’s Database Utility Scripts and I have wanted to get this down for a while now.

 

Performance

 

Missing Indexes

Missing Indexes can be a drag on system performance.

Unfortunately, there are a couple of missing indexes on the msdb.dbo.backupset table.

 

Code

Programmable

dbo.DatabaseBackup

Code Snippet

 


	DECLARE @CurrentDifferentialBaseLSN numeric(25,0)
	DECLARE @CurrentDatabaseName		sysname
	DECLARE @CurrentDatabaseID int

	DECLARE @Version numeric(18,10)
	DECLARE @AmazonRDS bit

	DECLARE @CurrentDifferentialBaseIsSnapshot bit

	SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.'
                          + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))
                          - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

	SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END

	SET @CurrentDatabaseName = db_name()
	SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)



	SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
    FROM sys.master_files
    WHERE database_id = @CurrentDatabaseID
    AND [type] = 0
    AND [file_id] = 1

    -- Workaround for a bug in SQL Server 2005
    IF @Version >= 9 AND @Version < 10
    AND EXISTS
    (
           SELECT * FROM sys.master_files 
           WHERE database_id = @CurrentDatabaseID 
           AND [type] = 0 AND [file_id] = 1 
           AND differential_base_lsn IS NOT NULL 
           AND differential_base_guid IS NOT NULL 
           AND differential_base_time IS NULL
    )
    BEGIN
    
          SET @CurrentDifferentialBaseLSN = NULL


    END

    SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot
    FROM   msdb.dbo.backupset
    WHERE  database_name = @CurrentDatabaseName
    AND    [type] = 'D'
    AND    checkpoint_lsn = @CurrentDifferentialBaseLSN

 

Missing Index

checkpoint_lsn_20161203_0857pm

 

Index Creation Statement

USE [msdb]
GO

CREATE NONCLUSTERED INDEX [INDX_CheckpointLSN_Type_DatabaseName]
ON [dbo].[backupset] 
(
	  [checkpoint_lsn]
	, [type]
	, [database_name]
)

GO


 

 

Code Snippet – INDX_DBA_DatabaseName_IsDamaged
Query Plan

queryplan_20170216_1226pm

Script
/*
Missing Index Details from sql01.master )
The Query Processor estimates that implementing the following index could improve the query cost by 92.1703%.
*/

/*
USE [msdb]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[backupset] ([database_name],[is_damaged])
INCLUDE ([database_backup_lsn],[backup_finish_date],[type])
GO
*/

use [msdb]
go

if object_id('[dbo].[backupset]') is not null
begin

	print 'Reviewing [dbo].[backupset] .... '

	/*
		Query:

			declare @CurrentDatabaseName    sysname
			declare @CurrentLatestBackup	datetime
			declare @CurrentDifferentialBaseLSN numeric(20, 1)

			set @CurrentDatabaseName = 'msdb'
			set @CurrentDifferentialBaseLSN = 1

			BEGIN        

				SELECT @CurrentLatestBackup = MAX(backup_finish_date)        
				FROM   msdb.dbo.backupset        
				WHERE (
							   [type] IN('D','I')        
							OR database_backup_lsn < @CurrentDifferentialBaseLSN
					  )        
				AND is_damaged = 0        
				AND database_name = @CurrentDatabaseName

			END

	*/

	/*

		CREATE NONCLUSTERED INDEX [INDX_DBA_DatabaseName_IsDamaged]
		ON [dbo].[backupset] ([database_name],[is_damaged])
		INCLUDE ([database_backup_lsn],[backup_finish_date],[type])

	*/

	if not exists
	(
		select *
		from   sys.indexes tblSI
		where  tblSI.object_id = object_id('[dbo].[backupset]')
		and    tblSI.[name] = 'INDX_DBA_DatabaseName_IsDamaged'
	)
	begin

		print 'Creating Index INDX_DBA_DatabaseName_IsDamaged ...'

		CREATE NONCLUSTERED INDEX [INDX_DBA_DatabaseName_IsDamaged]
		ON [dbo].[backupset] 
		(
			  [database_name]
			, [is_damaged]
		)
		INCLUDE 
		(
			   [database_backup_lsn]
			 , [backup_finish_date]
			 , [type]
		)
		with
		(
			FILLFACTOR=80
		)
		print 'Created Index INDX_DBA_DatabaseName_IsDamaged'

	end

	print 'Reviewed [dbo].[backupset]'

end

go