Just Deleting data will reclaim space??

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 Administration » Just Deleting data will reclaim space??

Add to briefcase

14 posts, Page 1 of 2

1 2 »»

Just Deleting data will reclaim space??

Expand / Collapse

Author

Message

Ratheesh.K.Nair

Posted Tuesday, January 15, 2013 9:42 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415, Visits: 3,374

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

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

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

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)

Post #1407583

Ratheesh.K.Nair

Posted Tuesday, January 15, 2013 10:06 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415, Visits: 3,374

Thanks for quick response Lynn.

I mean to OS..

Post #1407586

Ratheesh.K.Nair

Posted Tuesday, January 15, 2013 10:12 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415, Visits: 3,374

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

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

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

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)

Post #1407589

Posted Wednesday, January 16, 2013 4:11 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:56 PM
Points: 299, Visits: 480

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

Group: General Forum Members
Last Login: Yesterday @ 10:09 AM
Points: 5,266, Visits: 11,197

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

Ratheesh.K.Nair

Posted Wednesday, January 16, 2013 5:59 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415, Visits: 3,374

Thanks SQL Show & George

Post #1407790

Ratheesh.K.Nair

Posted Wednesday, January 16, 2013 6:00 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415, Visits: 3,374

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

Group: General Forum Members
Last Login: Yesterday @ 10:09 AM
Points: 5,266, Visits: 11,197

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

14 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse