Articles Posted in the " Meta Data " Category

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