How to delete large number of record without matter with transaction log?

Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> It happens to me that I have control over a MSSQL 2000 server that
>> was
>> looked after by another staff today. And than there is a call from the
>> server's owner company that the server's harddisk is running out of
>> space.
>> As background information, the SQL server's running on a 5GB HDD
>> partition with the Win2k system dir on it. And after some inspection, I
>> found the "msdb" database occupies about 1.13GB and it's transaction log
>> occupies 2MB.
>> So I opened Enterprise Manager and looked into the tables one by one.
>> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
>> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
>> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
>> cannot finish the task because the disk is running out of space and no
> room
>> for transaction log. And when I see the file size, the transaction log
> file
>> of msdb has grown to over 200MB. Oops.
>> At least, I managed to found an unoccupied machine to install a temp
> SQL
>> server, copy the database file to, trancate, and put back to the
>> origional
>> server. And the story is over. But what should I do if I experienced that
>> next time? There's management change in both my company and that company
> so
>> replacement of harddisk may not be feasible in a short time. And I'd like
> to
>> know if there's anything wrong in my procedure of handling the issue.
>> This
>> is my first time to handle a SQL server, and I have tough time on this. I
>> used to be a programmer only.
>> Looking forward for any advice. Thanks a lot.
>> Regards,
>> Lau Lei Cheong
>>
>|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> > Vyas's example shows how to divide a "big" transaction into a small ones
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > /*
> > DELETION here and Don't forget WHERE condition
> > */
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> > CHECKPOINT --Or doing BACKUP LOG File
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> > news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> >> Hello,
> >>
> >> It happens to me that I have control over a MSSQL 2000 server that
> >> was
> >> looked after by another staff today. And than there is a call from the
> >> server's owner company that the server's harddisk is running out of
> >> space.
> >>
> >> As background information, the SQL server's running on a 5GB HDD
> >> partition with the Win2k system dir on it. And after some inspection, I
> >> found the "msdb" database occupies about 1.13GB and it's transaction
log
> >> occupies 2MB.
> >>
> >> So I opened Enterprise Manager and looked into the tables one by
one.
> >> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> >> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the
"Query
> >> Analyzer"(I've explicitly called it to run the SQL statement) tell me
it
> >> cannot finish the task because the disk is running out of space and no
> > room
> >> for transaction log. And when I see the file size, the transaction log
> > file
> >> of msdb has grown to over 200MB. Oops.
> >>
> >> At least, I managed to found an unoccupied machine to install a
temp
> > SQL
> >> server, copy the database file to, trancate, and put back to the
> >> origional
> >> server. And the story is over. But what should I do if I experienced
that
> >> next time? There's management change in both my company and that
company
> > so
> >> replacement of harddisk may not be feasible in a short time. And I'd
like
> > to
> >> know if there's anything wrong in my procedure of handling the issue.
> >> This
> >> is my first time to handle a SQL server, and I have tough time on this.
I
> >> used to be a programmer only.
> >>
> >> Looking forward for any advice. Thanks a lot.
> >>
> >> Regards,
> >> Lau Lei Cheong
> >>
> >>
> >>
> >
> >
>