TEMPDB full
in [Programming SQL]
|
Prev: how to capture messages from SMO ExecuteWithResults()
Next: Cannot drop schema because it is being referenced by object 'SqlQueryNotificationService'
From: Kalyan on 14 Dec 2006 10:59 Step. i am doing 1. Drop Table1. 2. Select * into Table1 from Table2 join with 3 tables. Reason for drop statment is, i have three indexes on this table, instead of drop index and truncate table, i opted for dropped and recreated this table. In SQL Server 2000, if I run this query it took about 2 hrs. and Table1 will have about 50 million rows. Duration is acceptable since job is running in the night while no other job is running. Here is my question. Same Query with same data if i run on SQL Server 2005, After about 1 hrs, i am getting tempdb is full. (avilable space on my drive is 220 GB before start of transaction, Tempdb is autogrow by 10%.) Query is simple by joing 3 tables left outer join. Is there any way to do commit every 1 miilion rows, I am using select * into Any suggestions to avoid the tempdb full. Following msg. i got when i ran this query as job The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed. I checked log_reuse_wait_desc for that table value in log_reuse_wait_desc is NOTHING and log_reuse_wait column is 0 Any help Thanks Kalyan
From: Alejandro Mesa on 14 Dec 2006 11:05
Kalyan, See if this can help to understand what is happening. Row Versioning Resource Usage http://msdn2.microsoft.com/en-us/library/ms175492.aspx AMB "Kalyan" wrote: > Step. i am doing > > 1. Drop Table1. > 2. Select * into Table1 from Table2 join with 3 tables. > > Reason for drop statment is, i have three indexes on this table, instead of > drop index and truncate table, i opted for dropped and recreated this table. > > > In SQL Server 2000, if I run this query it took about 2 hrs. and Table1 will > have about 50 million rows. Duration is acceptable since job is running in > the night while no other job is running. > > Here is my question. > > Same Query with same data if i run on SQL Server 2005, After about 1 hrs, i > am getting tempdb is full. (avilable space on my drive is 220 GB before > start of transaction, Tempdb is autogrow by 10%.) > > > Query is simple by joing 3 tables left outer join. > > Is there any way to do commit every 1 miilion rows, I am using > > select * into > > Any suggestions to avoid the tempdb full. > > Following msg. i got when i ran this query as job > > The transaction log for database 'tempdb' is full. To find out why space in > the log cannot be reused, see the log_reuse_wait_desc column in sys.databases > [SQLSTATE 42000] (Error 9002). The step failed. > > I checked log_reuse_wait_desc for that table > value in log_reuse_wait_desc is NOTHING and log_reuse_wait column is 0 > > Any help > > Thanks > Kalyan > |