File Shrinking

I have a data file that was created from an ISA web Proxy application that
is (being used by our networking department) 50 gig in size, and I would
like it to be around 5 gig if possible. The problem is that it was
initially creatred at 38 gig, so I cannnot shrink it below this amount. It
has no free space allocated!!! I thought i could even delete some hundred
thousand records from the one and only table in the database, but i can't
even do that without it timing out, or telling me the transaction log is
full. I'm using simple recovery, so i thought the log file would not grow
upon this huge deletion transaction but it did grow enormously. I then
disabled automatic file growth on the log, but it tells me the log file is
full and it won't perform the transaction. I have shrunk the transaction
log down to 500K, but still nothing. I'm using sql server 2005, with
windows xp.
Overall, how can i make this data file smaller? I have tried everything I
know with the transaction log, and manually deleting , but nothing seems to
work.
Error messages:
The transaction log for database 'ISAWebProxyLog' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases (this column says NOTHING)
or
A problem occurred attempting to delete row 1. Error source:
Microsoft.visualstudio.datatools. Error Message: The row values updated or
deleted either do not make the row unique or they alter multiple rows.
Correct the errors and attempt to delete the row again.
That is why you should never create the files that large to begin with.
Create them relatively small then manually grow them to the size you need
for the next year or so. But in any case you can delete the rows in small
batches so that the tran log can be truncated in-between batches.
SET ROWCOUNT 50000
WHILE 1 = 1
BEGIN
DELETE FROM Table WHERE xxx
IF @.@.ROWCOUNT = 0
EXIT
END
SET ROWCOUNT 0
Another alternative is to export the data you wish to keep. Drop the DB and
create a new one with a smaller size. Then import the data back in.
Andrew J. Kelly SQL MVP
"Matt Fritz" <mafritz@.state.pa.us> wrote in message
news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>I have a data file that was created from an ISA web Proxy application that
> is (being used by our networking department) 50 gig in size, and I would
> like it to be around 5 gig if possible. The problem is that it was
> initially creatred at 38 gig, so I cannnot shrink it below this amount.
> It
> has no free space allocated!!! I thought i could even delete some hundred
> thousand records from the one and only table in the database, but i can't
> even do that without it timing out, or telling me the transaction log is
> full. I'm using simple recovery, so i thought the log file would not grow
> upon this huge deletion transaction but it did grow enormously. I then
> disabled automatic file growth on the log, but it tells me the log file is
> full and it won't perform the transaction. I have shrunk the transaction
> log down to 500K, but still nothing. I'm using sql server 2005, with
> windows xp.
> Overall, how can i make this data file smaller? I have tried everything I
> know with the transaction log, and manually deleting , but nothing seems
> to
> work.
> Error messages:
> The transaction log for database 'ISAWebProxyLog' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases (this column says NOTHING)
> or
> A problem occurred attempting to delete row 1. Error source:
> Microsoft.visualstudio.datatools. Error Message: The row values updated
> or
> deleted either do not make the row unique or they alter multiple rows.
> Correct the errors and attempt to delete the row again.
>
|||The second error message you indicate lets me think that your table is an
Heap (no indexes nor primary key). In some cases this situation causes this
kind of error
Gilberto Zampatti
"Andrew J. Kelly" wrote:

> That is why you should never create the files that large to begin with.
> Create them relatively small then manually grow them to the size you need
> for the next year or so. But in any case you can delete the rows in small
> batches so that the tran log can be truncated in-between batches.
> SET ROWCOUNT 50000
> WHILE 1 = 1
> BEGIN
> DELETE FROM Table WHERE xxx
> IF @.@.ROWCOUNT = 0
> EXIT
> END
> SET ROWCOUNT 0
>
> Another alternative is to export the data you wish to keep. Drop the DB and
> create a new one with a smaller size. Then import the data back in.
> --
> Andrew J. Kelly SQL MVP
> "Matt Fritz" <mafritz@.state.pa.us> wrote in message
> news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>
>