September 3, 2009 at 8:29 am
Hi again - I have a question about when a database is set to Auto Grow by 1MB being the default. Why when my database grows does it say that there is xxxMB (e.g. 100MB) unused space available? Surely if I am only growing by 1MB then the maximum space unused would be 1MB?
What am I missing!?
September 3, 2009 at 9:06 am
First, autogrow is not how you manage space. It's an emergency measure, and you ought to set it for emergencies. Lots of times there will be a need in an emergency, like an unexpected data load, to grow more than 1MB. Constantly growing 1MB fragments is a bad idea. You'd want it to grow by more.
Second, a database file is not like a Word or Excel file. You set it to have free space that is needed for reindexing and data growth. Growing a file is an expensive operation and can result in fragmented files. You want to minimize that. When you set a database size, you set it to be xx MB (or GB), which should hold all your data, and have space to handle administrative operations like reindexing as well as data growth for a month or three.
You have unused space in your database to allow for that.
September 3, 2009 at 9:17 am
HI there thanks for the reply. Yes I agree with you and I am undertaking an database growth monitoring exercise currently to determine what size I should set my database so as to avoid growth as you have mentioned. I was just wondering I am seeing such large unsused space after an auto grow when its set to 1MB. Are you saying that SQL Server creates this extra space based on the space indexes will need for rebuilds etc?
September 3, 2009 at 9:24 am
No, SQL wouldn't create that extra space. either someone added it, or there possibly was data deleted (or indexes) that freed space.
September 3, 2009 at 9:34 am
Another possible area that caused the extra space was alluded to in the first response by Steve. It is possible that there was a reindex as well.
Options for the extra space even though your growth is set to 1mb are:
Huge Data Inserts and the Files were grown at that point, and then back down to the size you see now since the inserts are done and the indexing on the inserts is complete.
Data Deletes
Index Deletes
Table Drops
Index Create or Rebuild
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 4, 2009 at 2:03 am
OK thanks to you both.
September 4, 2009 at 7:13 pm
If you are looking at the first property page for the server properties, you need to understand that the number shown there as free space includes the transaction log.
It could very well be the transaction log itself that has the available space.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 6, 2009 at 5:19 am
Hi Kwisatz78,
once upon a time with my colleagues we were trying to check the differences between fixed size db files and default database settings. We were loading something about 20GB of data - the operation that need to resize files takes 2 times longer then other one. And it was a simple load batch - if you add to this some user activity, reporting and so on - bad datafile settings can be a real problem.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply