Turn off autogrow allocate the entire drive

[kdubb77]

Is there any reason to not do the following.
I have an MSA500 with a 600 GB datafile drive for a databae that is going to get quite large over the next few years so instead of having it autogrow I am going to allocate 550GB to the database and turn off autogrow. Is there any SQL server 2000 specific overhead involved in having all the space allocated to the database??

 

[ptheriault]

I believe that is a total waste of resources. The database is going to have to maintane all those empty pages too. That is going to have a negative impact on disk performance.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[kdubb77]

So any index rebuild or insert and update operations will be affected by this?? Would this increase fragmentation?? Can you explain a little more?

 

[ptheriault]

If you create a huge databse file SQL Server will need to create and maintane all the empty extents that will be created. An extent is made up of 8 logically contiguous pages or 64 kb of space. So yes, I believe it would impact reindexing but I don't think it would increase fragmentation.

While it is good to create the datafile larger than you would need you don't want to create it to large. If it will take years to get to 550GB then start at 100GB and manually manage your growth events.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[kdubb77]

I executed the statement to change the file size on my datafile to 200000MB from 40000MB. The statement is still running and has been for 30 minutes but when I look in Windows Explorer the size of the .mdf has already changed to the new size??? I guess SQL is allocating all the pages.

 

[ptheriault]

Correct. The smaller file size will also make managing backups easier.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

When SQL Server 2000 grows a file it writes 0s to the file to wipe the space. This ensures that no old data is loaded into the file.

Large empty data files do not impact backup size. The backups do not backup the empty space. However if you are going to restore you will need to have the same amount of disk space to restore to.

When setting your growth make sure to change it from 10% to a more resonable number like 512MB or so. I'd recommend not going beyond 1 GB as it will take to long to expand the data file and connections may time out while waiting for the database to grow.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com