Recent Posts


  • 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

  • Free Azure Credit

    Free Azure Credit

    There are multiple ways you can get free Azure credit, which can add up to $500 for one year. With a Microsoft account and one of these free credit options you can open an Azure account. You will be able to use this account for any Azure service based on your needs including, Virtual Machines, Websites, Cloud Services, Mobile Services, Storage, SQL Database and many more. If you discipline yourself about stop/pause/delete resources when you are not using a resource or you no longer need, free credits can go long way.

    FreeAzureCredit

    $200 for 30 days

    You receive $200 of Azure …