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?