Table Growth Whilst Deleting? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Table Growth Whilst Deleting?

Author

Topic

Kinnerton
Starting Member

21 Posts

Posted - 2012-03-02 : 05:33:07

Hi,

(On SQL 2005 Standard)

I'm trying to delete chunks of old data from a large logging table. Following excellent advice from these forums, I am deleting in chunks with row-locking. This seems to going fine.

The weird thing is that I've deleted around 30 million rows (approx 30Gb) and there has been a consistent growth being reported by sp_spaceused.

Granted, there is data being added - but nowhere near as much as I've taken out.

Has anyone any ideas what's happening?

Oh, yes. Statistics have been refreshed.

Cheers,

Kinnerton

Kinnerton
Starting Member

21 Posts

Posted - 2012-03-02 : 05:44:54

I've just come across : http://support.microsoft.com/kb/913399

It says that a heap table delete doesn't release until a tablock occurs. I'm using rowlocking for my deletion script, does anyone know if running the code with a tablock (out-of-production-hours) will release the space I've already deleted?

Thanks.

Kinnerton
Starting Member

21 Posts

Posted - 2012-03-06 : 08:21:41

Is it a dumb question? Or does nobody know? :)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-06 : 11:40:20

I don't think the db will decrease at all automatically. (Depending on autogrow settings).

the engine figures that if the table were that big before it will be again so why move shit around.

I'm not sure though.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 12:19:39

what is your recovery model?

After your massive delete, unless you perform maintenance, your log will continue to grow, even as you remove the data.

You have top remember, SQL Server is making sure it can recover for you in case you need to

Also, even if you delete ALL of the data, the data file will not shrink unless you tell it to do so with DBCC

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kinnerton
Starting Member

21 Posts

Posted - 2012-03-16 : 05:28:08

Hi,

Thanks for the responses.

I'm not trying to reduce the physical footprint of the DB - as you say, that would be a pointless exercise. I'm trying to reduce reserved space from a massive logging table (deleting 6 month+ data) for other tables to use.

I'm in full recovery model.

Cheers,

Kinnerton

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-16 : 05:54:18

Wait until ghost clean up has finished.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources