asked
DATABASE SHRINKING
we have a database=340 gb, and both datafile and log file=300gb,free space=40gb. can we perform database shrinking or not? if yes which method we should use notruncate or truncate only?
This web site runs based on your votes. Please indicate all the helpful answers below by clicking on the thumbs up next to them. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
Hi SQLSkills [blogged][1] about trying to avoid shrinking database, but if you do need to then there is a suggested method in the link provided. However, if you "need" to then as the article advises perform an index re-org after the shrink. Hope this helps [1]:
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Shrinking database or it's data file never recommended as it cause fragmentation within data file. Still, if the free space is huge in data file and there is no space left on drive then you can shrink it using truncate only. Both options specified as notruncate and truncate only are meant for database and data files. Former one only move the allocated pages at end of file to the top where unallocated pages exists which can increase the free space and later one only shrink the end of file and release the free space back to OS based on size given. If no option specified then SQL server uses both method while shrinking database and data files. Shrinking database log file seems ok where some transactions increased the log file size and ate all space over the drive. This is as per me. I hope this answer your question.
17 People are following this question.
