This script will return all SQL instances running on a server or list of servers. cls Import-Module -Name SQLPS -DisableNameChecking $servers = “SERVER_NAME”, “SERVER_NAME_2”, “ETC”; foreach($server in $servers) { $path = “SQLServer:\SQL\” + $server; $instances = Get-ChildItem -Path $path; $instances; }

More
  • 2016-04-27

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

More
  • 2016-04-14

This is a bit of a scary task.  Someone tells you about 87 databases that all need to be restored from the most recent FULL backups. You can either spend the rest of your day pointing and clicking, or you can use some PowerShell power to crank out the script...

More
  • 2016-04-14