MSSQLTips: Tip #43 – SQL Server Agent Jobs Monitoring Script

Problems with SQL Server Agent Jobs can lead to missing backups, missing notifications about a failed job and sometimes missing mission critical tasks that are run as scheduled jobs.

Some of the common SQL Server Agent Job problems are described in other MSSQLTips tips. In this tip we have provided a single script that will check the jobs for a number of issues including:

  • Disabled SQL Server Agent Jobs
  • Failed SQL Server Agent Jobs or Job Steps
  • SQL Server Agent Jobs without a Description
  • SQL Server Agent Jobs without Schedules
  • Disabled SQL Server Agent Job Schedules
  • SQL Server Agent Jobs without Categories
  • SQL Server Agent Jobs Owned by a DBA or Other Privileged User
  • SQL Server Agent Jobs with no Owner or Owner Login is Denied “CONNECT TO SQL”
  • No SQL Server Agent Notifications on Job Failure or Completion
  • SQL Server Agent Operators Issues
  • SQL Server Agent Missing Job History
  • Potential SQL Server Agent Job Step Issue or Step Won’t Execute

Please read the latest MSSQLTips post: “SQL Server Agent Jobs Monitoring Script“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #39 – Finding SQL Server Object Dependencies with DMVs

This is the last tip about objects dependencies.

What should you know about the sys.sql_expression_dependencies DMV (Dynamic Management View)?

Please read the latest MSSQL Tip: “Finding SQL Server Object Dependencies with DMVs“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #38 – Finding SQL Server Object Dependencies for Synonyms

This tip describes how we can check SQL Server synonym dependencies (including for cross database situations).

Please read the latest MSSQL Tip: “Finding SQL Server Object Dependencies for Synonyms“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #37 – Template to Create SQL Server Synonyms with checks

When a SQL Server synonym is created with T-SQL the base object’s existence is checked only at run time. SQL Server Management Studio (SSMS) has some built-in checks that are performed during synonym creation whereas T-SQL scripts can be written several different ways and do not have any checks by default. In this tip I explain what is the best way to create synonyms and why.

Please read the latest MSSQL Tip: “Template to Create SQL Server Synonyms with checks“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #36 – Create Business Rules in SQL Server Master Data Services

In this tip we have provided the MDS Business Rules overview. You can also find out how to create a simple rule.

Please read the latest MSSQL Tip: “Create Business Rules in SQL Server Master Data Services“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #33 – SQL Server Master Data Services (MDS) custom Delete Stored Procedure

By default entity members in Master Data Services (MDS) are not deleted permanently. They are “soft” deleted (deactivated) and available for recovery from a transaction history.

In this tip we have listed several methods for deleting MDS members and we have provided a script for the custom “delete” stored procedure.

Please read my latest MSSQL Tip: “SQL Server Master Data Services (MDS) custom Delete Stored Procedure“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #10 – Different Ways to Find SQL Server Object Dependencies

In my 10th MSSQLTips tip you will learn about different ways to display objects dependencies and you will learn how to use provided scripts in your day-to-day work.

Please read the latest MSSQL Tip: “Different Ways to Find SQL Server Object Dependencies“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Q&A – SQL Server Developer: How to search for the text in stored procedures

SQL Server provides different metadata views that can help developers in their work, but sometimes developers are not up to date with the latest system tables and views changes.

In this Q&A we provide the query to search for the specific text in the stored procedures, views or functions. This is the easiest way to search for the string in objects definitions:

SELECT OBJECT_NAME(OBJECT_ID) FROM sys.sql_modules 
WHERE [definition] LIKE '%my_search_string%'

Another way is using syscomments system table which was used in older versions of SQL Server:

SELECT OBJECT_NAME(ID) FROM sys.syscomments 
WHERE [text] LIKE '%my_search_string%'

Also, you can use OBJECT_DEFINITION metadata function:

SELECT name, type_desc FROM sys.all_objects 
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%my_search_string%'