List of all failed SQL Agent Jobs

Some SQL Agent Jobs run multiple time a day (I have see a job running every 10 seconds in Production).  SQL Agent only gives the the current state of all the jobs.  Here is how you can find all failures with their date and time.  From here, tracking down the root cause should just be a bit of investigative work.

SELECT  sysjobs.name
,sysjobhistory.run_date
,sysjobhistory.run_time
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobhistory
ON sysjobhistory.job_id = sysjobs.job_id
WHERE sysjobhistory.run_status = 0
AND sysjobhistory.step_id <> 0
ORDER BY sysjobhistory.run_date DESC, sysjobhistory.run_time  DESC

Where to find Deadlocks

All deadlock information is logged under the system_health extended event by default.  simply open system_health, right click package0.event_file > View Target Data...  At this point we can right click the grid, and select filter by this value.

Redgate's SQL Search

I have been reluctant to use Redgate's SQL search.  Instead I have been scripting out the database and running a search.  I have used many clunky add-ins with little to no value add, which has put me off to trying many others.  Encouraged to give SQL Search a try by a friend, I quickly realized I had been missing out.

With a simple menu, I can quickly decide what I would like to search for.  Check a few boxes and start typing my search, right from SSMS.

Results are generated quickly, it didn't take long for me to see a time saving.  Gaining results quickly also kept me focused on the task at hand without loosing my concentration.  I highly recommend Redgate's SQL Search to ANYONE using SQL Server.

Custom Color for Production Connection

Have elevated permissions in production and want a visual aid to help quickly identify your production environments?

Backup Solution

New Year, Same old story.  SQL Server backups are a necessity for every SQL Server Instance.  While there are many ways to accomplish this task, my favorite by far is Ola Hallengren's SQL Server Maintenance Solution.  The script comes complete with Database backups, rebuild indexes, integrity checks and even log cleanup.

Show Desktop in Windows 10

I am always flooding my desktop with open windows while developing.  Showing the desktop is a must have in a situation where you need to switch gears for a few minutes.  Windows 10 has the show desktop built in right out of the box.  Simply click the small button in the lower right hand corner.

Auto Fix Orphaned User

When restoring a database to a new or different instance, logins at the instance level are not mapped to the database users.  SQL SERVER has a built in stored procedure to help us identify orphaned users, sp_change_users_login.  We can also use this stored procedure to fix the mapping if the login exists at the instance level.  Finally, if we need to create a new login for the existing user, we can create a login and password.  See the below examples.

Select from all tables on an Instance

We have all inherited less than desirable SQL Server Instances.  DBAs and Developers will both need to go through the countless databases, tables, SPs, etc. to get a handle of the situation.  In such a fragile situation, we need to ensure we keep our data integrity and prevent our developers from accident updates while trying to trouble shoot an issue.   Here is a nice Server Level Permission set to allow Developers to select from any table in any database on the instance.

Restore Master Database

Recently I was tasked with migrating a SQL Server Instance to a new infrastructure.   Along with the migration, I also had to create a DEV, QA, UAT  environments as they were not part of the original infrastructure.  Instead of trying to move all the pieces separately, I migrated the databases, along with restoring the Master database.

To being we must make sure the new SQL Server Instance matches the existing instance.  Here is how we make sure the Instances match.

db_executer

SQL Server comes with several predefined database roles which fit the needs of most day to day user needs.  A big miss that I have found is the ability to execute stored procedures.  Developers need the right to execute the SPs they are creating in the DEV and QA environment.  Many service accounts also need execute rights.  All that needs done is to create the Role, grant execute rights, and add members to a role.

CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
EXEC sp_addrolemember 'db_executor','User or Group'