deleting table phisically

hello
I delete a table from a DataBase in enterprise manager(drop table) ,
but it is not deleted phisically and hard's free space does not
increas.
How can I delete table phisically?
thanksYou need to shrink the database to recover disk space on the drive...
"nsh" <nsh5776@.yahoo.com> wrote in message
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||How do you know the table is not deleted phisically? because of the database
file (.mdf) file?
Most likely you just vacated space within the .mdf file.
Back up the database, then try to shrink it.
using Enterprise manager, Right mouse on the database -> All Tasks ->
Shrink dbase
You can also see how much space is being used in the .mdf file grafically
using Enterprise manager, Right mouse on the database -> View -> Task
Pad
Hope this helps
"nsh" <nsh5776@.yahoo.com> escribió en el mensaje
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||thanks for helping.
Before delete table .mdf & .ldf size are the same after delete table
(each table size is about 5GB ).
when I make Data Base ,I set Autoshrink .Is not it enough?
please help me ...
thanks
"Marcelo" <marcelo.no@.spam.santiago.cl> wrote in message news:<#YAUSvkRDHA.1324@.TK2MSFTNGP11.phx.gbl>...
> How do you know the table is not deleted phisically? because of the database
> file (.mdf) file?
> Most likely you just vacated space within the .mdf file.
> Back up the database, then try to shrink it.
> using Enterprise manager, Right mouse on the database -> All Tasks ->
> Shrink dbase
> You can also see how much space is being used in the .mdf file grafically
> using Enterprise manager, Right mouse on the database -> View -> Task
> Pad
> Hope this helps
> "nsh" <nsh5776@.yahoo.com> escribió en el mensaje
> news:534bf457.0307091048.714766fe@.posting.google.com...
> > hello
> > I delete a table from a DataBase in enterprise manager(drop table) ,
> > but it is not deleted phisically and hard's free space does not
> > increas.
> > How can I delete table phisically?
> > thanks|||> when I make Data Base ,I set Autoshrink .Is not it enough?
No, because it doesn't auto-shrink every single time the database changes --
this would cause a performance nightmare.

Deleting ReportServer TempDB

Hi, I need to free up some space in my harddrive and was wondering what the effects of deleting the TempDB would be. I am not using this machine to host any important reports. (I am using it as a development machine). my question is what happens when i delete the tempdb will it break the system and will i have to reinstall reporting services to get my reports working again?

You should never delete the ReportServerTempDB - RS will no longer work afterwards.

The RS windows service should automatically cleanup entries in the ReportServerTempDB after they are expired (e.g. sessions expire after 10 minutes).

If the ReportServerTempDB allocates a lot of disk space, but the database is actually almost empty, you can try to compact / shrink the database (through SQL Server Management Studio) to reduce its footprint on the hard disk.

-- Robert

sql

Deleting records to save DISK Space...

Hi,

In my data archiving process , I would end up deleting hunders records from the production databases but would that help me save some DISK space immediately? Should I run some DBCC command to get some disk space ?

if SO ..!! What should I do after deleting the records..??

Thanks
Cheriyan."Always be sure to backup your records ... before you delete them ... "|||Have a look in SQL Book online to this command


DBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)|||Kim Tripp presented a good session at the PASS conference a couple weeks ago about removing data using filegroups. If you are a PASS member, you can download the presentation here:

http://ew.sqlpass.org/ew/pass/callpapers/attach/TRIPP-Rolling_Range_For_Print.zip

Anyone can also download her scripts here:

http://www.sqlskills.com/pastConferences.asp

Deleting Older Files

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

LystraAs far as the full backup, you can overwrite the old backup file every time you take a full backup. This way you don't need to reserve the space for two full backup file. In the case of log backup, if you don't need to or don't want to backup the log file, just use simple recovery mode. It looks like you are deleting them any way.|||That's the problem it is not deleting the older files, even though I have it check to delete the files.

The database recovery mode is set to full.

Lystra|||Drives are cheap....

And what happens when you need to migrate data around?

Do you have a disaster box?

How odten do you dump the transaction log?

How many dumps do you have now?

How big is the hard drive?|||I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra|||Not to sound like a broken record.

I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra

Deleting filesystem files

Hi All,
I need a generic solution to delete backup files from filesystem in order to
reduce space.
Can this be done using the extended sp "xp_cmdshell"? If yes, can any body
show me an example on how to achieve this.
ie.., i want to write a SP and add it to SQL Jobs. I want that SP to delete
all files in a specific folder which are older then "N" number of hours.
Its really very urgent. Can anyone give me a quick solution / sample.
Regards
PrasanthIF you are using a Maintenance plan for creating the backups, then you have
an option when you are setting up the plan through the wizard where you can
use the option
"Remove files older than"
If you cannot do that, you can use a vbscript to automate the delete.
On how to do that.. check out this link..
[url]http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp[/u
rl]
Hope this helps.|||As omnibuzz suggested vbscript is the way to go.
If at you are not interest in that for some reason! then try these
suggestions:
1. How about writing a batch script to do this job. THen you can call this
bat file within a sqljob and schedule it.
2. Write a windows application to do and then schedule it using Windows
Scheduler.
3. Write a Windows service to do this job.
Best Regards
Vadivel
http://vadivel.blogspot.com
"SqlBeginner" wrote:

> Hi All,
> I need a generic solution to delete backup files from filesystem in order
to
> reduce space.
> Can this be done using the extended sp "xp_cmdshell"? If yes, can any body
> show me an example on how to achieve this.
> ie.., i want to write a SP and add it to SQL Jobs. I want that SP to delet
e
> all files in a specific folder which are older then "N" number of hours.
> Its really very urgent. Can anyone give me a quick solution / sample.
> Regards
> Prasanth

Deleting data with least impact

I have a large database - 33 million rows - VERY WIDE table and am
running out of disk space - lovely ...
I have to move 7 million records off the table - no biggie - I'll just
dts the records to another server.
My problem is deleting the remaining data to make room on the server
without impacting/logging too much.
Any help would be appreciated.
Thanks,
CraigOn Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> I have a large database - 33 million rows - VERY WIDE table and am
> running out of disk space - lovely ...
> I have to move 7 million records off the table - no biggie - I'll just
> dts the records to another server.
> My problem is deleting the remaining data to make room on the server
> without impacting/logging too much.
> Any help would be appreciated.
> Thanks,
> Craig
truncate|||On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> I have a large database - 33 million rows - VERY WIDE table and am
> running out of disk space - lovely ...
> I have to move 7 million records off the table - no biggie - I'll just
> dts the records to another server.
> My problem is deleting the remaining data to make room on the server
> without impacting/logging too much.
> Any help would be appreciated.
> Thanks,
> Craig
you can truncate the table one the data is moved out.|||On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> > I have a large database - 33 million rows - VERY WIDE table and am
> > running out of disk space - lovely ...
> > I have to move 7 million records off the table - no biggie - I'll just
> > dts the records to another server.
> > My problem is deleting the remaining data to make room on the server
> > without impacting/logging too much.
> > Any help would be appreciated.
> > Thanks,
> > Craig
> you can truncate the table one the data is moved out.
I will only need to delete 7 m rows of the 33 m rows ... so I need a
good way to do that.
Craig|||On Sep 12, 11:27 am, Craig <csomb...@.gmail.com> wrote:
> On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>
>
> > On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> > > I have a large database - 33 million rows - VERY WIDE table and am
> > > running out of disk space - lovely ...
> > > I have to move 7 million records off the table - no biggie - I'll just
> > > dts the records to another server.
> > > My problem is deleting the remaining data to make room on the server
> > > without impacting/logging too much.
> > > Any help would be appreciated.
> > > Thanks,
> > > Craig
> > you can truncate the table one the data is moved out.
> I will only need to delete 7 m rows of the 33 m rows ... so I need a
> good way to do that.
> Craig- Hide quoted text -
> - Show quoted text -
I need to look at the delete statement. How long it takes now?|||Do not forget changing your Recovery Model temporarily to "Simple Recovery
Model". And before doing this, backup your Transaction Log, otherwise log
chain will be broken and you will not be able to restore your database to
the point where you changed your recovery model if you encounter a problem.
After your deleting operation, change your Recovery Model back to FULL.
(After changing your recovery model to FULL, back up your transaction log
again to prevent breaking the log chain)
The aim of changing your recovery model is not to log all this 7million
deletion to the transaction log file and blow it up. You change your
recovery model to prevent this. Because in this operation (as you already
lack of free space on your disks) if you keep FULL recovery model, then it
would log all this deletion operation and it probably raise an "out of
space" error and halt the process of deletion or whatever.
Here's a link that you can obtain more info abour Recovery Models from:
http://msdn2.microsoft.com/en-us/library/ms366344.aspx
--
Ekrem Önsoy
"Craig" <csomberg@.gmail.com> wrote in message
news:1189574876.579528.58280@.r29g2000hsg.googlegroups.com...
> On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>
> I will only need to delete 7 m rows of the 33 m rows ... so I need a
> good way to do that.
> Craig
>|||On Tue, 11 Sep 2007 22:27:56 -0700, Craig wrote:
>On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>
>I will only need to delete 7 m rows of the 33 m rows ... so I need a
>good way to do that.
>Craig
Hi Craig,
A common technique is to split the delete in batches, like this:
DECLARE @.rc int;
SET @.rc = 1;
WHILE @.rc <> 0
BEGIN;
DELETE TOP(100000)
FROM YourTable
WHERE whatever has to be deleted;
SET @.rc = @.@.ROWCOUNT;
END;
In SQL Server 2000, DELETE TOP(..) is not supported - instead, use SET
ROWCOUNT 100000 in the beginning and SET ROWCOUNT 0 at the end of the
script. Also, for all versions, you might need to experiment to find the
ideal batch size.
IIf your recovery model is full, either switch temporarily to simple (as
sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||> IIf your recovery model is full, either switch temporarily to simple (as
> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
Now, above doesn't jive in a SQL Server forum. I think you meant:
CASE WHEN recovery model is full THEN switch temporarily to simple ...
(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with your
problem, Craig...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60tge3pj8h8jk3d3kohjahp2gh6s345595@.4ax.com...
> On Tue, 11 Sep 2007 22:27:56 -0700, Craig wrote:
>>On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>>
>>I will only need to delete 7 m rows of the 33 m rows ... so I need a
>>good way to do that.
>>Craig
> Hi Craig,
> A common technique is to split the delete in batches, like this:
> DECLARE @.rc int;
> SET @.rc = 1;
> WHILE @.rc <> 0
> BEGIN;
> DELETE TOP(100000)
> FROM YourTable
> WHERE whatever has to be deleted;
> SET @.rc = @.@.ROWCOUNT;
> END;
> In SQL Server 2000, DELETE TOP(..) is not supported - instead, use SET
> ROWCOUNT 100000 in the beginning and SET ROWCOUNT 0 at the end of the
> script. Also, for all versions, you might need to experiment to find the
> ideal batch size.
> IIf your recovery model is full, either switch temporarily to simple (as
> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Thu, 13 Sep 2007 09:52:56 +0200, Tibor Karaszi wrote:
>> IIf your recovery model is full, either switch temporarily to simple (as
>> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
>Now, above doesn't jive in a SQL Server forum. I think you meant:
>CASE WHEN recovery model is full THEN switch temporarily to simple ...
Hey, Tibor,
You are not actually using CASE as a *statement*, now are you? Off you
go to the nearest C++ newsgroup!!
>(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with your
>problem, Craig...)
And neither could I ... :-)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||> You are not actually using CASE as a *statement*, now are you? Off you
> go to the nearest C++ newsgroup!!
I guess my wishes for a closer integration with ANSI SQL PSM syntax influences me... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:rhije3lgrsc2476uhtfig6ue17iiv3b1s8@.4ax.com...
> On Thu, 13 Sep 2007 09:52:56 +0200, Tibor Karaszi wrote:
>> IIf your recovery model is full, either switch temporarily to simple (as
>> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
>>Now, above doesn't jive in a SQL Server forum. I think you meant:
>>CASE WHEN recovery model is full THEN switch temporarily to simple ...
> Hey, Tibor,
> You are not actually using CASE as a *statement*, now are you? Off you
> go to the nearest C++ newsgroup!!
>>(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with
>>your
>>problem, Craig...)
> And neither could I ... :-)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Deleted Records space

Am new to SQL2000, but have worked with other RDBMS's.
I am wondering what the procedure is to recover the space taken up in a
database by records marked for deletion?
Is this something automatically done as part of the shrink database procedure?
Kind Regards,
Naj
Hi
As soon as a row is deleted, it's space can be occupied by other data. It is
best to re-index the clustered keys as this will re-organize the DB to it's
original fill factor again. Shrink DB will not help you in this case.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
> Am new to SQL2000, but have worked with other RDBMS's.
> I am wondering what the procedure is to recover the space taken up in a
> database by records marked for deletion?
> Is this something automatically done as part of the shrink database
> procedure?
> Kind Regards,
> Naj
|||Thanks very much for the information Mike!
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> As soon as a row is deleted, it's space can be occupied by other data. It is
> best to re-index the clustered keys as this will re-organize the DB to it's
> original fill factor again. Shrink DB will not help you in this case.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
> news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
>
>

Deleted Records space

Am new to SQL2000, but have worked with other RDBMS's.
I am wondering what the procedure is to recover the space taken up in a
database by records marked for deletion?
Is this something automatically done as part of the shrink database procedure?
Kind Regards,
NajHi
As soon as a row is deleted, it's space can be occupied by other data. It is
best to re-index the clustered keys as this will re-organize the DB to it's
original fill factor again. Shrink DB will not help you in this case.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
> Am new to SQL2000, but have worked with other RDBMS's.
> I am wondering what the procedure is to recover the space taken up in a
> database by records marked for deletion?
> Is this something automatically done as part of the shrink database
> procedure?
> Kind Regards,
> Naj|||Thanks very much for the information Mike!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> As soon as a row is deleted, it's space can be occupied by other data. It is
> best to re-index the clustered keys as this will re-organize the DB to it's
> original fill factor again. Shrink DB will not help you in this case.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
> news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
> > Am new to SQL2000, but have worked with other RDBMS's.
> > I am wondering what the procedure is to recover the space taken up in a
> > database by records marked for deletion?
> >
> > Is this something automatically done as part of the shrink database
> > procedure?
> >
> > Kind Regards,
> > Naj
>
>

Deleted Records space

Am new to SQL2000, but have worked with other RDBMS's.
I am wondering what the procedure is to recover the space taken up in a
database by records marked for deletion?
Is this something automatically done as part of the shrink database procedur
e?
Kind Regards,
NajHi
As soon as a row is deleted, it's space can be occupied by other data. It is
best to re-index the clustered keys as this will re-organize the DB to it's
original fill factor again. Shrink DB will not help you in this case.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
> Am new to SQL2000, but have worked with other RDBMS's.
> I am wondering what the procedure is to recover the space taken up in a
> database by records marked for deletion?
> Is this something automatically done as part of the shrink database
> procedure?
> Kind Regards,
> Naj|||Thanks very much for the information Mike!
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> As soon as a row is deleted, it's space can be occupied by other data. It
is
> best to re-index the clustered keys as this will re-organize the DB to it'
s
> original fill factor again. Shrink DB will not help you in this case.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
> news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
>
>

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.
Jim
Hi 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
>
>
>

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
>
>
>

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
>
>
>