Category Archives: Technology

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];

Restore All Databases With Most Recent Backup

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 »

PowerShell – List All Directories With Users Who Can Access

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 »

SQL Server – Database Stuck In Single User Mode

If you've painted yourself into the single_user corner, here is how you can get out.

SQL Server – Cluster Queries

Get some clustered facts about your server.

Favorite Computer Environment Tools

The Deep Secrets of My Computing Environment!

In my computing environment, I have a set of tools that I rely on because they just make life easier. Here they are in order of (perceived) actual usage.

SQL Server – Getting All SQL Services Running On System

Getting all services running SQL Server using T-SQL: SELECT * FROM sys.dm_server_services

PowerShell – Getting All Running SQL 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 »

PowerShell – Making Arrays

The basics of creating a PowerShell array and working with it.

PowerShell – Find Active Node On Cluster

PowerShell script that prints out the state of the node you are on – Active or Passive.