Contents
Does database size affect performance?
For basic insert/update/delete transactions that affect just a few rows, then the growth in data size is probably not a big consideration. The database will use in-memory indexes to access the correct page. Just having more data is unlikely to affect performance unless the tables are used in queries.
What does shrinking a database do?
The Shrink Database task reduces the size of SQL Server database data and log files. Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file.
How large should my database be?
A database should not contain more than 1,000 tables; Each individual table should not exceed 1 GB in size or 20 million rows; The total size of all the tables in a database should not exceed 2 GB.
Why is shrinking a database bad for performance?
One person shrinks the database to gain space (thinking it will help performance), which leads to increase in fragmentation (reducing performance). To reduce the fragmentation, one rebuilds index, which leads to the size of the database to increase way more than the original size of the database (before shrinking).
How to reduce the size of a database?
Now let us TRUNCATE the table and check the size and Fragmentation. You can clearly see that after TRUNCATE, the size of the database is not reduced and it is still the same as before TRUNCATE operation. After the Shrinking database operation, we were able to reduce the size of the database.
What happens to database size after truncate operation?
You can clearly see that after TRUNCATE, the size of the database is not reduced and it is still the same as before TRUNCATE operation. After the Shrinking database operation, we were able to reduce the size of the database. If you notice the fragmentation, it is considerably higher.
How big is the database after a rebuild?
You can notice that after rebuilding, Fragmentation is reduced to a very low value (almost same to the original value); however the database size increases way higher than the original. Before rebuilding, the size of the database was 5 MB, and after rebuilding, it is around 20 MB.