Truncate msdb log file

The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?

Learn more. Asked 6 years, 1 month ago. Active 6 years, 1 month ago. Viewed 5k times. We have a SQL Server cluster with 60 databases. Full backup runs every night, log backup runs every 15 minutes. The MSDB database recovery model is set to simple default. To be able to see the history of these jobs, we had to setup a job cleaning the history log. Should we enable "Auto shrink" or create a job to shrink the log file? Improve this question. Answered by:. Archived Forums. Sign in to vote.

Hi, Log file size of msdb is greater that 3 GB. I am little worried about the size. So please help me to solve if this is an issue. As normal we have scheduled tasks for maintaing the instance. How can i check the saved SSIS packages if any..? We have a daily full backups of system databases. Privacy policy. Shrinks the current database's specified data or log file size. You can use it to move data from one file to other files in the same filegroup, which empties the file and allows for its database removal.

You can shrink a file to less than its size at creation, resetting the minimum file size to the new value. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data. Used pages in the file's area to be freed are moved to free space in the file's kept areas.

The free space at the file's end isn't returned to the operating system, and the file's physical size does not change. The log files are not affected. The data file is shrunk only to the last allocated extent. Other users can work in the database during file shrinking - the database doesn't have to be in single-user mode.

You don't have to run the instance of SQL Server in single-user mode to shrink the system databases. Asked by:. Archived Forums. Sign in to vote. Thursday, November 5, PM. What is the recovery model of that database? I would see why this log file has become GB.

What is the result of this query? The question now is: What types of backup do you want to do for this database? Do you wan tto perform log backups? If you do want to take log backups for the database you first need to determine whether it is acceptable to have a broken log backup chain since your most recent log backup.

If it is, then set to simple, shrink to reasonable size, full, do db backup and then log backups. If not, then just do a log backup which likely will be huge , then possibly shrink and keep doing regular log backups.