DBA’s normally create databases – Right? Most of us know about the best practices in creating a database. Here I am giving a short note on shrinking and growing of databases. While we configure the database size, we can set the database size to be fixed or auto grows. The advantage of setting the database size to be fixed is it will reduce the fragmentation of the file. The disadvantage of fixed database, when database grows to the fixed size, we will run into problems.
DBA’s can use sp_spaceused stored procedure to determine the database and free space available in tables, indexed view or service broker queue.
The drawback of allowing databases to grow automatically is that growth can lead to file fragmentation. File fragmentation can degrade the performance. A better technique when configuring the database is allocating more space to the database files because this will minimize fragmentation.
Also note that shrinking a database is not good idea, because it will fragment the indexes. If you shrink the database, we need to re-create indexes. The command used for shrinking the database is DBCC SHRINKFILE. You cannot shrink the database below the minimum size, which is specified while creating the database, even if all data is deleted in the database.