1

We have a SQL Server Express instance, where one database reached 10Gb.

We have deleted 90% of the larger db table records, supposedly clearing 4Gb of space, according to allocation_units.used_pages.

DBCC SHRINKFILE has reduced the log file, but hasn't affected data file size. The database recovery model is simple.

DBCC SHRINKFILE (datafile, TRUNCATEONLY);
DBCC SHRINKFILE (logfile, TRUNCATEONLY);

Are there any restrictions of DBCC SHRINKFILE on SQL Server Express edition?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
fcaserio
  • 726
  • 1
  • 9
  • 18
  • You are using TRUNCATEONLY, which can only truncate the data file back to the last used block in the file. Since it does not relocate any data blocks, it cannot shrink it if the last block in the file is in use. Solution: don't use TRUNCATEONLY. – RBarryYoung Oct 11 '16 at 15:37
  • What should I use to reduce the data file to the minimum possible size? I have allways used TRUNCATEONLY – fcaserio Oct 11 '16 at 15:59
  • Just skip the Parameter or use NOTRUNCATE. Warning, it could be a lot slower to run, depending on how big the DB is and how full it is. – RBarryYoung Oct 11 '16 at 16:07
  • OK, it worked, tks a lot! What exactly is the difference on using NOTRUNCATE or TRUNCATEONLY? Is NOTRUNCATE recommended to databases using recovery model SIMPLE? – fcaserio Oct 11 '16 at 16:25
  • No this has nothing to do with the SIMPLE setting or that your are running Express. TRUNCATEONLY is just the "quick and easy" setting for SHRINKFILE. It's fast because it doesn't move anything around, but that also severely limits it. NOTRUNCATE is the full version, and it always works, but it can be very slow, taking hours or even days sometimes. See the doc here https://msdn.microsoft.com/en-us/library/ms189493.aspx – RBarryYoung Oct 11 '16 at 16:32
  • Sorry, my bad, NOTRUNCATE does not actually shrink it, so it should be followed with TRUNCTEONLY. To get both, just omit the parameter. – RBarryYoung Oct 11 '16 at 16:35

0 Answers0