I thought the only way to get Deadlock info out of SQL was to enable trace flags 1204 and 1222 (see http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx) but this isn’t actually the case . ..  behold  .  . .

SELECT
 --first resource
 DB_NAME((CAST (XEventData. XEvent.value ('(data/value)[1]' , 'varchar(max)' ) AS XML)).value ('(deadlock/process-list/process/@currentdb)[1]' , 'int') ) as process1,
 --lockmode
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/@lockMode)[1]' , 'varchar(1)') as lockmode,
 --what sql was running
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/inputbuf)[1]' , 'varchar(max)') AS text ,
 
 
 --second resource
 DB_NAME((CAST (XEventData. XEvent.value ('(data/value)[1]' , 'varchar(max)' ) AS XML)).value ('(deadlock/process-list/process/@currentdb)[2]' , 'int') ) as process2,
 --lockmode
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/@lockMode)[2]' , 'varchar(1)') as lockmode,
 --what sql was running
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/inputbuf)[2]' , 'varchar(max)') AS text ,
 
 
 XEventData .XEvent. value('(@timestamp)[1]' , 'datetime' ) as Timestamp,
 
 
 --the xml
 CAST(XEventData .XEvent. value('(data/value)[1]' , 'varchar(max)' ) AS XML) AS DeadlockGraph
 
 
FROM
 (
 SELECT
 CAST(target_data AS XML ) AS TargetData
 FROM
 sys.dm_xe_session_targets st
 JOIN sys .dm_xe_sessions s
 ON s. address = st. event_session_address
 WHERE
 name = 'system_health'
 ) AS Data
 CROSS APPLY TargetData.nodes ('//RingBufferTarget/event' ) AS XEventData ( XEvent )
WHERE
 XEventData .XEvent. value('@name' , 'varchar(4000)' ) = 'xml_deadlock_report'

 Try it 😀

Using XP_CMDSHELL we can run commands that could otherwise be run using Windows Scheduled Tasks. It is kick ass, what makes this even more kick ass is being able to capture the errors running these commands produce.

Below is an example of a simple script which runs a XP_CMDSHELL command and sends an email in the event of any failure. Dare I say this is even quick and dirty . . .

use msdb

declare @cmd_to_run varchar (1500)
declare @return_code int

--The command you want to run
select @cmd_to_run = '<<INSERT YOUR OWN COMMAND>>'

-- Run the command, capture what int code is returned
exec @return_code = master.dbo .xp_cmdshell @cmd_to_run

-- Returning a 0 means there is a problem, send an email if 0 is returned 
if @return_code <> 0
begin
       print 'FAILED'
       exec sp_send_dbmail
            @profile_name = '<<PROFILE NAME>>' ,
            @recipients = '<<RECIPIENTS>>' ,
            @importance = 'high' , --somethings failed dudes, its always high importance
            @subject = '<<SOMETHING MEANINGFUL TO YOU>>' ,
            @body = ''<<SOMETHING MEANINGFUL TO YOU>>'
end

 

 

What does parallelism look like in SQL? How would you know when SQL is doing it parallel style? When looking at sys.sysprocesses you might see something like the below,

CXPACKET_SYSSYSPROCESSES

Here we see many instances of the CXPACKET waittype running seemingly from one spid. The CXPACKET waittype pops up whenever a thread running in parallel is waiting on another thread to complete before the overall process can complete. If you’re seeing CXPACKETs then you’re seeing parallelism. If you see CXPACKETs persist, then there might be an issue slowing down your query, but thats for another story . . . (more info on this waittype here)

The most obvious way to see parallelism is in the query plan. As shown below,

parallel_query_plan

You see this, you’re seeing parallelism :) Painless.

The script below will generate an email if a database has had a full backup in the last 7 days, listing the database and the last backup in a table. This could be useful to flag up databases which failed to be backed up during a job or just for auditing/informational purposes. This script is attached to a job and is scheduled to run every after the full backup maintenance plan has run to inform the SQL team of any databases missing a bak.

-- any full backup failures?
declare @failureCount         int
set @failureCount =  1

if exists (

select null
from sys .databases as d
left join msdb.dbo.backupset as bs
on d. name = bs .database_name
where is_copy_only = 0 and type in ('D' )
group by database_name
having      datediff (D, max( BACKUP_FINISH_DATE), getdate()) > 7
)
                         begin
                               set @failureCount = 0

                         end

--if a full backup is missing, run the following 
if (@failureCount = 0)
begin

declare @bodyText              nvarchar(max )
declare @bodyLine              nvarchar(512 )

declare @databaseName         nvarchar( 128)
declare @@lastbak             nvarchar(128 )

-- populate mail body top
print 'populate table'
set @bodyText = N'<html><body>'
set @bodyText = @bodyText + N'<style> type="text/css" body {font-family:"Calibri";font-size:"10pt";} </style>'
set @bodyText = @bodyText + N'The following databases did not get a full backup on %s - verify is this is correct:'
set @bodyText = @bodyText + N'<br><br><table border="1">'
set @bodyText = @bodyText + N'<tr><th>Database Name</th><th>Last Backup</th></tr>'
set @bodyText = replace( @bodyText,N'%s' ,@@servername)

-- cursor for disabled jobs
print 'starting cursor'

declare csrDatabases cursor fast_forward
for

select
       cast(bs . database_name as nvarchar( 128)) ,
       cast(max ( bs. backup_finish_date ) as nvarchar (128))
from
       sys . databases as d
       left join msdb. dbo.backupset as bs
on
      d . name = bs .database_name
where
      is_copy_only = 0 and type = 'D'
group by
      database_name
having
       datediff(D , max (BACKUP_FINISH_DATE), getdate()) > 7
order by
      database_name

open csrDatabases 
fetch next from csrDatabases
into @databaseName, @@lastbak

-- populate mail html table with disabled jobs
while @@fetch_status <> -1
begin

       set @bodyLine = N'<tr><td>%1</td><td>%2</td></tr>'
       set @bodyLine = replace( @bodyLine,N'%1' ,@databaseName)
       set @bodyLine = replace( @bodyLine,N'%2' ,@lastbak)
       set @bodyText = @bodyText + @bodyLine

       fetch next from csrDatabases
       into @databaseName, @@lastbak

end

close csrDatabases 
deallocate csrDatabases 
print 'cursor ended'

-- populate mail body bottom
set @bodyText = @bodyText + N'</table>'
set @bodyText = @bodyText + N'</body></html>'

-- send mail
print 'start sending mail'
exec sp_send_dbmail
       @profile_name = 'profile_name',
      @recipients = 'email_address' ,
      @importance = 'high' ,
      @subject = 'BACKUP - FULL - MISSING ,
      @body  = @bodyText,
      @body_format = 'html'
print 'finish sending mail'
end

go

Note, with regard to backup ‘type’

  • ‘D’ denotes a full backup
  • ‘I’ denote a differential
  • ‘L’ denotes a log backup

This script could be easily adapted to check differential’s and log files.

You know the score. You set up a maintenance plan to backup all or most of your databases on an instance then you get an email saying the job has failed. Doesn’t mean they all didn’t backed up . . . Get a list of the last backup for every database in your instance, backup only the one which failed to backup.

Change the type of backup to ‘D’ to get the last full and ‘I’ to get the last differential

select bs. database_name, bs .type, max( bs. backup_finish_date ) AS Latest_bak
from sys .databases as d
left join msdb.dbo.backupset as bs
on d. name = bs .database_name
where is_copy_only = 0 and type = 'D'
group by database_name, type
order by database_name

Use this T-SQL to find this out and kick ’em off 😉 !

select name
from master.. syslogins 
where sysadmin = '1'

Last post on BCP tonight promise  . . .

There are *many* ways of exporting an XML file from SQL, adding the section in bold below to the select in a BCP command will create XML from the search results.

BCP "select * from table FOR XML AUTO,TYPE, ELEMENTS " QUERYOUT "C:\TEST.XML" -c -t -T -S server

 

 

Ta-Dah! The  switches below in the BCP statement export the results of the query to CSV

BCP "select * from table" QUERYOUT "C:\TEST.csv" -c -t"," -r"\n" -T -S server

:-)

 

 

 

Query plans possess vital information which can help a DBA to make optimization decisions.

The T-SQL below will retrieve the query plan for database of your choice. By extending the where clause in the sub query you can be more precise about the query plans you return based on particular commands for example. The sub query could even be replaced with your own SPID.

select query_plan
from sys .dm_exec_requests as er
cross apply sys. dm_exec_query_plan(er .plan_handle)
where session_id in ( select spid from sys. sysprocesses where dbid = DB_ID('database_name' ))

Happy viewing :)

 

 

 

Insert your query and away you go :-)

EXEC msdb. dbo.sp_send_dbmail    
@profile_name = '<<NAME>>' ,    
@recipients = '<<RECIPIENTS>>' ,    
@query = '<<QUERY>>' ,      
@attach_query_result_as_file = 1,      
@query_attachment_filename = 'sample.csv' ,     
 @query_result_separator = '   ' ,      
@query_result_header = 1,      
@query_result_no_padding = 1,      
@exclude_query_output = 1,    
@subject = '<<SUBJECT>>'