Solved

delete command maintenance

Posted on 2010-09-18
10
136 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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