0

I am using SQL Server express edition with 10 GB Size limit, There some table having millions + rows and I need to perform data purging operation at the end of every month, so as to free up the space and make it available for new data storage

If I use the shrink through SSMS(Right click on database then task then shrink ) it works smoothly and free up the space as expected. But the problem arises when I try to achieve the same with

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

GO

DBCC SHRINKDATABASE(MyDatabase)

GO

ALTER DATABASE MyDatabase SET RECOVERY FULL

GO

It literally show no effect, infact it increase the log file size but never freeing up the space.Is there any solution,it will be greatly appriciable

Thanks in advance

TT.
  • 15,774
  • 6
  • 47
  • 88
Vsagar
  • 197
  • 2
  • 17

1 Answers1

0

I would not switch the recovery model to SIMPLE to force shrinking.

When the recovery model is set to FULL you must execute a full backup and at least one transaction log backup to fulfil the recovery condition before you can shrink the database.

Patrick
  • 668
  • 4
  • 11