SqlTipsDBWontShrink

You may run into a database that won't shrink, there's several "obvious" problems that are blogged about everywhere - However sometimes it's more complex.

First let me go over the basic things - Make sure all of these aren't your problem:

• Log files will be kept until they are backed up in full recovery mode, this means a transaction log backup, NOT a full backup or database backup.
• Log files should truncate at checkpoints if the database is in Simple recovery mode.

Now, assuming you fully understand the above things and your database STILL won't shrink there are some things you should know and check:

A view sys.databases contains a few fields:

• Name - Name of database
• log_reuse_wait - Reason why log isn't being truncated
• log_reuse_wait_desc - Description

We can run the following query to get a status:



You may receive a log_reuse_wait status of 6 - this means replication is behind. HOWEVER I was troubleshooting this today on a database without replication configured. Something is clearly wrong in the server setup to have this "confusion" in the database.

There's a Microsoft KB article that describes how to manually remove a publication / replication - The overly simple method is:



References:

• sys.databases view information (cache)
• Manually remove replication (cache)