msdb data file size reading at 88% full

Using Sql 2000: my msdb data file size is 88% full , and one of my other databases data file size is 85% full.  I've read that shrink database may help.  Any suggestions on how to reduce the size?  How do I see if it is set to automatically grow?  If the size is at this size will there be a lot of performance issues?

thanks!!

yanci1179Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
IF the databases are 88% and 85% full then I am not shrinking them is the best thing to do. Depending on the actual sizes then any data modification could cause them to autogrow which is definitely could cause a performance issue.

The size of the database usually does not have a hugh bearing on performance. It really depends on the number of records in the tables and how well the data is indexed.

You can chack to see if the database files are set to auto grow by looking at the properties of the database in Enterprise Manager. Or you can run sp_helpdb database name. The output will give you this information.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lahousdenCommented:
Are you currently experiencing performance issues?  What are the actual database sizes?
In SQL 2000 you can use the Data Files tab in the Database Properties window in Enterprise Manager to see whether the "Automatically grow file" checkbox is checked for a particular file - not sure how to review this in SQl 2005.
0
yanci1179Author Commented:
they are not set to auto grow.  Decided that it would be better to add disk space or re-allocate space from another database,.  new at this, any suggestions?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

yanci1179Author Commented:
Here's the stats on the databases

Database: msdb
Number of tables: 114
Data file size: 4541 MB (88% full)
Data size: 3674.62 MB
Index size: 560.45 MB
Log File: 3.99MB


Number of Tables: 2570
Date File Size: 26,382 MB (86% full)
Data Size:14,724.33 MB
Index Size:8,058.27 MB
Log File: 12,681.2 MB
0
rboyd56Commented:
My thinking is that you should your databases to a size that is appropriate for them and then monitor them closely to make sure they do not get full. However, it is a good idea to have auto grow enabled for that one time when a large transaction might cause the databse to get full.

If you are backing up your databases regularly you can run the sp_delete_backuphistory in msdb. This will free up space in that database by deleteing old backup history.
0
yanci1179Author Commented:
i thought that i should add more disk space.
0
rboyd56Commented:
That is always an option if you are going to need more space.
0
yanci1179Author Commented:
how do i determine if space is the issue.  what other things can i look at before i suggest to get more space?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.