Solved

delete command maintenance

Posted on 2010-09-18
10
126 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:
ScottPletcher earned 334 total points
ID: 33717366
DBCC CLEANTABLE ( 0 , table_name [, batch_size_as_#_rows] )
0
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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:ScottPletcher
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:ScottPletcher
ScottPletcher 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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now