MS SQL Server - TempDB growth

Author

TempDB growth

cbrichards via SQLMonster.com

2006-11-28, 7:14 pm

Every month we archive (write to file using bcp) about 50 million rows to a
file, and then delete the data that was written to file. In the process of
deleting, the transaction log grows and grows and grows. This is done in a
stored procedure.

We have changed this procedure to only write to file and then delete, 5
million rows at a time. This keeps tempdb to a reasonable size.

We are running FULL recovery mode, SQL Server 2005, SP1, on Windows 2003.

Is there anyway to reduce the growth of tempdb when these deletes are taking
place?

--
Message posted via http://www.webservertalk.com

Hari Prasad

2006-11-29, 12:12 am

Hi,

First step is to reduce the batch size to 100,000 or low. So as tempdb
growth will be reduced.

take a look into the article:-

http://www.microsoft.com/technet/pr...withtempdb.mspx

Thanks
Hari

"cbrichards via webservertalk.com" <u3288@uwe> wrote in message
news:69fc2d8be0d89@u
we...
> Every month we archive (write to file using bcp) about 50 million rows to
> a
> file, and then delete the data that was written to file. In the process of
> deleting, the transaction log grows and grows and grows. This is done in a
> stored procedure.
>
> We have changed this procedure to only write to file and then delete, 5
> million rows at a time. This keeps tempdb to a reasonable size.
>
> We are running FULL recovery mode, SQL Server 2005, SP1, on Windows 2003.
>
> Is there anyway to reduce the growth of tempdb when these deletes are
> taking
> place?
>
> --
> Message posted via http://www.webservertalk.com
>

Tracy McKibben

2006-11-29, 7:12 pm

cbrichards via webservertalk.com wrote:
> Every month we archive (write to file using bcp) about 50 million rows to a
> file, and then delete the data that was written to file. In the process of
> deleting, the transaction log grows and grows and grows. This is done in a
> stored procedure.
>
> We have changed this procedure to only write to file and then delete, 5
> million rows at a time. This keeps tempdb to a reasonable size.
>
> We are running FULL recovery mode, SQL Server 2005, SP1, on Windows 2003.
>
> Is there anyway to reduce the growth of tempdb when these deletes are taking
> place?
>

Using smaller batches is really the only option. You're asking SQL to
delete 5 million rows at once, all within the same transaction (batch).
The transaction log must be large enough to hold that entire
transaction, no way around it.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Sponsored Links