Currently Browsing: SQL Tips and Tricks

SQL Server / Extended Events / Finding Deadlocks

SQL Server / Extended Events / Finding Deadlocks
Recently we experienced deadlocks on one of our staging servers, so I wanted to capture those deadlocks using Extended Events. This how I did it. First of all you need location where you gonna keep your XEL file, then we can create Extended Event session Using SSMS Creating new Extended Event session   Selecting Events you want to capture, in this case xml_deadlock_report   Save the event data to an XEL file   Using T-SQL CREATE EVENT SESSION [Deadlock_Report] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET...
read more

Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)

Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)
“Operating system error 32 (The process cannot access the file because it is being used by another process)” message in SQL Server error log BackupDiskFile::OpenMedia: Backup device ‘\\NAS\Shared_LogShip\DB_log.trn’ failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.). If you are using shared NAS for your logs backup location for the primary server and you restoring those logs directly to the secondary server (without copy job) you can face this kind of issues.  If backup and restore job are scheduled at the...
read more

SQL Server / Upgrading the Secondary Server Instances (STANDBY)

SQL Server / Upgrading the Secondary Server Instances (STANDBY)
If you planning to upgrade to the new SQL Server Version 2016 or 2017 and if there is log shipping in place between those servers you should follow some upgrading order. Always upgrade the secondary SQL Server instances first, before upgrading the primary server instance. If you do the opposite log shipping will fail because a log backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server. But this is not a reason why I am writing this post. I am writing to alert you that in case you have a secondary database configured by using RESTORE WITH STANDBY,...
read more

Next Entries »