Articles Posted in the " SQL Server " Category

  • 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)' ,
        

  • Fix High or Low VLF Count

    Fix High or Low VLF Count

    We started collecting a count of all VLF across the enterprise and yes there were few with high counts. I know there are no SET guidelines how many is too low or too high. After chatting with MVP Allen Kinsel (Blog|Twitter) and Kevin Conan (Twitter) I decided to fix using this threshold. You will be able adjust these threshold in my script to whatever you deem reasonable.

    WHERE
    ( (vlfcount > 50
    AND tLogSizeMB <= 8 * 1024)
    OR
    (vlfcount > 200
    AND tLogSizeMB BETWEEN 8 * 1024 + 1 AND 36 * 1024)
    

  • 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