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

Question

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

bryan 29523

SSC Enthusiast

Points: 114
More actions
September 24, 2013 at 2:48 pm

#298281

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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 SSC Enthusiast Points: 114 More actions · Answer 2

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 SSC Guru Points: 59351 More actions · Answer 3

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 SSC Guru Points: 59351 More actions · Answer 4

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;-)