Articles Posted by the Author:



  • Warning: Non-existent step referenced While Creating SQL Agent Job

    Warning: Non-existent step referenced While Creating SQL Agent Job

    Today there was a question submitted in dba.stackexchange.com with heading “Suppressing non-ANSI warnings in SQLCMD script”. Details here. In summary, this happens when you use a script to create a SQL Agent job and one of the job step has conditions (under advanced properties) to go to certain steps in case of success or failure. Exact text of the warning:

    Warning: Non-existent step referenced by @on_success_step_id.
    Warning: Non-existent step referenced by @on_fail_step_id.

    I searched for the keywords “Warning: Non-existent step referenced” and looked at the top 10 results. Most of them talked about properly setting advance property of each …


  • How to Exclude Read_Only Databases Using sp_MSforeachdb

    How to Exclude Read_Only Databases Using sp_MSforeachdb

    I will explain how you can use sp_MSforeachdb stored procedure against READ_WRITE database only.

    There are numerous articles on the web about excluding databases using database id or name. Recently I needed a script to run against databases that are part of Always On Availability Groups and in READ_WRITE status. I used the ‘Updateability’ properties of DATABASEPROPERTYEX function.

    Here is how I implemented this.

    SET NOCOUNT ON
    
    EXEC sp_MSForEachDb '
    USE [?]
    IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'') 
    IF ((SELECT DATABASEPROPERTYEX(''?'', ''Updateability''))=''read_write''  )
    BEGIN
    .....
    .....
    END
    '
    

    I would like to know if you implemented same …


  • TSQL to Find Status of SQL Server Statistics

    TSQL to Find Status of SQL Server Statistics

    About two years ago, I was working on an update statistics solution for 15 TB OLTP database.   First thing I needed to know, is the status of statistics.   With a google search I landed into this blog post by Erin Stellato (blog | Twitter) from SQLskills. Which gave me a perfect head start.

    I expanded that query to add more things in the result set.

    • Separate column for schema and object name.
    • Statistics ID.
    • Is this a temporary statistics (more here) ?
    • Column names (I wanted one row per statistics even with more than one column).
    • Ability