2

I have a clustered SQL 2016 standard database. It is set up in a virtual environment with a total of 250 GB of storage. However, my developer set the initial size of the databases to be 100 GB. There are two databases (200 GB total), using 40 GB and 20 GB, respectively.

I am unable to run a backup due to the large initial database size. I have read multiple articles about how shrinking the database is against best practices including 'Stop Shrinking Your Database Files. Seriously. Now.' but I don't see any other options.

Can someone assist? My plan is to run the command DBCC SHRINKDATABASE down to a size just larger than the databases are now and then rebuild the index. I appreciate any suggestions.

  • Whats the size of your database backups after taking the backup? Also are you sure you are using compression while taking backups? Do you have enough storage if not that should be the first goal as backup should not be large even for that initial size – KASQLDBA Jan 22 at 16:58
  • A backup will not contain empty pages, so irrespective of initial size, your backup should be small if you don't have much data. You should use compression. Also, you can reduce initial size if the data is not going to grow to the current size. – Kin Shah Jan 22 at 18:47
  • I would recommend shrinking the database files with 'dbcc sirinkfile(<DbFileNumber>, <SizeInMegabytes>);' and perform a index rebuild to reduce fragmentation. If you have only two files (mdf and ldf) they would have 1 for the master and 2 for the log. If you have more, you can increase the number. The second parameter defines the desired size in megabytes. 102400 will reduce to 100 gigabytes if possible. If you have more data it will reduce as much as possible. – Sven Jan 22 at 19:26
  • Why are you unable to backup due to the large initial database size? 40 GB of data is 40 GB of data and possibly slightly smaller using compression. Could you elaborate on why you can't backup your database? if you are talking about taking the database offline and copying the mdf and ldf files, then that is not the best practice anyway. Thanks for clarification. – John aka hot2use Jan 23 at 8:04
2

As others have said, shrinking a database in this situation is not at all inappropriate.

What the experts mean when they say things like "Stop Shrinking Your Database Files. Seriously. Now." is that you don't want to shrink them just because they are a little bit bigger than you expect. You want to make sure you know why your database is "too big" before you shrink it. Otherwise, you're only treating the symptom, not the disease.

For example, maybe a big weekly data import causes your database to grow by 200 GB every Sunday, and then a consolidation removes 195 GB of that data every Monday. If you look at your database on Tuesday, it'll seem to be much bigger than it needs to be. But if you shrink it, it'll just grow back on the following Sunday.

In the case presented in this question, the size of the databases is the result of an erroneous initial setup, rather than the result of normal activity. As such, shrinking it makes good sense.

1

In your case, I would definitely Shrink the database say to 50GB and 30GB respectively and then ensure autogrowth is enabled and set to reasonable value (Say 128mb). Then as @KASQLDBA mentioned, ensure your database backups are being compressed. This adds a fair bit to the CPU usage during the backup, but saves TONS on network bandwidth and Disk space. If you are using Ola Hallengen's backups, add :

@Compress = 'Y',

If you are using normal MSSQL Backups, add :

BACKUP DATABASE mydatabase TO DISK='Z:\Backups\mydatabase.bak'   
WITH     
   COMPRESSION; 

Once set you should be good to go. You might not even require the shrink but i would. 100GB for new databases is rather big, and you have no idea on the growth. It may take some time. In addition, starting with file sizes that are so big, will make generating a growth baseline more complicated.

  • @LowlyDBA Wow i must have gotten a different question confused. Thanks for pointing it out. Adding a explanation. – WadeH Jan 22 at 19:07
1

Kin pointed out in a comment that a backup doesn't contain empty pages. Do make a note of this. Shrinking means moving pages toward the beginning of the file (unless TRUNCATEONLY is specified which just cut off the file if/where possible). So, shrink doesn't mean that you "free up" any space in the database - you're just moving stuff around. I.e., don't expect a backup to be smaller just because you shrunk the file. Shrink the file if it extremely large compared to the amount of data and you need to reclaim the disk space for the file.

Quick elaboration: Shrink move pages towards beginning of the file. Backup excludes non-used extents. It is possible that due to movement you end up with a higher degree of fullness of the extents after a shrink which can affect the backup size to a small degree. I.e., I simplified the explanation above a bit. I'd be very surprised if you see a significant difference because of this, though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.