Articles Posted in the " SQL Server " Category

  • SQL Server Fullscan Statistics Being Overwritten with Sample Statistics

    SQL Server Fullscan Statistics Being Overwritten with Sample Statistics

    I observed this behavior only in following version of SQL Server while using Always On Availability Group feature.

    Version: Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

    In this article by David Barbarin similar behavior was also observed in SQL Server 2014 SP2.

    Once I update my statistics with fullscan, with in 10~20 seconds some of the statistics on the same table are getting update on secondary with a sample pecent of rows. Meaning my best statistics are being …


  • TSQL Tuesday #79 -JSON Support in SQL 2016

    TSQL Tuesday #79 -JSON Support in SQL 2016

    I thank Michael J Swart (b|t) for hosting TSQL Tuesday #79. Topic for this month is to write about SQL Server 2016. This is my first time joining in TSQL Tuesday and I am excited about it.

    TSQL2SDAY-300x300

    In my workplace we use MongoDB besides SQL Server. MongoDB save documents in binary form of  JSON called BSON. Our developers are excited to see JSON support in SQL 2o16.  I will give some simple examples about JSON support in SQL Server 2016.  Readers need to be aware that SQL Server is not supporting native JSON type.  In …


  • What Is Consuming My Log Space

    What Is Consuming My Log Space

    Often times DBA’s (including myself at the beginning of my DBA career) struggle to find out what is causing the log files to grow. Or what is consuming all the space in my log files. I understand there are many factors that can impact the log file size beside all the active transactions.

    Such as:

    • Recovery Model
    • If replication/logshipping/mirroring is set up
    • Frequency of transaction log back up
    • Longest running transaction
    • and many more

    I wanted to see a list of all the sessions that are consuming more than 1kb off log space for all the databases in the server.…


  • Manage SQL Agent Job History

    Recently in one of our server row count in MSDB.DBO.SysJobHistory table exceeded 100 Million and looking at job history was slow via SSMS. It was impossible via the GUI. Because we had about 50 jobs that were running frequently (called from a listener mostly once in every 2 to 3 minutes). I was curious if we can manage the job history for individual job or job category. I also wanted different retention for success vs failure of the job. Meaning for the jobs that run every minute few days of history for success run is enough whereas for failed ones …


  • List All Objects In FileGroup with Size

    Recently at my work due low free space on a LUN I was forced to rebuild few indexes on a different filegroup (which resides on a different LUN). I wanted to list all user objects belong to a single filegroup including size. Did not find a query online with everything I needed. So I wrote this one and wanted to share.

    --Run this in the context of database that you are working on
    SELECT
        FileGroup = FILEGROUP_NAME(a.data_space_id) ,
        TableName = OBJECT_NAME(p.object_id) ,
        IndexName = i.name ,
        8 * SUM(a.used_pages) AS 'Size(KB)' ,
        8 * SUM(a.used_pages) / 1024 AS 'Size(MB)' ,