none
MSDB too big RRS feed

  • Question

  • MSDN Db grew to 79G. There are 92575987 in sysjobhistory table. I am not able to run clean up as it fails with "failed with the following error: "The transaction log for database 'msdb' is full" . I did shrink Transaction log file and it gives back the space but when I run the clean up job then it fails as it fills up the DB again. 

    The was able to query the oldest file and it seems it is from "

    SELECT min(run_date)
    FROM dbo.sysjobhistory; and it came otu to be 

    20130506

    If I cannot delete all the files then can I delete few months data at a time ? or something else

    Thank you


    @R

    Wednesday, January 10, 2018 10:55 PM

Answers

  • Hi youlearn,

    >>If I cannot delete all the files then can I delete few months data at a time ?

    To cleanup dbo.sysjobhistory table, you should run dbo.sp_purge_jobhistory. I’d suggest you schedule it as a job and run on a regular basis. Here’s some example:

    EXEC msdb.dbo.sp_purge_jobhistory; ----cleanup all job history
    GO
    DECLARE @enddate DATETIME = DATEADD(MONTH, -2, GETDATE()); ----cleanup job history that older than 2 month
    EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @enddate;
    GO
    



    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by youlearn Friday, January 19, 2018 6:24 PM
    Thursday, January 11, 2018 2:56 AM
    Moderator

All replies

  • hi,

    I see that you tried to shrink the database (which is usually a mistake), but did you tried to change the file size? Is it configure with auto growth ? let it growth if it need to and make sure you have space in the disk (or you can use file in another disk if needed)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, January 11, 2018 2:24 AM
    Moderator
  • Hi youlearn,

    >>If I cannot delete all the files then can I delete few months data at a time ?

    To cleanup dbo.sysjobhistory table, you should run dbo.sp_purge_jobhistory. I’d suggest you schedule it as a job and run on a regular basis. Here’s some example:

    EXEC msdb.dbo.sp_purge_jobhistory; ----cleanup all job history
    GO
    DECLARE @enddate DATETIME = DATEADD(MONTH, -2, GETDATE()); ----cleanup job history that older than 2 month
    EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @enddate;
    GO
    



    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by youlearn Friday, January 19, 2018 6:24 PM
    Thursday, January 11, 2018 2:56 AM
    Moderator
  • https://sqlperformance.com/2015/07/sql-maintenance/msdb

    http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

    use msdb 

    go 
    --backupset 
    Create index IX_backupset_backup_set_id on backupset(backup_set_id) 
    go 
    Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) 
    go 
    Create index IX_backupset_media_set_id on backupset(media_set_id) 
    go 
    Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) 
    go 
    Create index IX_backupset_backup_start_date on backupset(backup_start_date) 
    go 
    --backupmediaset 
    Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) 
    go 
    --backupfile 
    Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) 
    go 
    --backupmediafamily 
    Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) 

    go 
    --restorehistory 
    Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) 
    go 
    Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) 
    go 

    --restorefile 
    Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) 

    go 
    --restorefilegroup 
    Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) 
    go 

    /************************************************************************ 
    *    End Script                                        * 
    ************************************************************************/ 

     

    Notice that these are performance indexes only; they do not enforce any constraints. 

    For SQL 2000 users, there is one more bit of work to do. 
    You need to schedule a job to clean out MSDB backup history on a regular basis. 
    SQL 2005 users can set the maintenance plans to handle this. The command is: 

    use msdb
    go 
    declare @OldestDate datetime 
    set @OldestDate = getdate() -120 

    exec sp_delete_backuphistory @OldestDate 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 11, 2018 6:55 AM
    Moderator
  • Thursday, January 11, 2018 7:05 AM
  • Delete using your procedure above , few months at a time is working . Awesome. I have a question though,,

    I have total 55 months of data. First I deleted older than 45 months and that increased the DB size so I started recording 

    MDF : 64126.75 MB - Delete older than 40 months
    LDF : 13241.83 MB


    MDF : 64126.75 MB  - Delete older than 35 months
    LDF : 16460.38 MB

    MSDB is in Simple recovery mode but still LDF keep increasing ,,You know why?

    Why I check to see how much space will I get back by Shrinking (everyone says bad idea) Log file it shows 

    25112.44 MB (99%)

    What is going on ? and what is the right solution to decrease the DB size later.

    Thank you for your time 


    @R


    • Edited by youlearn Thursday, January 11, 2018 6:49 PM
    Thursday, January 11, 2018 6:49 PM
  • Hi youlearn,

    >>MSDB is in Simple recovery mode but still LDF keep increasing ,,You know why?

    Could you please execute the query below and post the result back here? If the result is ‘NOTHING’, it means noting is preventing the log file from being used at this moment, but it might not be possible to find out what triggers log file growth now.
    SELECT log_reuse_wait_desc
    FROM sys.databases
    WHERE name = 'msdb';
    

    >>What is going on ? and what is the right solution to decrease the DB size later.

    Where did you get this, could you please post a screenshot? If it’s the SSMS shrink file GUI, it just tells you that the file is 99% empty. If the file size is too large, then you should just shrink it. Shrink is a bad idea, only when you don’t understand it, and when you shrink database on a regular basis. It’s shouldn’t cause much problem in situations like this, and just make sure you rebuild/reorganize your indexes after shrink to keep your database performance.


    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 12, 2018 3:07 AM
    Moderator
  • the log file is the "bucket" where SQL Server keep track of what it has been done, for various reasons. If you need a bucket of, say 20 gallons, for a delete, then you don't want to take out your angle grinder and make that bucket smaller after each delete (so SQL server need to take it internal welder and make it bigger when the next delete is executed). I.e., don't shrink anything until you are done with msdb, sysjobhistory and all is fine except the ldf file size. Let it grow. Don't worry about it. Unless you fill the disk, of course.

    Now, if it keeps growing in between your delete operations, then you *could* spend time investigate this, or just let it grow (see above) until all is done. Check out log_reuse_wait_desc, as suggested.

    When it is tome to do the shrink, finally, check out: http://karaszi.com/why-you-want-to-be-restrictive-with-shrink-of-database-files, some details in there regarding shrinking ldf, including DBCC LOGINFO.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, January 12, 2018 9:37 AM
    Moderator
  • Check why log is not reused as mention by Lin.

    DBCC SQLPERF(LOGSPACE)  --Share data

     How old data is stored in sysjobhistory ?

    USE [msdb]
    
    GO
    EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=1000, 
    @jobhistory_max_rows_per_job=100
    GO
    EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2017-06-01T00:00:00'  ---delete data using giving date
    
    






    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Friday, January 12, 2018 10:39 AM
  • 1. yes it came out to be "Nothing"

    2. Yes I got that from SSMS shrink GUI. I will shrink the log file when I delete all the job history data which I am doign few months at a time. 

    So , Are you saying that we cannot find out why log file (MSDB Ldf) increasing when I am performing the sp_purge_jobhistory

    Thank you


    @R

    Friday, January 12, 2018 6:10 PM
  • "So , Are you saying that we cannot find out why log file (MSDB Ldf) increasing when I am performing the sp_purge_jobhistory"

    You most probably *can* find out why that is happening. The question is if you are prepared to invest the time and effort into that? You would look into things like your virtual log files (see the link I posted in other reply) and stuff like that. In the end, it is up to you. But we can't do that for you since we don't have access to your system, something we would need in order to diagnose the situation.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, January 15, 2018 1:46 PM
    Moderator