yanci1179
asked on
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!!
thanks!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
ASKER
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
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
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.
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.
ASKER
i thought that i should add more disk space.
That is always an option if you are going to need more space.
ASKER
how do i determine if space is the issue. what other things can i look at before i suggest to get more space?
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.