Home Forums SQL Server 2005 Administering How space is allocated for new objects in the data files? RE: How space is allocated for new objects in the data files?

  • It is important that you setup the database to automatically shrink itself.

    This is BAD advice. You should never shrink your database on a regular basis. If you do:

    1) You will get NTFS file fragmentation which will harm your performance. NTFS file fragmentation can only be fixed by doing a NTFS Defrag.

    2) You will get index fragmentation inside your database which will harm performance. This can be fixed by rebuilding indexes, but the benefit of an index rebuild will be lost when the database is shrunk.

    The only time is is worth shrinking a database file outside of an emergency is when you expect a long-term decrease in database size. If you expect your database to increase to its original size within 3 months, then do not shrink it.

    The posts show a large number of tempdb files. If tempdb is split into multiple files, they should all have the same size and allow zero growth. (You can create a 1MB file that does allow growth if you are worried about tempdb filling up.)

    The main reason for having multiple files in tempdb is to allow SQL Server to balance IO load acros the files. This only happens if the files are the same size (or almost so - 1% or 2% difference will not harm much but it will harm) AND the files allow zero growth. If the tempdb files allow growth, the object you are working on is effectively pinned to that file, and if that file cannot grow then you get an error, even if other files in tempdb do have space.

    You should only have multiple files in tempdb if your server can make use of them. A good rule for CPU loading is one tempdb file per processor core, up to a maximum of about 8 files. A good rule for your disks is that each tempdb file should exist on separate disk drives to the other files, otherwise the increased I-O load caused by multiple tempdb files is likely to cause more queueing than having a single file. Even in a SAN, if the LUNS hosting tempdb map to the same disks then you do not get a benefit from having multiple tempdb files.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara