Reg:Shrink Database not happen

Question

Post reply

Reg:Shrink Database not happen

vs.satheesh

SSCrazy

Points: 2392
More actions
May 16, 2019 at 10:24 am

#3644137
Dear All,

In My production environment , every six month cycle we are replace the database server.whenever replace the database we are truncate all the unwanted transaction data. My problem is after truncate the data i try to shrink the database. But not reduce the MDF file size.i tried all the possibilities like
1. Change the database recover mode in simple i tried , that time also not working
2. Index rebuild all the tables
3. mdf and ldf separately tried
4. backup file got reduced but mdf file not reduced.
please give solution for above this problem.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andrey SSC Eights! Points: 840 More actions · Answer 1

questions:

Are there any LOB columns (text, image, varchar(max), etc) in the tables where you truncate (delete?) the data?
do you see and how do you check that free space exists in the database?
provide the output of the following:
```
 select @@version?
```

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

The solution is simple. Don't shrink. There is no point in shrinking if you are going to fill up the space again. Also, shrinking the data files introduces a ton of fragmentation.

You can also read SQL Server MVP Tibor Karaszi's article on the topic:

https://karaszi.com/why-you-want-to-be-restrictive-with-shrink-of-database-files.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Andrey SSC Eights! Points: 840 More actions · Answer 3

vs.satheesh wrote:

please give solution for above this problem.

also, check this out:

https://dba.stackexchange.com/questions/186871/how-to-free-the-unused-space-for-a-table