Some comments in this forum might be helpful for you. Why on earth you are shrinking whole database, I am sure you are aware that it is bad but shrinking whole database is blunder.
You may stop it and shrink either data file or log file from where you want to extract space. Shrinkdatabase is even more evil than shrinkfile. Please check if the process is blocked? After reading an article from the guy who owned the shrink code at Microsoft sqlskills. SqlWorldWide thank you for the tip. Add a comment. Active Oldest Votes. Improve this answer. BradC BradC 9, 7 7 gold badges 42 42 silver badges 79 79 bronze badges.
Thank you for the clarification! Since our database only has one Filegroup created prior to me , the main thing that seems to be missing would be controlling the free-space left. Log files are mostly empty space anyway unless a large transaction is currently in progress , so "target percent free" isn't a useful way to think about it.
Sign up or log in Sign up using Google. Learn More. Sign in. 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. Friday, August 17, AM. 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. Okay, so you just must have that extra 3GB freed up on your drive that the mdf file is holding hostage. There is a way to do this with a few extra steps, but be very careful and follow these steps in the order they are laid out. The images below show the new mdf file size, the ldf file size and the free space on the hard drive.
As mentioned at the beginning of the article, this process will cause disk fragmentation. So, you really need to weigh your options and determine if this is right for you and your hardware.
Shrinking the database to free up space on your drive might a viable solution in a desperate situation, but it is not something I would recommend putting on any kind of recurring schedule. If you know your database will regrow to that max size or more on a regular basis, then your best option would be to leave the reserved space as is.
This simple change fore the test we did makes a difference of three gigs of storage. So, plan your database and tables carefully before you create them. In situations like the one in this article, it is always better to increase your drive size, as apposed to decreasing your data size.
Related Articles. How to rename a SQL Server database. How to determine SQL Server database transaction log usage. Understanding how SQL Server stores data in data files. Access is denied error when attaching a SQL Server database. Copy a SQL Server database with just the objects and no data. How to stop and start SQL Server services.
0コメント