MSDB system database on SQL Server 2005

[former_member282786]

Hi

At the moment our SBO Database files are relatively small in size (the LDF and MDF files), they are only in the MB side. Due to a sudden drastic growth in Hard Drive space used to what we have I investigated the Data folder under SQL Server. Our Data folder is on another drive (E:// Drive) and our backups on another so all the E:// Drive will have is the data for SQL Server.

I noticed under the System Databases that the msdbdata and msdblog Files (MDF and LDF) are pretty big in size (50GB / 40GB respectively), can anyone tell me how this System Database file got so big and is there a connection between these System Databases to my SBO Databases on the SQL Server.

(All SBO Databases have backup recovery as Simple and Autoshrink on, there is also a maintenance plan in place)

Regards

Kurt Walters

[former_member186095 · Accepted solution]

Hi Kurt,

The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.

MSDB is not a big concern as long as you purge backup and sql agent job history as well as database mail items. So, I think you must check the backup ad agent job history first.

Check also the autogrowth. How much it is. are you enabled autogrowth ?

JimM

[former_member282786 · Accepted solution]

Hi Jim

Thanks for the reply, I have gone onto Google and checked a few issues with SQL Users but can you elaborate on:

1) MSDB is not a big concern as long as you purge backup and sql agent job history as well as database mail items

2) So, I think you must check the backup ad agent job history first.

3) Check also the autogrowth. How much it is. are you enabled autogrowth ?

Currently the msdb database as well as all my SAP Databases have Autoshrink and backup recovery as Simple, the Maintenance Plans in place (which SQL Server Agent is currently switched off) are setup as follows:

Properties

-Schedule Type (Recurring)

- Occurs (Daily)

Backup

- To defined Drive

_Tasks_

- Back Up Database (Full)

to relevant drive on server with.bak extension

- Shrink Database

Shrink DB when it grows beyond 50MB

Amount of free space to remain after shrink = 10% (I think this is where the issue could lie in the Maintenance Plan)

Return free space to Operating System (ticked)

- Clean Up History

Historical Data to delete - Backup and Restore History (ticked) - SQL Server Agent Job History (ticked) - Maintenance Plan History (ticked)

Remove historical data older than = 4 weeks

- Maintenance Cleanup Task

Delete .bak file extension from relevant backup folder where backup file is older than 4 weeks

Report

Write a report to a TXT file = on relevant folder location

Can you help me if possible / point me in the right direction of where I amy be goign wrong.

Regards

Kurt Walters

[Former Member · Accepted solution]

Hi Kurt Walters,

To keep backup file for 4 weeks is way too much for the system. One day would be fine because there are little values to keep the old backup. Maximum 3 days needed. Try to reduce this first to see.

Thanks,

Gordon

[former_member282786 · Accepted solution]

Hi Gordon

Thanks for the tips I will give it a go. Is there anyway of reducing the size of the Log and data file of the msdb database because I have selected the backup recovery mode as Simple and Autoshrink as well as Shrink > Files > Log; the size goes down but within 3-6hrs the files size has increased.

Regards

Kurt Walters

[Former Member · Accepted solution]

You must set up something wrong. The rule is: never touch MSDB default setting. System will take care of it. All manual intervention will cause weird problem.

[former_member282786 · Accepted solution]

Hi Gordon

Unfortunately this issue was happening before any manual intervention took place on the msdb database, the backup recovery mode was already set as Simple (this we did not touch - this db has not been touched since installation), the IT Administrator went to Shrink > Files > Log as well as Autoshrink under the properties of the db (but that is it).

I have looked at a few SAP Business One notes regarding this matter and everything that is currently set on the msdb database is what is needed, there are a few queries to run so will test this.

We have a SQL Expert currently looking at this issue so will let you know what the exact issue is.

Regards

Kurt Walters

[former_member186095 · Accepted solution]

Hi,

You have described my questions well. Another things to consider is Log Extended Information. Here a brief description about it:

When this item is checked, all the T-SQL that gets submitted to
SQL Server will be included in the maintenance plan history. Itu2019s checked by default and is
useful for debugging any issues that arise from plan execution. However, depending on the
size of the T-SQL script being executed, this could add a lot of data to your msdb database.
To make sure your msdb database doesnu2019t get too full, you can either uncheck this option
or add a History Cleanup task that will automatically remove maintenance plan history tasks
older than a certain date.

Fore more detail, you may read Apress.Accelerated.SQL.Server.2008.May.2008 pdf file.

JimM

[former_member282786 · Accepted solution]

Hi Jimmy

Thanks for the response, just to answer your first question regarding the autogrowth on msdb:

- Enable Autogrowth (ticked)

- File Growth (In Percent = 10)

- Maximum File Size (Restricted File Growth (MB) = 2,097,152)

Now the above I have not touched as I always leave the default system databases as they are (as per point pointed out by Gordon I have alwways followed this)

What I think I will try first is the deletion of the Maintenance Plans and Jobs as well as clearing out the History of the Plans, then when redoing the Maintenance Plans & Jobs set them to 3 days the most before deleting all the History (as pointed out by Gordon), by looking at it maybe the 4 weeks workth of runs have increased the size.

I will have a look for the PDF you mentioned as well.

Regards

Kurt Walters

[former_member282786 · Accepted solution]

Hi Jimmy & Gordon

Thanks very much for your help, it is much appreciated. I think what I will do is re-install SQL Server as I need to recreate the MSDB database, it gives me a good reason to install SQL Server 2008 which is what I am wanting to do.

Regards

Kurt Walters

[agustin_marcoscividanes · Accepted solution]

Hi

did you install LVS addon in your system?

The LVS addon had a bug that made grow fast the MSDB database in MSSQL 2005.

Kind regards.

Agustín.

[agustin_marcoscividanes · Accepted solution]

Hi

did you install LVS addon in your system?

The LVS addon had a bug that made grow fast the MSDB database in MSSQL 2005.

Kind regards.

Agustín.