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

 

 

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>>'

Its important for security to monitor unused logins on SQL. Only logins which need to be there should be there. There are a number of ways to monitor the which logins are using used but these methods vary across the versions of SQL, it can be handy to have a basic standard method which a DBA is confidence possesses interoperability across all versions of SQL. Below i’ve outlined a really simple way the sys.sysprocesses table can be used to capture logon data which could be used to find logins which aren’t being used

1) Create a table to hold the audit data

create table record_login_user_behaviour (

loginame         varchar(50 ),
cmd              varchar(200 ),
database_name    varchar(50 ),
frequency        int,
date_captured    datetime

)

2) Create a stored procedure to capture login names of currently running processes. I included a count of the commands being used and the time, you may want to tweak for your needs.

USE <<database>>
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure mon_record_login_user_behaviour

as
set nocount on

insert into record_login_user_behaviour
select distinct loginame,cmd , DB_NAME (DBID) as database_name , COUNT(loginame ) as frequency, GETDATE() as date_captured
from sys .sysprocesses
where loginame <> 'sa'
group by loginame, cmd , dbid
GO

3) Attach capture procedure to job to regularly grab what logins are being used.

4) The script below will compare what logins have been captured as being used against the logins on SQL and produces a list of what what logins haven’t been used.

select distinct name
from sys .server_permissions as spe
inner join sys. server_principals as spr
on spe. grantee_principal_id  = spr .principal_id
where name not in (select distinct loginame from record_login_user_behaviour )

Any ways of improving this activity would be gratefully recieved :-)

 

 

Sometimes you want to make changes across all tables in a particular database.

For example, lets say you’ve got a database that was replicated and now its not and you want to strip the msrepl_tran_version column. During the replication process a column is added to all tables to ensure that there is uniqueness across all the rows. When replication is stopped there  is no longer a need to have this column, in fact its confusing being there  because it doesn’t do anything. The T-SQL below will generate script which when run in a query window will alter all the table in a particular database – it will drop the msrepl_tran_version to be precise,

USE <<DATABASE>>
SELECT
 'ALTER TABLE [' + o.[name] + '] DROP COLUMN [msrepl_tran_version];'
FROM
SYS.OBJECTS AS o
INNER JOIN SYS.COLUMN AS c
ON o.OBJECT_ID = c.OBJECT_ID
AND [name] = 'msrepl_tran_version'
WHERE
o.type = 'u'