Delete the VLTB in sqlserver 2000

Question

Delete the VLTB in sqlserver 2000

balaji.ganga 68339

Right there with Babe

Points: 755
More actions
April 4, 2011 at 4:19 am

#104058

Hi
I'm having chalan table. it consists of 47 gb space. it will add 1 million records per day. So i need to remove the unwanted datas from chalan table. But the disk space is not sufficient.
If i delete the records.. the log size was full. Then the delete operation has been terminated.
I need to two things;;
1. To delete the unnecessary data.
2. we don't want the ltransaction og during delete operation.
Can anyone helps us to resolve this fix
regards
balaji g

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

balaji.ganga 68339 (4/4/2011)
I need to two things:
1. To delete the unnecessary data.
2. we don't want the transaction log during delete operation.

No, trust me, you really want it to be logged. Fortunately, there's no way to make it non-logged.

You simply have to decrease your batch size and run transaction log backups between batches.

See this great article by Lynn Pettis:

http://www.sqlservercentral.com/articles/T-SQL/67898/

Hope this helps

Gianluca

-- Gianluca Sartori

balaji.ganga 68339 Right there with Babe Points: 755 More actions · Answer 2

Gianluca Sartori (4/4/2011)
balaji.ganga 68339 (4/4/2011)
I need to two things:
1. To delete the unnecessary data.
2. we don't want the transaction log during delete operation.
No, trust me, you really want it to be logged. Fortunately, there's no way to make it non-logged.
You simply have to decrease your batch size and run transaction log backups between batches.
See this great article by Lynn Pettis:
http://www.sqlservercentral.com/articles/T-SQL/67898/
Hope this helps
Gianluca

Hi Gianlua,

Thanks for your quik response. I agree with you. But at the same time .

while performing the delete operation the log size would be increased drastically.

i used the dbcc shrink file un documented command. but its too late to delete the table.

Is there any options? please suggest me.. it will really helpful for me.

Thanks

Balaji G

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

Balaji,

1. There is no such thing as "no transaction log" - all operations are logged - it's just that whether or not you want it to be minimally logged, bulk-logged or fully logged. Also, I would always recommend using full logging

2. As far as containing the size of the log growth, refer my article on the same topic http://www.sqlservercentral.com/articles/T-SQL/72606/[/url]

Finally, why do you really need to use SHRINK? Shrink increases fragmentation, and will impact performance. Avoid using SHRINK unless you absolutely have to (I haven't used SHRINK for quite a while now - about 6 months - and all is perfectly fine).

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

balaji.ganga 68339 (4/4/2011)
while performing the delete operation the log size would be increased drastically.
i used the dbcc shrink file un documented command. but its too late to delete the table.
Is there any options? please suggest me.. it will really helpful for me.

If you take transaction log backups between batches and choose an appropriate batch size, your transaction log should not explode. If it still does, choose a smaller batch size.

-- Gianluca Sartori