Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Timeout in select * from [master].sys.all_objects and SQL Prompt timeout Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 2:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 10:53 AM
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

Post #1498042
Posted Wednesday, September 25, 2013 5:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1498269
Posted Wednesday, September 25, 2013 6:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 10:53 AM
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
Post #1498289
Posted Wednesday, September 25, 2013 6:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1498294
Posted Wednesday, September 25, 2013 6:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1498295
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse