SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Just Deleting data will reclaim space??


Just Deleting data will reclaim space??

Author
Message
VastSQL
VastSQL
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9566 Visits: 5306
Hi Experts,

We have deleted 144101324 records from our WH database will it release the space to disk?

Thanks in Advance.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94747 Visits: 38956
Ratheesh.K.Nair (1/15/2013)
Hi Experts,

We have deleted 144101324 records from our WH database will it release the space to disk?

Thanks in Advance.


Do you mean to the database or the OS? If the former, yes; if the latter, no. To release space back to the OS you would have to shrink the database, and unless the space is critical and needed for other resources I wouldn't shrink the data.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
VastSQL
VastSQL
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9566 Visits: 5306
Thanks for quick response Lynn.

I mean to OS..
VastSQL
VastSQL
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9566 Visits: 5306
Dev team have created queries to remove duplicate records from DB and it is removing this many records per year starting from 2009. Is it good or will it cause any overhead. Database is in SIMPLE recovery mode so log growth is not am concerned about.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94747 Visits: 38956
Not knowing the data that is being delete or the system itself, I couldn't tell you. I would recommend rebuilding the the clustered and non-clustered indexes on the tables if you are deleting large number of rows for specific tables.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
SQL Show
SQL Show
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 1078
DELETE WITH (TABLOCK) will immediatly release space to database. If you want it to OS, then you need to shrink the database/file.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24586 Visits: 13698
Ratheesh.K.Nair (1/15/2013)
Dev team have created queries to remove duplicate records from DB and it is removing this many records per year starting from 2009. Is it good or will it cause any overhead. Database is in SIMPLE recovery mode so log growth is not am concerned about.


If the deletes are batched up you won't have log growth problems but if the delete is one large transaction you could still get log growth problems

---------------------------------------------------------------------
VastSQL
VastSQL
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9566 Visits: 5306
Thanks SQL Show & George
VastSQL
VastSQL
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9566 Visits: 5306
george sibbald (1/16/2013)
Ratheesh.K.Nair (1/15/2013)
Dev team have created queries to remove duplicate records from DB and it is removing this many records per year starting from 2009. Is it good or will it cause any overhead. Database is in SIMPLE recovery mode so log growth is not am concerned about.


If the deletes are batched up you won't have log growth problems but if the delete is one large transaction you could still get log growth problems


The query is deleting duplicate records its fetching data using cursor.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24586 Visits: 13698
if its deleting one at a time dont expect problems with the log but it will run slowly.

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search