question

xhani avatar image
xhani 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?
databaseshrink-databaseshrink-log-fileshrink
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why do you want to shrink it?
0 Likes 0 ·
research purposes
0 Likes 0 ·
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.
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

NeerajTripathi avatar image
NeerajTripathi answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.