Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

delete command maintenance

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

Thanks.
0
Comment
Question by:arunbhatt
9 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 70

Accepted Solution

by:
Scott Pletcher earned 1336 total points
ID: 33717366
DBCC CLEANTABLE ( 0 , table_name [, batch_size_as_#_rows] )
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 70

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 664 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1336 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 23

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 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