My recommendation is not to do any create tables or select into (DDL) inside
a transaction when doing normal processing, these features where put in to
aid safe modification of database schema.
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.
> Creating a temp table will put locks on sysobject, syscolumns etc in
> Therefore any other transaction will be blocked on the tempdb. Create the
> tables in a small transaction, an continue processing in another
> >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
> >what this means is that the create table part locks sysobjects,
> >and sysindexes, then for the duration of the insert part the locks are
> >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
> >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
> >> when using temp tables, and then failing to release them, causing the
> >> DB to lock up pretty fast. Has anyone experienced something like this
> >> 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