Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2013-04-08 : 15:26:34
|
| Hi,Our monitoring tool has reported that blocking on produtioni checked the detailsone user is running adhoc query on standalone database(not production database)one more query is running by the same user against tempdbSELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.name AS [Name] FROM sys.tables AS tbl WHERE (tbl.name not like '#%') ORDER BY [Schema] ASC,[Name] ASCis 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 productionSo 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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-08 : 16:28:35
|
| 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. |
 |
|
|
|
|
|
blocking