September 14, 2012 at 9:42 am
In my server I have a few 10's of databases and on some of them I created a snapshot to 'freeze' them in a certain state. My users (=developers) can open the database snapshot to take a look at that frozen state.
Everything is done through Dynamics NAV clients and sometimes it decides to INSERT/UPDATE some table inside the snapshot. Of course this does not work but it seems to freeze the session. After that, other sessions connected to other databases start to freeze.
Killing all those sessions does not work either because most of them go into roll-back and the roll-back remains at 0%.
After a while the other sessions also freeze and the only option I have is to kill SQL server. After SQL Starts up again, in a few seconds all databases are again online and all works fine again.
The SQL server version is SQL 2008 R2 Build 10.50.2500.
Every time (luckily it happens quite rarely) the server starts hanging, I noticed there is a session with an INSERT-command (a 1-record insert) busy on the snapshot, so I think it is connected to that but I am not 100% sure.
I googled the internet and also searched the forum if there is some database snapshot-bug when inserting a record in a snapshot, but I didn't find anything.
Someone knows if there is a database snapshot + INSERT on it bug?
September 14, 2012 at 9:46 am
I was unaware that database snapshots were writable. Everything I've ever learned is that only the engine can write to a db snapshot and everyone else can only read off of it.
Or are you talking about snapshot replication?
September 14, 2012 at 9:53 am
Thank you for your reply.
No, I am talking about database snapshots.
And no, they are not writeable. It just happens that the Dynamics NAV-client decided to write some record to it. And because of that the session hangs and I am not able to kill it in SQL server. I would expect some error generated by SQL Server would be given back to the client but it doesn't happen.
September 14, 2012 at 11:42 am
kriki-503680 (9/14/2012)
Thank you for your reply.No, I am talking about database snapshots.
And no, they are not writeable. It just happens that the Dynamics NAV-client decided to write some record to it. And because of that the session hangs and I am not able to kill it in SQL server. I would expect some error generated by SQL Server would be given back to the client but it doesn't happen.
When a SPID gets hung, there is no error. Nothing has technically broke yet. All you can do is watch for things like this via the regular use of sp_who2 and other trace processes.
I do recommend, however, you get the vendor of that Dynamics NAV-client to fix the silly thing before it brings down your database. Or rename the snapshots to see if maybe it's mistaking the snapshots for the real databases.
September 16, 2012 at 1:07 pm
I did check for some time what it was doing. But the SPID was there on that insert-statement that should have generated a SQL-error in the first place because it was in a Snapshot. But it didn't and it continued consuming CPU.
I also know it is not the Dynamics NAV client (BTW: the vendor would be the same as the one of SQL Server:Microsoft) because I know very well how it works.
The fact that it does some unforeseen insert is because of configuration in the application I didn't care to remove. Why?
Because my firsts tests, months ago, I tried to insert a record and it generated a SQL error.
September 17, 2012 at 4:42 am
Okay, I'm a little confused. I thought you said it was the Dynamic NAV client that was doing the insert, now you're saying it's not.
If it's not the Dynamic NAV client causing the issue, you need to track down what is doing the INSERT and stop it from doing the INSERT (not wait for an error). The error isn't an error if it's a hung SPID. It can't even get to the point of generating an error if it's hung.
I don't know what else to tell you.
September 17, 2012 at 10:32 am
I wasn't clear on that: the INSERT IS generated by Dynamics NAV. But the problem is not that Dynamics NAV makes it hang. Even if Dynamics NAV is completely blocked (has happened a few times), it is still possible to kill the the SPID in SQL.
But in this case it is not even possible to kill the SPID which is very strange.
BTW: after a while also the sp_who and sp_who2 didn't respond any-more. Definitely NOT a Dynamics NAV problem.
I hoped it was some known problem (even if I couldn't find anything googling for it).
I'll keep monitoring to see if it happens again (luckily [or unluckily] it happens only once every few months).
Or maybe I should just remove the snapshots...
Thanks for your help anyway.
September 17, 2012 at 10:37 am
It doesn't make sense to me that a client tool outside of the actual SQL engine would be inserting data into a database snapshot. I do think you should call Microsoft and ask them to help you resolve the issue because I do think the hung SPID is related to this.
But that's just my opinion. I have no experience with Dynamic NAV to back it up.
September 18, 2012 at 1:50 am
The reason the application does it is because it thinks it is a normal database. In some test (at least a year ago) I tried out what it would do if I tried to insert a record and in fact I got back a nice error (as I expected and hoped) and I didn't get any.
I will see if it happens again and see if I can get some more info about the problem and then I will get in ouch with Microsoft.
September 18, 2012 at 7:15 am
But if Dynamics NAV is a Microsoft product, they should be smart enough to understand the difference between a snapshot and a real database. It's simple enough for anyone to read sys.databases and see if Source_Database_ID is NULL or not. If that column has a value, it means that the database is a database snapshot.
That's what is confusing me. The fact that it is so simple to tell the difference, yet the application apparently can't.
EDIT: Also, snapshots can't have the same name as the source database. So it has to deliberately be inserting into a database of a different name (the snapshot) to create this problem.
September 18, 2012 at 7:24 am
Most of the original code base for Dynamics Nav & AX (and maybe the other) products was written by a mixture of different vendors, Nav was a collaboration from Navision which merged with Damgaard and later took over by MS. Was the same for Dynamics AX, which was done by IBM and Damgaard which came in to the Navision portfolio and transfered when MS bought out NavisionDamgaard.
My experience with anything below and including Nav 2009, AX 2009 is that seemed to just be cobbled together to work with SQL, instead of Oracle and DB2 as most of the DB calls are wrapped in FETCH API CURSOR wrappers, so it doesnt surprise me that it cant tell the difference between a DB and a Snapshot. (In my opinion)
I would aim to see massive improvements in AX 2012 and Nav 2013 which from what I remember are more geared towards SQL as its RDBMS, but not yeat had the liberty to play with AX 2012.
September 18, 2012 at 7:37 am
Ah, a mystery unraveled. Thank you for clarifying, Anthony. That helps my understanding of the issue somewhat.
September 18, 2012 at 8:31 am
To complete what anthony.green wrote:
NAV is an ERP and was first designed to use its native database. Until version 2.50 it worked only with its native database. From 2.50 on, also SQL server was supported but it was so bad that in Belgium that version was never released (it was still Navision then).
Every version became better and better on SQL but it always had to support both native database as SQL server.
Now with NAV2013 the native database has been deprecated (finally!), the NAV product team can concentrate completely on SQL server.
But still: the code base is completely in C/AL (Pascal-like) and completely in NAV and thus still RBAR but at least they eliminated cursors and use MARS now.
It is the user that can decide to which DB connect (a snapshot is seen as a DB in NAV). NAV does NOT test (at least as far as I know) to see if it is connected to a snapshot or a real DB. Probably the product team never considered the possibility to connect to a snapshot.
I came up with that idea for a certain reason (I blogged about it: Native backup on SQL[/url] when I read about SQL snapshots).
But the point is still that when inserting a record, a SQL error should be generated (as seen in my blog), but the session (and even less SQL Server itself) should not hang. That is the reason I think that it is NOT a NAV problem but some problem in SQL Server with an INSERT in snapshots.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply