Recent Posts

  • Which Node SQL Cluster was Running

    Which Node SQL Cluster was Running

    While troubleshooting unplanned SQL cluster failover few questions are asked commonly.

    1. When did the last failover occur?
    2. How frequently you failover (planned and unplanned)?
    3. Did this happen recently.

    All these questions can be answered by this query.  You output will depend how frequently you recycle error log and retention of error log.

    You can also use this query to look for other items in your error log.

    
    IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'#ERROR') AND TYPE IN (N'U'))
    BEGIN
    CREATE TABLE #ERROR (
    LogDate DATETIME,
    ProcessInfo NVARCHAR(255),
    LogText NVARCHAR(MAX)
    )
    END
    GO
    
    IF NOT EXISTS (SELECT 

  • Optimize SQL Backup

    Optimize SQL Backup

    Database backup is one of the regular tasks DBA’s perform but few explore all the options that are built in the product. Instead of exploring and using built in switches I noticed DBA’s try writing custom scripts or buying third party tools.
    I explored some of the options and was able to do a full backup of 8TB size database under 2.5 hours. I obtained a 30% performance improvement with using non default values for 3 of the switches that come with Backup command.

    • MAXTRANSFERSIZE
    • BLOCKSIZE
    • Number of BACKUP DEVICES (You can use up to 64)-These files can be in

  • Find Transactional Replication Details

    Find Transactional Replication Details

    This will be a quick post about a script to find replication metadata in details.  You need to run this in your distributor (where distribution database reside) server and filter in various ways depending on your need.  Also modify order by based on what you are looking for.

    Run where distribution database reside

    USE distribution;
    GO
    SELECT DISTINCT
            p.publication ,
            p.publisher_db ,
            SUBSTRING(da.name, 0, CHARINDEX('-', da.name)) AS Source_Server ,
            a.article ,
            a.source_object ,
            s.subscriber_db ,
            REVERSE(SUBSTRING(REVERSE(da.name),
                              CHARINDEX('-', REVERSE(da.name)) + 1,
                              ( ( CHARINDEX('-', REVERSE(da.name),
                                            CHARINDEX('-', REVERSE(da.name)) + 1) )
                                - ( CHARINDEX('-', REVERSE(da.name)) ) - 1 ))) AS Destination_Server ,
            

  • Monitoring Transaction Replication Latency in Real Time

    Monitoring Transaction Replication Latency in Real Time

    This post will give a practical solution with complete code to monitor SQL server transactional replication latency in real time.  My solution assumes you are using common distribution agent.  With minor change you can also implement this for independent distribution agent.

    This solution is a implementation of Kendra Little’s blog post.

    Concept is to create a table with a single row storing current date-time (updated via a sql job every minute).  Replicate that data to your subscriber and measure the difference between current time and time-stamp in subscriber table.

    Before you run any of these code make sure you read …


  • WMI issue with SQL Server 2014

    WMI issue with SQL Server 2014

    Issue: I installed sql2014 enterprise edition with cu3 in following machine.  Just as a disclaimer I have been running a sql2012 edition on the same  host without any issue with WMI counters.

    capture1

     

    I could not see any wmi counters for sql2014.  I tried perfmon.exe and also querying:

    SELECT * FROM sys.dm_os_performance_counters
    SELECT * FROM sys.sysperfinfo
    

    Then I snipped through sql server error log and found this:

    Cannot query value ‘First Counter’ associated with registry key ‘HKLMSYSTEMCurrentControlSetServicesMSSQL$SQL2014Performance’. SQL Server performance counters are disabled.

    So I went to registry and added a new key with value 4090 under  HKEY_LOCAL_MACHINESYSTEMCurrentControlSetservicesMSSQL$SQL2014Performance

    capture2

    Restarted sql …