How long shrink database

Possibly, by visualizing our data page allocation. Check out the following open-source project that I created for this purpose:. Another possible solution is to move your whole table somewhere outside the file entirely.

Whether it be to a different filegroup , or even a different database. Remember: Backups are your soul-mates. Moving your tables to different filegroups, dropping and creating indexes… Those are all serious schema changes, and may not always be possible.

To help you with this, I wrote the following script below. Change the parameters to fine-tune it for your use case:. Are you sure you wanna do it? Cover Image by Michal Jarmoluk from Pixabay. Hi Eitan: Is this script compatible with sql server R2? Well it throws me the following error ….

Like Like. Thanks, Raul! Yes, they had partitioned heaps. Like Liked by 1 person. You are commenting using your WordPress. You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email. Notify me of new posts via email. This site uses Akismet to reduce spam. Learn how your comment data is processed. Disclaimer Let me start off with a mandatory warning: as a rule of thumb, shrink operations are to be avoided, if possible.

Why are there 8 more pages in the current size than the actual used pages? SQL Server has also reserved one extent for housekeeping.

A larger value and perhaps a smaller factor would allow this to be run along with other work, although running it in the wee hours is preferred. Sometimes, shrinking a database is unavoidable. This happened to me after I applied table and index compression to a large database. However, shrinking a database is an expensive operation. Target Size: ' ,. TargetSize , ' MB. END ;. FROM sys. Author Recent Posts. Gerald Britton. United States English.

Ask a question. Quick access. Search related threads. Remove From My Forums. Answered by:. Archived Forums. Sign in to vote. Dear all, We're using sql server enterprise with sp2. Thanks a lot Wallace. The only advice I can give you is to engage PerfMon and either Profiler or a server-side trace to see what if any activity is going on.

It may very well be that this is the normal time SQL Server needs to do this shrink. But it may be hung and you can't see that hang through SSMS. Another thought is that if you're shrinking via SSMS on a non-local box client as opposed to server , you're not seeing the commands come through the network on a timely basis. As Alen said, do it in small chunks. I would add printing a message for each chunk so you know that something is happening.

Of course, it takes a while for the buffer to flush you'll a lot messages in bursts. This is a script I use:.