Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Just Deleting data will reclaim space?? Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 9:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:44 AM
Points: 2,634, Visits: 3,985
Hi Experts,

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

Thanks in Advance.
Post #1407581
Posted Tuesday, January 15, 2013 9:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 20,865, Visits: 32,903
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.



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)
Post #1407583
Posted Tuesday, January 15, 2013 10:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:44 AM
Points: 2,634, Visits: 3,985
Thanks for quick response Lynn.

I mean to OS..

Post #1407586
Posted Tuesday, January 15, 2013 10:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:44 AM
Points: 2,634, Visits: 3,985
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.
Post #1407587
Posted Tuesday, January 15, 2013 10:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 20,865, Visits: 32,903
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.



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)
Post #1407589
Posted Wednesday, January 16, 2013 4:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
DELETE WITH (TABLOCK) will immediatly release space to database. If you want it to OS, then you need to shrink the database/file.
Post #1407733
Posted Wednesday, January 16, 2013 4:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 5,888, Visits: 13,063
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


---------------------------------------------------------------------

Post #1407736
Posted Wednesday, January 16, 2013 5:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:44 AM
Points: 2,634, Visits: 3,985
Thanks SQL Show & George
Post #1407790
Posted Wednesday, January 16, 2013 6:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:44 AM
Points: 2,634, Visits: 3,985
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.
Post #1407792
Posted Wednesday, January 16, 2013 8:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 5,888, Visits: 13,063
if its deleting one at a time dont expect problems with the log but it will run slowly.

---------------------------------------------------------------------

Post #1407857
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse