• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

delete command maintenance

Hi,
  How to reclaim space after delete command on a table?

Thanks.
0
arunbhatt
Asked:
arunbhatt
3 Solutions
 
Lee SavidgeCommented:
You can shrink the database. you may also want to consider backing up the log file and then truncating it.

Lee
0
 
JoeNuvoCommented:
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
 
Scott PletcherSenior DBACommented:
DBCC CLEANTABLE ( 0 , table_name [, batch_size_as_#_rows] )
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
jvejskrabCommented:

DON'T USE SHRINK database or file !!!!!  It fragments indexes!!! Try other options instead of shrink !
0
 
lundnakCommented:
Shrinking can be used if you need to.  However, just make sure to rebuild the clustered indexes when you are done shrinking.
0
 
Scott PletcherSenior DBACommented:
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
 
lundnakCommented:
@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
 
Scott PletcherSenior DBACommented:
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
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now