SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Timeout in select * from [master].sys.all_objects and SQL Prompt timeout


Timeout in select * from [master].sys.all_objects and SQL Prompt timeout

Author
Message
bryan 29523
bryan 29523
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 14
I have a situation where SQL Prompt times out reading the DB objects when I open SQL Manager. Red-Gate suggested I run:

select * from [master].sys.all_objects;



This will timeout. It appears that there is a bad record that is causing the select to hang.

I changed it to this so I could try narrowing down the bad record:
select TOP 1000 * from [master].sys.all_objects ORDER BY [OBJECT_ID];



It hangs if I try to select any more than 740 records.

Can anyone have suggest a way of troubleshooting this, or clearing it out?

It's a production server, but there only 6 databases. I am considering detaching the DBs, wiping out the instance and re-attaching them.

Can anyone point me to a description of the process for wiping out the instance?

Thanks,
Bryan
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5242 Visits: 4076
bryan 29523 (9/24/2013)
It's a production server, but there only 6 databases. I am considering detaching the DBs, wiping out the instance and re-attaching them.
NO.......

Dont try this ..there are very much chances that you wont able to attched them successfully.

TRy one thing .. have you try to query any user data base with sql prompt .

TRy to uninstall Sql prompt (if possible ) then do your querying.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
bryan 29523
bryan 29523
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 14
I have uninstalled SQL Prompt, but I have the same issue.

I am not sure why wiping out the SQL instance and recreating it would be a problem. Wouldn't be the same as when I set up the server in the first place?

Thanks
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5242 Visits: 4076
try sp_lock and see where you have LOCK- X records , check the Spid for that in Sp_who2

and try this too
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5242 Visits: 4076
check the states of all sys data bases too

select state_desc, * from  sys.databases.



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search