How long shrink database sql server

Now i am stuck in a situation where i have to shrink the datafile to reclaim disk space. Do you think your query would be helpful in this scenario or should i just go ahead and shrink the datafile the usual way. In my case what would be the ideal increments? Thanks again for the wonderful script You must be logged in to reply to this topic. Login to reply. February 4, at am The server is primarily used as a spare where I restore databases to.

You also may need to put tempdb on its own disk. February 4, at pm Some times it may take many hours If you have LOB in your db February 6, at am February 6, at pm Cheers, Todd. You have transaction log and DB backups running daily..?? Thanks , Shekhar.

Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog.

Podcast Explaining the semiconductor shortage, and how it might end. Does ES6 make JavaScript frameworks obsolete? Featured on Meta. Now live: A fully responsive profile. Related 0. Hot Network Questions. If desired, please make a choice:. Anonymous evaluation for troubleshooting and further development. Google Analytics. Loading details. Accept selection Accept nothing Accept all.

Accept selection. Our ldf file grew to this size because there were about eight million "delete" actions run on the Sales. Customers table in the database. Notice also the available free space on the drive has decreased when we were expecting an increase.

This is due primarily to the increased ldf file size and the size of the mdf file retaining at that peak reserved space. SQL Server offers a couple of ways you can shrink the database and file size.

Like many things, this will boil down to personal preference. While some people prefer the GUI way of doing things, others will want to be more hands on and work in "terminal mode". In the left pane where your databases are listed, right-click on the "SampleDataBase" and from the "Tasks" option select "Shrink" then "Files", as in the image below. On the next dialog box, make sure the File type is set to "Data" to shrink the mdf file.

Near the bottom of the dialog box you have three radio buttons with the options to Release unused space , Reorganize pages before releasing unused space or Empty file by migrating the data to other files in the same filegroup.

Once again, in the left pane where your databases are listed, right-click on the "SampleDataBase" and from the "Tasks" option select "Shrink" then "Files", as in the image below. On the next dialog box, change the File Type to Log to shrink the Log file size. Near the bottom of the dialog box you have three radio buttons with the same options.

The mdf file size should have reduced only slightly in size. Remember, although we removed approximately eight million rows of data, we did retain about two million rows and SQL is keeping some degree of reserve file space. Your numbers may not be exactly what you expected, but as long as they are close, then you know you have successfully completed the database "shrink" operation.

Bear in mind, your computer may be adding data to one or more other databases as you perform these functions so your numbers will not be exact.