Just a quick note on a scenario I ran into, and what I did to fix it: Scenario A user was running a stored procedure that created a table. Rather than having a table created in the "dbo" schema, it was creating a table with their user name as the...
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...
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,...
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...
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.
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...
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...
If you've painted yourself into the single_user corner, here is how you can get out.