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

  • ganci.mark

    Hall of Fame

    Points: 3517

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721524

    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: 3517

    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

    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: 3517

    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: 721524

    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

    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);

  • 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]

  • ganci.mark

    Hall of Fame

    Points: 3517

    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: 997364

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • ganci.mark

    Hall of Fame

    Points: 3517

    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

    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

    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);

  • 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]

  • Jeff Moden

    SSC Guru

    Points: 997364

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • daneillen

    SSC Veteran

    Points: 233

    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.

  • Viewing 14 posts - 1 through 14 (of 14 total)

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