Can't see some of the rows in my SQL SERVER 2005 DB - table

Question

Can't see some of the rows in my SQL SERVER 2005 DB - table

NThacker

SSC Rookie

Points: 25
More actions
January 17, 2008 at 4:18 pm

#76872

Hello!
I am using SQL Server 2005. For one of the tables in my DB, I am not able to view all the rows. I know the total number of rows in that table because that information is logged in another table. One possible reason according to me could be that some of the rows got locked by some application that was accessing that DB. I even killed that application to make sure it was not accessing the DB. Still I could not view all the rows.
Someone please help me figure out what is going on and how I can resolve this.
Thanks in advance!

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

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

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

How many rows are we talking about here? What happens when you run Select count(*) from the table, then run Select * from the table? Do the records match? Do you know an ID from one of the "missing" rows? Can you try to select that one ID you know is there, and does it return? What happens when you select the top 100, then the top 100 ordered descending?

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

You can run DBCC OPENTRAN('dbname') to verify that there are no open transactions. Run the Activity Monitor will also let you know if a transaction is still open in the database (rolling back maybe). If there are open transactions, that could prevent you from seeing all the records.

Then again, maybe someone deleted some...

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

NThacker SSC Rookie Points: 25 More actions · Answer 3

Thanks Jeremy and Grant Fritchey for your reply!

Jeremy, when I tried to execute the select count(*) query, it kept on executing but did not finish execution. With select * query, after a very long time, it returned about 100 rows and was still executing and added 100 more rows and again kept executing...

In all, I had about 13000+ rows and when I opened the table directly, I could see 12000 of them. The process I was running on them (though one application) is -

i assigned a datareader to select some rows based on a flag(one of the fields in that table). For each row,I did some processing and then opened a new connection to update that flag through an update query. From the status of the application, I know it selected about 1700 files and out of them some 290 files were processed and update query run on them.

I beleive when I select some rows through datareader, it should not lock the rows. Please correct me if I am wrong.

Grant Fritchey, I will test the suggestion you gave and get back with the results asap.

Thanks again!

NThacker SSC Rookie Points: 25 More actions · Answer 4

For anyone who faced a smilar situation, I went to 'Management' in the SQL Server (it is there at the same level the 'Databases', 'Security', etc) -> 'SQL Server Logs' -> 'Activity Moniter' -> 'View Processes'.

A window with all the processes popped up. There I killed processes related to my troubled DB. On doing so, all my locked rows were visible as normal.

Now, to avoid this situation, I modified my select statment to 'Select ColumnName from TableName with (NOLOCK)'. And in mu update query, I added a 'with (ROWLOCK)'. After that I never had any locking issue. 🙂

Hope this helps...

Thanx Everyone!