Very Computer

>Hi everyone-

>We have an intermittent problem with SQLServer 6.5 putting locks on tempdb
>when using temp tables, and then failing to release them, causing the whole
>DB to lock up pretty fast. Has anyone experienced something like this or
>knows of circumstances that might mitigate it?
>We're using Weblogic's JDBC driver to connect to SQLServer.
>Many thanks in advance for any clues you can provide.

>Ulf

>Ulf,

>A change of behaviour was done between previous version of SQL Server and
>6.5, in 6.5 the complete SELECT .. INTO table is done inside a transaction,
>what this means is that the create table part locks sysobjects, syscolumns
>and sysindexes, then for the duration of the insert part the locks are
held.
>This has the effect of blocking other users from creating tables.

>In service pack 1 a trace flag was introduced (5302) that changes this
>behaviour back to how it was before, the create table and insert are
>seperate and the locking problem goes away. I use this trace flag countless
>numbers of times, there is only one reported bug to do with union and I
>think views.

>--
>Tony Rogerson MCP,SQL Server MVP
>Torver Computer Consultants Ltd.

>> Hi everyone-

>> We have an intermittent problem with SQLServer 6.5 putting locks on
tempdb
>> when using temp tables, and then failing to release them, causing the
>whole
>> DB to lock up pretty fast. Has anyone experienced something like this or
>> knows of circumstances that might mitigate it?
>> We're using Weblogic's JDBC driver to connect to SQLServer.
>> Many thanks in advance for any clues you can provide.

>> Ulf

> Creating a temp table will put locks on sysobject, syscolumns etc in
tempdb.
> Therefore any other transaction will be blocked on the tempdb.  Create the
> tables in a small transaction, an continue processing in another

> Dave

> >Ulf,

> >A change of behaviour was done between previous version of SQL Server and
> >6.5, in 6.5 the complete SELECT .. INTO table is done inside a
transaction,
> >what this means is that the create table part locks sysobjects,
syscolumns
> >and sysindexes, then for the duration of the insert part the locks are
> held.
> >This has the effect of blocking other users from creating tables.

> >In service pack 1 a trace flag was introduced (5302) that changes this
> >behaviour back to how it was before, the create table and insert are
> >seperate and the locking problem goes away. I use this trace flag
countless
> >numbers of times, there is only one reported bug to do with union and I
> >think views.

> >--
> >Tony Rogerson MCP,SQL Server MVP
> >Torver Computer Consultants Ltd.

> >> Hi everyone-

> >> We have an intermittent problem with SQLServer 6.5 putting locks on
> tempdb
> >> when using temp tables, and then failing to release them, causing the
> >whole
> >> DB to lock up pretty fast. Has anyone experienced something like this
or
> >> knows of circumstances that might mitigate it?
> >> We're using Weblogic's JDBC driver to connect to SQLServer.
> >> Many thanks in advance for any clues you can provide.

> >> Ulf