Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

332 Posts

Posted - 04/08/2013 :  15:26:34  Show Profile  Reply with Quote

Our monitoring tool has reported that blocking on prodution

i checked the details

one user is running adhoc query on standalone database(not production database)

one more query is running by the same user against tempdb

SELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], AS [Name] FROM sys.tables AS tbl WHERE ( not like '#%') ORDER BY [Schema] ASC,[Name] ASC

is this Intellisense feature? (which helps the user to make correct SQL Queries)

one of production process (running aginst production db) is blocked by this user temp db query.

standalone database running on the same server production

So my question is user is trying to run adhoc query in standalone database and production db process is blocked. Just trying to understand how SQL works (because nothing in appln client or our server processes accesses this standalone databse)

Please clarify.

Edited by - laddu on 04/08/2013 15:55:06

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 04/08/2013 :  16:28:35  Show Profile  Reply with Quote
The query you have posted should complete within milliseconds unless the user explicitly acquired some type of lock and did not release it. If you see that query running for a long time, there is something not right with that session/query.

Is the query run by the user on the standalone database accessing any resources (tables/views) from the production database? If it does, that may point something.

Another possibility (although you said it is BLOCKing), is taht your server resources might be being taxed by the standalone db query.

Also, the tempdb is a shared resource, shared by all databases on the server. So there could be resource contention. Also, if there are global temp tables used that again could cause interactions between databases where you think there may be none.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000