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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 why we might go to database detach

Author  Topic 

Haja Mohideen
Starting Member

12 Posts

Posted - 2014-11-26 : 03:48:58
I have connected the database TESTDB from ssms then When i was try to execute simple query or complex queries.
It doesnt executed. It took's very long time and execution not completed.

This issue occured in production database at last week. I was tried with many ways but didn't worked out.
Finally, I HAVE DETACH DATABASE LIKE BELOW,
TESTDB --> TASK --> DETACH --> DROP CONNECTIONS BOX(Tick it) --> click OK.
Finally, after detach finished successfully database worked properly query executed successfully.

My question is that what was the Actual Issue when query was not Executed. Once i did detached database then that issue has resolved.
so what was happened, I want some detailed Information about the Actual Issue when query was not executed.
Thanks in advance


Nothing is impossible

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-11-26 : 04:35:15
Some process might had blocked your process due to which it took long time and didn't complete. when you have detached the db, all the existing connections are lost which made your query to execute successfully

Hema Sunder
Go to Top of Page

Haja Mohideen
Starting Member

12 Posts

Posted - 2014-11-28 : 01:09:43
Dear sunder.bugatha
Thanks for your reply. yes absolutely u r right there were some process had blocked. But i want exactly what was the Actual Issue, Which process been blocked.

I just want what is exact Issue we faced while simple or complex query took long time to execute(even execution not completed)

Nothing is impossible
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-11-30 : 02:29:28
You can trap the process and kill the process - rathen than detaching the database. This post outlines different techniques to detect and resolve blocking issues - http://www.sqlserver-dba.com/2012/11/sql-server-how-to-detect-and-troubleshoot-blocking-and-deadlocks.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-12-01 : 03:49:42
Run one of the below to find out the blocking/blocked processes :

SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED<>0

OR

SP_WHO2

Hema Sunder
Go to Top of Page
   

- Advertisement -