Shrink data file after deleting 20,000 rows
Question
Tuesday, July 16, 2013 3:59 PM
i have deleated about 20,000 rows from my database that held about 70 Gig of data,
now i want to reclaim the free space.
How can i do it.
Regards
Khurram
k
All replies (10)
Wednesday, July 17, 2013 4:42 AM âś…Answered | 2 votes
Generally SQL server does not shrink the database automatically after deleting or dropping a lot of rows from the database. It happens very rarely. To reclaim the empty space you should run DBCC SHRINKFILE or DBCC SHRINKDATABSE. I prefer DBCC SHRINKFILE. See the following article for the referrence:
How do I reclaim space in SQL Server?
Thanks,
Tuesday, July 16, 2013 4:13 PM
Take transaction log backup it will shrink space...Even if it does not try running shrink manually.
Before running above run DBCC LOGINFO(DB_NAME) if last value in status col is ZERO you can shrink else u have to take trnlog backup first and then shrink.
Caution: Shrinking should be rare activity
Srry missed about data file, It will automatically release space,after your transaction commits.
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Tuesday, July 16, 2013 4:20 PM
I was reluctant to give u this article as i feel shrinking datafile is really bad idea but in wort case scenario where u want to shrink refer to this article
http://blog.sqlauthority.com/2009/01/25/sql-server-shrinking-ndf-and-mdf-files-a-safe-operation/
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Tuesday, July 16, 2013 4:24 PM
i have deleated about 20,000 rows from my database that held about 70 Gig of data,
now i want to reclaim the free space.
How can i do it.
Regards
Khurram
k
To shrink and reclaim the space. You should use dbcc shrinkfile and need to shrink the data files. Tkaing log backup will not shrink.
Muthukkumaran Kaliyamoorthy
Helping SQL DBAs and Developers >>> SqlserverBlogForum
Tuesday, July 16, 2013 4:52 PM
i mean shrinking the data file
k
Tuesday, July 16, 2013 5:16 PM | 1 vote
What is the recovery model ? If its full recovery model, take a transaction-log backup and then shink the files with truncateonly option
Satheesh
Tuesday, July 16, 2013 6:08 PM
Shrinking database is not recommended way.alter database recovery model to simple and shrink the file.
Tuesday, July 16, 2013 6:55 PM
How important is it to re-claim that storage? How much is it worth? See http://www.karaszi.com/SQLServer/info_dont_shrink.asp. If you still want to do this, then just check out the DBCC SHRINKFILE command. A very straight-forward operation.
Tibor Karaszi, SQL Server MVP | web | blog
Tuesday, July 16, 2013 7:17 PM
Depends on how you want to reuse the free space.
If you delete rows from a table with a clustered index, unused space in the table is the result. Pages that have become completely empty will be released to the database for general reuse within the database. Unused space on pages that are not empty can be "freed" by reindexing.
If you delete rows from a heap (a table without a clustered index) without using a tablock hint, unused space in the heap is the result. Empty pages will not be released. The only way to reclaim these pages is to (temporarily or structurally) add a clustered index.
If you need the free space returned to your file system - in other words, if the "70 Gig of data" needs to come down - then you can reclaim this space by shrinking the database.
You should think twice before you shrink the database. Because first of all, shrinking may increase logical fragmentation, and because of that may affect performance. Secondly, if the database needs to grow afterwards, and there has been other disk activity, physical fragmentation may increase, which again could negatively affect performance.
Gert-Jan
Tuesday, July 23, 2013 6:57 AM
from SSMS, change back up type from full to simple.
Shrink the log file and change back up to full mode again. you full claim full space that log file occupied
Thanks, Rajendra