Read SQL deadlock information from Extended Events Log

Analysis Deadlock from Extended Event Log is difficult.
It will give result in XML Code


Do google and found out script to convert the XML code to table format so we can easy analysis the deadlock information


SQLServerCentral.com - Reading Deadlock Trace
http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx


we paste the xml format get from the extended event log;
1) SSMS- >Servername->Management->Extended Events-> Session-> system_health->Package0.event_file
2) Right click -> View Target data
3) filter with xml_deadlock_report
4) in the row return with xml_deadlock_report, click on detail window -> values
    xml data will be appear in another window
5) copy the deadlock graph xml data and add at the start of xml file and to the end of the xml data
6) pass the xml into "put your deadlock graph here"
http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx
declare @deadlock xml set @deadlock = 'put your deadlock graph here'
select [PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'), [DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'), [KeylockObject] = Keylock.Process.value('@objectname', 'varchar(200)'), [Index] = Keylock.Process.value('@indexname', 'varchar(200)'), [IndexLockMode] = Keylock.Process.value('@mode', 'varchar(5)'), [Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end, [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'), [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'), [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'), [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'), [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'), [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'), [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'), [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
 from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
      LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/keylock') as Keylock(Process)
        ON Keylock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') = Deadlock.Process.value('@id', 'varchar(50)')

SQL Reporting database (SSRS) moving/migration- note

Back Up and Restore Reporting Services Encryption Keys An important part of report server configuration is creating a backup copy of the symmetric key used for encrypting sensitive information. A backup copy of the key is required for many routine operations, and enables you to reuse an existing report server database in a new installation.

refer to the above link for how to back up and restore reporting services encryption keys.

Moving the Report Server Databases

Both the reportserver and reportservertempdb databases must be moved or copied together

• Schedules will be recreated the first time that you restart the Report Server service.
• SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.
• Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.
• Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

 refer to the above link for more information

Create the RSExecRole

Information from the article
Reporting Services uses a predefined database role called RSExecRole to grant report server permissions to the report server database. The RSExecRole role is created automatically with the report server database. As a rule, you should never modify it or assign other users to the role. However, when you move a report server database to a new or different SQL Server Database Engine, must re-create the role in the Master and MSDB system databases.

The article has instruction how to re-create the role in the Master and MSDB system databases using SSMS gui

The below scripts copy from the above article for my reference

use [master]
GRANT EXECUTE ON [sys].[xp_sqlagent_enum_jobs] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_is_starting] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_notify] TO [RSExecRole]
use [msdb]
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_verify_job_identifiers] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [RSExecRole]
GRANT SELECT ON [dbo].[syscategories] TO [RSExecRole]
GRANT SELECT ON [dbo].[sysjobs] TO [RSExecRole]