CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SqlLearner
SqlLearner

Reputation: 793

Unable to access one of the table in SQL Server database

I have a production database which has 200 tables. Since last week I am unable to access one of the tables. When I just select top 100 rows it keeps on running.

How can I find out why the table is not accessible? How can I find if there is any lock on the table? All the other tables are running fine

Upvotes: 2

Views: 13977

Answers (2)

SouravA
SouravA

Reputation: 5253

From what I understood, you are not able to get any results when you query it.

There may be a lot of reasons for that.

1) It could be locked. To do a dirty read, try querying with NOLOCK hint.

SELECT Column1 FROM TableName WITH (NOLOCK)

To check if there are locks on the table, use the script below:

declare @a table (
spid int,
[dbid] int,
objid int,
indid int,
[type] varchar(10),
resource varchar(100),
mode varchar(2),
[status] varchar(20)
);

insert into @a
exec sp_lock

select object_name(objid) tablename, * from @a where object_name(objid) = 'TableName'

2) Queries might be slow when statistics are outdated. Try updating them.

UPDATE STATISTICS dbo.TableName;

3) The TOP operator itself. The top operator basically takes the entire set of data and sorts it and gives you the first 100. You can add query hints to get some data before it is sorted. 

SELECT TOP 10 Column1 FROM TableName (OPTION FAST(1))
--Have avoided doing a `SELECT * FROM....`

SELECT 1 FROM TableName (OPTION FAST(1))
--Without `TOP`

Upvotes: 7

Andy
Andy

Reputation: 55

Check Permissions on the table. Right click on the table and select properties. Click on the permissions tab and make sure you have access to that table.

Upvotes: 0

Related Questions