SQL Server Tacklebox- P20: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 4 Managing data growth Common causes of space issues The following issues are among the most-common of DB space-related sorrow Poorly configured Model database meaning that subsequent databases adopt properties AutoGrowth Recovery Model and so on that are inappropriate for their intended use. Inefficient Delete Insert or Bulk Insert statements such processes plus those that create temp tables can very quickly fill the log file with unnecessary data. The situation is exacerbated by incorrect Model database configuration. Indexes and large row counts clustered indexes can take up a lot of space for tables that contain millions of rows of data. However you simply need to plan for this because the consequences of not having these indexes can severely impact performance. Blatant misuse of TempDB Temporary tables often play an important role when developers are tasked with comparing millions of rows of data to return a small subset of results. This practice can have unwanted consequences such as inadvertently filling the TempDB database. It is our job as DBAs to make sure this does not happen often by performing a code review and offering an alternate solution. Over the coming sections I am going to delve into each of these issues and discuss the techniques I have used to analyze and fix each one where possible. I say where possible because sometimes data growth really does exceed all expectation and confound even the most rigorous capacity planning. The only course of action in such cases is to expand disks or add additional SAN space things only peripherally known to many DBAs. I want to stress that this chapter is not going to shine a light on SQL Server internals. I will not be taking you on a journey to the heart of the database engine to explore the esoteric concepts of leaf level storage. Every DBA needs to understand where and how objects such as tables and indexes use up space on your servers and be very familiar with core concepts such as pages extents fill .
