Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert record in DATABASE SNAPSHOT seems to block server Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 9:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:11 AM
Points: 72, Visits: 35,733
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?
Post #1359441
Posted Friday, September 14, 2012 9:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 5,584, Visits: 6,380
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?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1359453
Posted Friday, September 14, 2012 9:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:11 AM
Points: 72, Visits: 35,733
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.
Post #1359464
Posted Friday, September 14, 2012 11:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 5,584, Visits: 6,380
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1359533
Posted Sunday, September 16, 2012 1:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:11 AM
Points: 72, Visits: 35,733
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.
Post #1359911
Posted Monday, September 17, 2012 4:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 5,584, Visits: 6,380
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.



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1360090
Posted Monday, September 17, 2012 10:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:11 AM
Points: 72, Visits: 35,733
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.
Post #1360318
Posted Monday, September 17, 2012 10:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 5,584, Visits: 6,380
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1360324
Posted Tuesday, September 18, 2012 1:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:11 AM
Points: 72, Visits: 35,733
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.
Post #1360603
Posted Tuesday, September 18, 2012 7:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 5,584, Visits: 6,380
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1360769
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse