After successful table insert no rows showing in SELECT output or SP_SPACEUSED output

Question

Post reply

After successful table insert no rows showing in SELECT output or SP_SPACEUSED output

ganci.mark

Hall of Fame

Points: 3533
More actions
June 24, 2011 at 3:05 pm

#243272

Something strange ?I ran into today.
After inserting rows into a table and receiving no errors and xx row(s) inserted messages,
Selecting from the table yielded no rows. Select count(*) resulted in 0.
SP_SPACEUSED resulted in 0 row.
I ran dbcc dbreindex on the table and no change(inserts successfull but no results on select)
There were no triggers on the table but I did notice a large number of statistics.
Just about one for every column and the index.
I removed all statistics except the one for the index becaus i wouldnt allow me too.
I then ran SP_UPDATESTATS.
Table still was empty but now when I insert the rows are there when I select.
What truly caused this bizzare behavior and how can I be certain this doesnt happen especially in a production environment?
Has anyone experiened this before?
Thanks
Mark G.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 1

Never seen anything like this. Unless you perhaps had implicit transactions off. Perhaps one started and didn't commit?

The SELECT should return the rows, however, if it's the same connection.

I would lean towards operator error, somewhere, but not sure where. Can you post the code you used?

ganci.mark Hall of Fame Points: 3533 More actions · Answer 2

Thanks for your reply.

This was first encountered by a developer before it was reported to me(DBA).

I confirmed the problem by repeating it on my own work station and management studio connection.

I had actually thought of the implicit transaction turned off possibility and tried to issue a commit and received Msg 3902 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Here is the code:

INSERT INTO JT056_GROUP_DETAIL (GRGR_CK, GRGR_ID)

VALUES (123445, '00405010')

SELECT * FROM JT056_GROUP_DETAIL

where the following is true

GRGR_CK int

GRGR_ID varchar(8)

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

Any triggers on the table that cause delete or rollback of the newly inserted rows?

Are you sure it is a table, not a view?

Just a thought.

ganci.mark Hall of Fame Points: 3533 More actions · Answer 4

Thanks for the Reply Nils.

I had already ruled out triggers and this is definately a table this was occurring on.

I know it is very strange.

I was able to repeat it over and over.

I even inserted a few hundred rows from the similar table in our Qual server to this table and every time the report was xxx row(s) inserted.

Yet no rows showed in selects or row counts.

It was only after removing all stats and running SP_UPDATESATS that I wasable to insert and then view the rows actually in the table.

Not sure if it was the removing the stats part or the sp_updatestats that fixed it?

I should have tested in between.

I was hoping this is a known issue with some sort of resolution as it concerns me.

Thx

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 5

I'd open a case with Microsoft. I've never heard of anything remotely like this with SQL Server that wasn't some programming mistake.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 6

Do you have a backup that contains the problem table?

It would be worth restoring that somewhere to perform some more tests.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ganci.mark Hall of Fame Points: 3533 More actions · Answer 7

Great idea Magoo. I restored a backup from that morning of the issue but unfortunately could not reproduce the issue. My next backup occurred after I "fixed" the problem.

I know the developers were doing stuff prior to reporting ths. I think I will see if they can recreate any steps that they had performed to see if we can create this again. I agree with Steve that there has to be some tie to some programmatic mistake or something the developers had done.

I was hoping to see this behavior again after the restore to have something to show Microsoft support.

All:

Thanks for all the ideas and input.

I will followup here when I learn anything new.

Thanks

Mark G.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 8

ganci.mark (6/24/2011)
Something strange ?I ran into today.
After inserting rows into a table and receiving no errors and xx row(s) inserted messages,
Selecting from the table yielded no rows. Select count(*) resulted in 0.
SP_SPACEUSED resulted in 0 row.
I ran dbcc dbreindex on the table and no change(inserts successfull but no results on select)
There were no triggers on the table but I did notice a large number of statistics.
Just about one for every column and the index.
I removed all statistics except the one for the index becaus i wouldnt allow me too.
I then ran SP_UPDATESTATS.
Table still was empty but now when I insert the rows are there when I select.
What truly caused this bizzare behavior and how can I be certain this doesnt happen especially in a production environment?
Has anyone experiened this before?
Thanks
Mark G.

I've seen this happen thousands of times and it'll happen again and again. There's actually no way to prevent it and no way that MS can fix it. It's a PICNIC problem where the user hits the "parse" button instead of the "execute" button. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ganci.mark Hall of Fame Points: 3533 More actions · Answer 9

Update:

Late yesterday the developer reported this occuring again so I investigated.

By this time I was convinced it had to be something they were doing.

I inserted a test record and this time I witnessed it in the table. Moments later it was gone! Now I know it had to be a developer process.

Sure enough profiler revealed a process that another group of developers had inadvertently turned on and it was running a delete on the table.

Stopped that process. Problem Solved.

I have to admit I had lost confidence in SQL Server.

Shame on me! 🙂

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 10

Yes, SHAME on you 😛

So what did we learn? Nothing actually; just another confirmation that all problems are caused by developers 🙂

mister.magoo SSC-Forever Points: 47068 More actions · Answer 11

Nils Gustav Stråbø (6/28/2011)
Yes, SHAME on you 😛
So what did we learn? Nothing actually; just another confirmation that all problems are caused by developers 🙂

No, what we learnt here was to run trace - which would have shown what was happening 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 12

ganci.mark (6/28/2011)
Update:
Late yesterday the developer reported this occuring again so I investigated.
By this time I was convinced it had to be something they were doing.
I inserted a test record and this time I witnessed it in the table. Moments later it was gone! Now I know it had to be a developer process.
Sure enough profiler revealed a process that another group of developers had inadvertently turned on and it was running a delete on the table.
Stopped that process. Problem Solved.
I have to admit I had lost confidence in SQL Server.
Shame on me! 🙂

BWAA-HAAA! Good gravy, Batman! I hope you're not letting them mash potatoes in production. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

daneillen SSC Veteran Points: 283 More actions · Answer 13

I had the same problem. It occurred after I restored a database with a different name ( while the same db was maintained on the server). I ensured that the physical and logical files were a different name than the db being restored. After that was complete and update statement I ran gave me a successful completion message, but in viewing the record there was no change. In that case I was able to change it by right clicking the table in SSMS and selecting 'edit top 200 rows'. Subsequently, I attempted an insert statement, and again I received a success message, but a select returned no rows. So, after checking settings in the properties and finding nothing amiss there I decided to take the database offline then bring it back online again. That was the solution. My insert statement worked.