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 »
Maybe this is a one-off type of thing, but if you need to see every folder within a directory and get the list of who can access that folder, it can be done using PowerShell. The output is tab-separated and can easily paste into an Excel sheet. cls cd \\DIRECTORY_NAME\CHANGE_THIS $items = get-childitem -R |… Read More »
If you've painted yourself into the single_user corner, here is how you can get out.
Get some clustered facts about your server.
Getting all services running SQL Server using T-SQL: SELECT * FROM sys.dm_server_services
Getting all SQL Server services running on a server using PowerShell: Get-WmiObject -Class sqlservice -Namespace “ROOT\Microsoft\SqlServer\ComputerManagement11” | Select ServiceName Additionally, this can be set to an array and worked with as such. $services = Get-WmiObject -Class sqlservice -Namespace “ROOT\Microsoft\SqlServer\ComputerManagement11” | Select ServiceName foreach($service in $services) { $service.ServiceName } $services[0].ServiceName >> MSSQLSERVER $services[1].ServiceName >> SQLBrowser $services[2].ServiceName… Read More »
The basics of creating a PowerShell array and working with it.
PowerShell script that prints out the state of the node you are on – Active or Passive.