How to use the Sqldumper.exe utility to generate a dump file in SQL Server 2008 in T-SQL

If you need to generate a full memory dump, automatically when a specific SQL Alert occurs, you can use the script T-SQL below


declare @pid int
declare
@str varchar(1024)
declare @dumpdir varchar(255)
declare @sqlbindir varchar(255)

set @dumpdir = 'C:\'
set @sqlbindir = 'C:\Program Files\Microsoft SQL Server\100\Shared\'
set @pid=convert(int, SERVERPROPERTY('ProcessID'))
set @str = 'cd ' + @sqlbindir + '&& "sqldumper.exe" '+convert(varchar(10), @pid)+' 0 0x0800 0 ' + @dumpdir

exec xp_cmdshell @str

 More detail about SQLDumper option below :


Usage: SqlDumper.exe [ProcessID
[ThreadId [Flags[:MiniDumpFlags] [SqlInfoPtr [DumpDir [ExceptionRecordPtr [ContextPtr [ExtraFile]]]]]]]]
[-I<InstanceName>]
[-S<ServiceName>]
[-remoteservers:[print|dump|freeze|resume|remote:guid\dumporigin\signature\localId\port\operationType]]

  Flags:
    dbgbreak            = 0x0001
    nominidump          = 0x0002
    validate_image      = 0x0004
    referenced_memory   = 0x0008
    all_memory          = 0x0010
    dump_all_threads    = 0x0020
    match_file_name     = 0x0040
    no_longer_used_flag = 0x0080
    verbose             = 0x0100
    wait_at_exit        = 0x0200
    send_to_watson      = 0x0400
    defaultflags        = 0x0800
    maximumdump         = 0x1000
    mini_and_maxdump    = 0x2000
    force_send_to_watson= 0x4000
    full_filtered_dump  = 0x8000

  MiniDumpFlags:
    Normal                           = 0x0000
    WithDataSegs                     = 0x0001
    WithFullMemory                   = 0x0002
    WithHandleData                   = 0x0004
    FilterMemory                     = 0x0008
    ScanMemory                       = 0x0010
    WithUnloadedModules              = 0x0020
    WithIndirectlyReferencedMemory   = 0x0040
    FilterModulePaths                = 0x0080
    WithProcessThreadData            = 0x0100
    WithPrivateReadWriteMemory       = 0x0200
    WithoutOptionalData              = 0x0400
    WithFullMemoryInfo               = 0x0800
    WithThreadInfo                   = 0x1000

Michel Degremont| Microsoft EMEA
Product Support Services Developer -SQL Server Core Engineer |