I'm still fairly new to SQL Server. We have a production database at our company that has grown from about 40gb to 100gb over the course of about five years. What I want to better understand is if the data file can become "bloated" with nonexistent data, or if the file size is an accurate reflection of how large the database needs to be. From what I understand, a DBA would almost never shrink a DB file, but are there other bits of maintenance that one might do to decrease the size of this file or remove unnecessary bloat?
Thank you for any assistance!
There are a whole bunch of things that can consume file space in SQL Server. Some of it will be filled. Some of it will be empty. A lot of it is totally unnecessary.
Lets first start with indexes. If your not doing index maintenance, you can quite easily be wasting 50% of both your disk space and memory just due to the bad form of page splits. If you're doing index maintenance incorrectly (which, oddly enough, is advertised as "Best Practices"), the same or worse can be true. And, no matter what happens, if you do a REBUILD on larger indexes (anything over 128 extents, which is only 8MB), you could have quite a bit of unwanted/unnecessary free space in your data files because an index rebuild makes a full copy of the index before dropping the original. If you're using REORGANIZE on larger indexes, your log files can explode to more than 155% of the size of the largest index (it was a helluva surprise to me when a 146GB clustered index with only 12% fragmentation grew the log file to 227GB from only 20GB).
Another huge waste of space are work tables that were supposed to be temporary but that no one has deleted. If you check sys.dm_db_index_usage_stats for tables that haven't been written to or read from in a month, you can develop a good list of suspects. Never just drop these tables. Just rename them by adding a suffix of "_ToBeDeleted" to them and wait a couple of months before you delete them. Renaming tables will change the "modified" date for the table and you can key off of that.. We use a "scratch" schema in our databases where we work and have an automatic "sweeper" that renames any such table after a week of existence and automatically drops them a week after that.
As if that's not bad enough, Microsoft decided that they'd help us with performance in version 2016. They made it so that "insert bulk" (not to be confused with "BULK INSERT"), which a lot of front ends use to add data to tables, would automatically reserve (IIRC) 4 extents up use and without checking to see if there were any extents that were already allocated and had free space. It also doesn't matter how many rows are being inserted. If it's just one row at a time, the 4 extents (32KB) are being consumed and most of the space will be "unused". The WhatsUp database in our system went kinda of nuts... it contained only 8GB of data but had 47GB of "unused" space. Don't confuse that with the much more benign "Unallocated Space", which can be used by any table or index. "Unused space" is assigned to indexes and cannot be used by anything else except that one index.
If you see databases with a large amount of "Unused" space, that's probably the issue and you're going to first need to enable Trace Flag 692 and then rebuild your indexes to recover the space as "Unallocated".
You'll also find various audit and history tables in any given database. As single such table can grow to be as larger than the rest of the entire database. You need to ferret those out and have people identify when you can archive the data out of those tables.
I'm still fairly new to SQL Server.
Welcome to the data equivalent of "Ripley's Believe It or Not". 😀
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)