Solved

delete command maintenance

Posted on 2010-09-18
10
142 Views
Last Modified: 2013-12-01
Hi,
  How to reclaim space after delete command on a table?

Thanks.
0
Comment
Question by:arunbhatt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33707724
You can shrink the database. you may also want to consider backing up the log file and then truncating it.

Lee
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33707818
after huge delete
you should rebuild table's clustered index
since huge delete will leave a lot of index fragment

and after that, shrink the database or shink all files in filegroup which that table keep on.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 334 total points
ID: 33717366
DBCC CLEANTABLE ( 0 , table_name [, batch_size_as_#_rows] )
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 3

Expert Comment

by:jvejskrab
ID: 36589774

DON'T USE SHRINK database or file !!!!!  It fragments indexes!!! Try other options instead of shrink !
0
 
LVL 7

Expert Comment

by:lundnak
ID: 38515670
Shrinking can be used if you need to.  However, just make sure to rebuild the clustered indexes when you are done shrinking.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38515711
Shrinking can also fragment heaps, and you can't easily defragment those.

Also, rebuilding the indexes will make the db grow again -- which kinda negates the effect of the shrink in the first place!
0
 
LVL 7

Assisted Solution

by:lundnak
lundnak earned 166 total points
ID: 38515853
@ScottPletcher:  I do agree with you.  My post was in response to the previous post.  I do not like these posts that tell people to never use DBCC Shrink.  Any seasoned veteran knows that tt always depends upon your database and the circumstance that you are in.  If I use DBCC ShrinkFile, I prefer to use the TRUNCATEONLY option.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 334 total points
ID: 38515964
Yes, you're right.  Sorry, I didn't see the post immediately previous to yours when I posted my last response.

Certainly there ARE times when a shrink is a good idea, usually after a clean up or after tuning the db and thus freeing up a significant amount of space.

You can reduce db growth when rebuilding indexes by specifying "SORT_IN_TEMPDB = ON", which I strongly urge anyone to use when rebuilding indexes after a shrink.  If you don't do that, you could easily re-grow your db back up to (or even over) the size it was before the shrink.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39687908
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question