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 »
PowerShell script to quickly change all Recovery Options to “Restart the Service”. https://gist.github.com/benf101/a3c7020c0a582aca06059b750bd08528
Creating and writing to output file in PowerShell.
This is a PowerShell script that puts out the XML required to update your Red Gate MultiScript servers. Manually entering the SQL instances from within the application can be a daunting task if you have a large number to do. This script will have you up and running in seconds. You must first have a… Read More »
When using AlwaysOn, you must connect using your listener name, not your node name. If you connect with the node name, it will only work until there is a failover, which would defeat the purpose of your High Availability setup, right?
While this seems like a simple thing to do, I recently discovered so many ways not to do it (also known as failing) that I just had to document this one.
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; }
This was made possible by the Scripting Guy. I have boiled it down to the answer. If you want to use PowerShell to query for the log on account that normally is displayed in the services.msc service properties window: Here is the PowerShell script: $server_name = "name_of_server"; $services = Get-WmiObject win32_service -ComputerName $server_name… Read More »
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];
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 and get on with your… Read More »