Solved

msdb data file size reading at 88% full

Posted on 2007-03-29
8
408 Views
Last Modified: 2008-02-01
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!!

0
Comment
Question by:yanci1179
  • 4
  • 3
8 Comments
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18817383
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
 
LVL 10

Expert Comment

by:lahousden
ID: 18817398
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
 

Author Comment

by:yanci1179
ID: 18817662
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:yanci1179
ID: 18817669
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
 
LVL 16

Expert Comment

by:rboyd56
ID: 18817987
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
 

Author Comment

by:yanci1179
ID: 18818835
i thought that i should add more disk space.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18818940
That is always an option if you are going to need more space.
0
 

Author Comment

by:yanci1179
ID: 18819871
how do i determine if space is the issue.  what other things can i look at before i suggest to get more space?
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlServer no dupes 25 34
Need help how to find where my error is in UFD 6 25
Update a text value in another table 10 35
VB.NET 2008 - SQL Timeout 9 22
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now