Solved

delete command maintenance

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

Thanks.
0
Comment
Question by:arunbhatt
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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