Delete not releasing disk space

I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
generates a lot of data each day so I used DTS to backup data to access to
burn to dvd. Then I used a delete sql statement in query analyzer as such:
DELETE
FROM FireWallLog
WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
CONVERT(DATETIME, '2004-11-14', 102)
After a good 10 minutes or so, it comes back with hundreds of thousands of
rows affected, which is good. I then look at the database MDF and LDF files
and see that the MDF file didn't shrink any and the LDF grew in size, up to
a gig in size.
It is my understanding from reading SQL Books online that the delete
statement is supposed to open up disk space immediately. It doesn't seem to
be the case. My hard drive is about full (again .. I cleared other space up
earlier because once it fills up, the proxy server starts to crash - hard).
Thanks.
JimHi Jim
Massive deletes will deallocate space within the SQL Server files, for
re-use by the database, but will not remove space from the physical files.
Plus, as you noticed, deleting so many rows causing a lot of logging, which
could cause the log file to grow.
The only way to remove space from the files is to physically shrink the
database or its files.
Please read about DBCC SHRINKFILE and DBCC SHRINKDATABASE in the Books
Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jim in Arizona" <tiltowait@.hotmail.com> wrote in message
news:%23RcvGam0EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
> generates a lot of data each day so I used DTS to backup data to access to
> burn to dvd. Then I used a delete sql statement in query analyzer as such:
> DELETE
> FROM FireWallLog
> WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
> CONVERT(DATETIME, '2004-11-14', 102)
> After a good 10 minutes or so, it comes back with hundreds of thousands of
> rows affected, which is good. I then look at the database MDF and LDF
> files and see that the MDF file didn't shrink any and the LDF grew in
> size, up to a gig in size.
> It is my understanding from reading SQL Books online that the delete
> statement is supposed to open up disk space immediately. It doesn't seem
> to be the case. My hard drive is about full (again .. I cleared other
> space up earlier because once it fills up, the proxy server starts to
> crash - hard).
> Thanks.
> Jim
>
>
>|||In addition to Kalen's advice, please see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jim in Arizona" <tiltowait@.hotmail.com> wrote in message
news:#RcvGam0EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
> generates a lot of data each day so I used DTS to backup data to access to
> burn to dvd. Then I used a delete sql statement in query analyzer as such:
> DELETE
> FROM FireWallLog
> WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
> CONVERT(DATETIME, '2004-11-14', 102)
> After a good 10 minutes or so, it comes back with hundreds of thousands of
> rows affected, which is good. I then look at the database MDF and LDF
files
> and see that the MDF file didn't shrink any and the LDF grew in size, up
to
> a gig in size.
> It is my understanding from reading SQL Books online that the delete
> statement is supposed to open up disk space immediately. It doesn't seem
to
> be the case. My hard drive is about full (again .. I cleared other space
up
> earlier because once it fills up, the proxy server starts to crash -
hard).
> Thanks.
> Jim
>
>
>