LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:

> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt
.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.

>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> > Hi,
> > The log of the TEMPDB database is growing and growing. What should i do ?
> > What did i arrive to this situation?
> > For information we are using SSIS and OLAP, maybe it will help to answer.
> > Thanks.
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>> Hi,
>> The log of the TEMPDB database is growing and growing. What should i do ?
>> What did i arrive to this situation?
>> For information we are using SSIS and OLAP, maybe it will help to answer.
>> Thanks.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.
Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.

Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.
|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:

> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>
|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.

>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
sql

Log Grow

Hi,
Simple question but, for me, not obvious.
SQL Server 2000 + SP4.
My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
Recovery model simple.
1o Question - Why is growing the tlog for tempdb ?
I could shrink the log but this is just a workaround because in 4, 5 days i
have the same situation again.
2o Question - What could i do '
Thanks in advance
CMCCC
http://www.aspfaq.com/show.asp?id=2446
"CC" <CC@.discussions.microsoft.com> wrote in message
news:EA9F5E5A-043C-4D62-B0F8-BDD4C89A8F3E@.microsoft.com...
> Hi,
> Simple question but, for me, not obvious.
> SQL Server 2000 + SP4.
> My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
> Recovery model simple.
> 1? Question - Why is growing the tlog for tempdb ?
> I could shrink the log but this is just a workaround because in 4, 5 days
> i
> have the same situation again.
> 2? Question - What could i do '
> Thanks in advance
> CMC
>
>

Log Grow

Hi,
Simple question but, for me, not obvious.
SQL Server 2000 + SP4.
My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
Recovery model simple.
1º Question - Why is growing the tlog for tempdb ?
I could shrink the log but this is just a workaround because in 4, 5 days i
have the same situation again.
2º Question - What could i do '
Thanks in advance
CMCCC
http://www.aspfaq.com/show.asp?id=2446
"CC" <CC@.discussions.microsoft.com> wrote in message
news:EA9F5E5A-043C-4D62-B0F8-BDD4C89A8F3E@.microsoft.com...
> Hi,
> Simple question but, for me, not obvious.
> SQL Server 2000 + SP4.
> My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
> Recovery model simple.
> 1? Question - Why is growing the tlog for tempdb ?
> I could shrink the log but this is just a workaround because in 4, 5 days
> i
> have the same situation again.
> 2? Question - What could i do '
> Thanks in advance
> CMC
>
>

Log for tempdb is full

I keep getting the following error in my SQL 2K SP3a server logs:
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space..
But using the EM I cannot backup the transaction logs - the options are
grayed out. If I try to backup the tempdb through EM, I get an error that
Backup and Restore are not allowed on tempdb. I have the options set to
allow unlimited growth. There are many GB's of space on the drive. What is
the problem/solution?
TIA!
http://www.aspfaq.com/2446
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>
|||Perhaps there's code in some application that has an open transaction that
has run amok?
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>

Log for tempdb is full

I keep getting the following error in my SQL 2K SP3a server logs:
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space..
But using the EM I cannot backup the transaction logs - the options are
grayed out. If I try to backup the tempdb through EM, I get an error that
Backup and Restore are not allowed on tempdb. I have the options set to
allow unlimited growth. There are many GB's of space on the drive. What is
the problem/solution?
TIA!http://www.aspfaq.com/2446
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>|||Perhaps there's code in some application that has an open transaction that
has run amok?
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>

Log for tempdb is full

I keep getting the following error in my SQL 2K SP3a server logs:
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space..
But using the EM I cannot backup the transaction logs - the options are
grayed out. If I try to backup the tempdb through EM, I get an error that
Backup and Restore are not allowed on tempdb. I have the options set to
allow unlimited growth. There are many GB's of space on the drive. What is
the problem/solution?
TIA!http://www.aspfaq.com/2446
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>|||Perhaps there's code in some application that has an open transaction that
has run amok?
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>

Log files

I am getting the error message ,
"Could not allocate space for object '(SYSTEM table id: -1021390423)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full."
when i run a select stmt. I am thinking it's because my log file is full.
Can you pls tell me what i am supposed to do now?Read the error message closely, as it explains the problem very well. The pr
oblem is the tempdb
database. It is not the transaction log for tempdb, it is the database files
. They are not large
enough, and sometimes autogrow doesn't grow fast enough for the space needed
by the query
processing. Either pre.allocate storage for tempdb, or tweak the query (look
at query plan, add
indexes, modify the SQL etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>I am getting the error message ,
> "Could not allocate space for object '(SYSTEM table id: -1021390423)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full."
> when i run a select stmt. I am thinking it's because my log file is full.
> Can you pls tell me what i am supposed to do now?|||How do I make them grow?
"Tibor Karaszi" wrote:

> Read the error message closely, as it explains the problem very well. The
problem is the tempdb
> database. It is not the transaction log for tempdb, it is the database fil
es. They are not large
> enough, and sometimes autogrow doesn't grow fast enough for the space need
ed by the query
> processing. Either pre.allocate storage for tempdb, or tweak the query (lo
ok at query plan, add
> indexes, modify the SQL etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>
>|||Should i go to properties,data files and increase the size for space
allocated? Pls reply. Thanks in advance.
"PH" wrote:
> How do I make them grow?
> "Tibor Karaszi" wrote:
>|||Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:BA24839D-8D6E-4263-999A-2CF015CFAAA0@.microsoft.com...
> Should i go to properties,data files and increase the size for space
> allocated? Pls reply. Thanks in advance.
> "PH" wrote:
>

log file for tempdb is full

I continue to get this message in event viewer for SQL Server 2000 "The log
file for database tempdb is full. Backup the transaction log for the
database to free up some log space."
How can I fix this problem?Check out this link
http://www.aspfaq.com/show.asp?id=2446
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthlink.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>|||This indicates that you need to increaes the log file for a tempdb. The size
required depends on your application's use of tempdb.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthlink.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>

log file for tempdb is full

I continue to get this message in event viewer for SQL Server 2000 "The log
file for database tempdb is full. Backup the transaction log for the
database to free up some log space."
How can I fix this problem?Check out this link
http://www.aspfaq.com/show.asp?id=2446
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthlink.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>|||This indicates that you need to increaes the log file for a tempdb. The size
required depends on your application's use of tempdb.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthlink.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>

log file for tempdb is full

I continue to get this message in event viewer for SQL Server 2000 "The log
file for database tempdb is full. Backup the transaction log for the
database to free up some log space."
How can I fix this problem?
Check out this link
http://www.aspfaq.com/show.asp?id=2446
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthli nk.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>
|||This indicates that you need to increaes the log file for a tempdb. The size
required depends on your application's use of tempdb.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthli nk.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>

log file for database tempdb is full

Hi

I am getting this common error once or twice a day:

Error: 9002, Severity: 17, State: 2
The log file for database 'tempdb' is full. Back up the transaction
log for the database to free up some log space.

provided.....

1. My log file drive has more than 20 GB free out of 30 GB
2. Both data file & log file has default setting on unrestricted file
growth by 10%
3. Currently we moved from SQL 7.0 to SQL 2000 & the load in the user
side also doubled
4. We can't do the temporary solution like restarting the server or
SQL service, because the application is a real time system with much
less manual interaction.

Thanks in advance.

Regards
SeniSenthuran (senthurs@.yahoo.com) writes:
> Error: 9002, Severity: 17, State: 2
> The log file for database 'tempdb' is full. Back up the transaction
> log for the database to free up some log space.
> provided.....
> 1. My log file drive has more than 20 GB free out of 30 GB

It appears that you have some operations that take a serious load in
tempdb. Could be worktables, could be temptables that grow a lot,
and which see a lot of updates. I am afraid that you need to track
down which operations this might be.

When you say that there is 20 GB free, is this just when you have
gotten this message, of after you have restarted SQL Server?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mike
are u using some syntax such as
select xxx into #abc from xyz ?
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>