Category Archives: SQL Server

SQL Server Maintenance Plan Backups Failing On Secondary Replica in High Availability Environment

When a High Availability (HA) environment failed over, on the node that became Secondary, the backup jobs did not gracefully handle the change. The Maintenance Plans are supposed to intelligently determine whether each database is Primary or Secondary in an HA environment, and skip the backup if it is not Primary.  This check actually fails… Read More »

TSQL – Make Excel Link In Output

Here is a situation I just came across (boiled down and simplified).  An end user was having to manually find a user id, navigate to an image folder, and then search through hundreds of images to find the one corresponding with that user.  Sure, they were in numerical order, but still, what a pain!  He wanted to… Read More »

TSQL – Get All Queries Currently Running

This is perhaps my most commonly used query when troubleshooting. From SQL Server DMV’s in action ebook. The output shows the spid (process identifier), the ecid (this is similar to a thread within the same spid and is useful for identifying queries running in parallel), the database name, the user running the SQL, the status… Read More »

T-SQL – Get Currently Running Jobs With Duration

Query to get all currently running jobs with their run duration in seconds. Source: http://www.sanssql.com/2013/08/t-sql-query-to-find-currently-running.html SELECT [J].[name] AS [Running_Jobs] , [JA].[Start_execution_date] AS [Starting_time] , DATEDIFF(ss, [JA].[Start_execution_date], GETDATE()) AS [Has_been_running(in Sec)] FROM [msdb].[dbo].[sysjobactivity] [JA] JOIN [msdb].[dbo].[sysjobs] [J] ON [J].[job_id] = [JA].[job_id] WHERE [job_history_id] IS NULL AND [start_execution_date] IS NOT NULL ORDER BY [start_execution_date];