Data, Design, and SQL Server - All Comments

re: Transaction count during DML statements

Dimitri Furman — Fri, 12 Apr 2013 16:02:19 GMT

@Varun: Yes, ACID is certainly in effect for single DML statements in SQL Server, with or without an explicit transaction.

re: Transaction count during DML statements

Varun — Fri, 12 Apr 2013 15:30:36 GMT

Very nice finding and explanation. I was troubleshooting my application and was wondering where the second count of transaction is coming from.

So can I infer from this, if ran my DML(delete) statement without an explicit transaction, sql server will take care of rolling back stuff in case anything weird happen during the execution, to maintain Database Atomicity and Consistency.

re: Transaction count during DML statements

JRStern — Tue, 29 Jan 2013 18:57:51 GMT

Thank you for posting this - nice if Microsoft added this note to the BOL entries for the associated DMVs!

re: Yet another cause of Kerberos authentication failure connecting to SQL Server

Dimitri Furman — Mon, 08 Oct 2012 19:23:32 GMT

@ManishKumar1980: I used an internal tool named SSPIClient. It provides a trace of API calls made when establishing a Kerberos connection. There is some info about the tool in this CSS blog: blogs.msdn.com/.../when-in-doubt-reboot.aspx.

In theory, you should be able to obtain the same info from a network trace, but it wouldn't be nearly as simple as with the tool.

re: Yet another cause of Kerberos authentication failure connecting to SQL Server

ManishKumar1980 — Mon, 08 Oct 2012 18:03:38 GMT

How did you trace authentication process on client.(The next step was to trace the authentication process on the client.....)

re: Getting out of single user mode

Rick Murphy — Fri, 28 Sep 2012 18:32:54 GMT

I had this issue where a User database was in single user mode and could not tell what spid have the connection. I used SSMS set results to Grid and then ran sp_who. This blewup and through an error because the DB was in Single User Mode. I reviewed the out out of the query just before it blewup and thankfully sp_who lists SPIDs in order! The last spid I saw was 385, so I ran the "Kill 386" and killed the next spid. Than I ran "sp_dboption DatabaseName, 'single', false". This worked. :)

re: Getting out of single user mode

Dimitri Furman — Mon, 06 Aug 2012 16:37:50 GMT

Technically, you could kill the session, since it would be the only non-system session against that database. However, killing an application session on a production system may not be something you could just do at will, as it could possibly break the application. Even if you kill the session, with sufficiently high level of activity (as was the case in this example), another application session will immediately grab the single-mode connection, and you will be back to square one.

re: Getting out of single user mode

SQL_Jay — Mon, 06 Aug 2012 14:57:41 GMT

Any reason why we can't kill the session for that particular database? Even if the spid is changing its going against that particular db correct?

Thanks

re: Estimating data compression savings for entire database

Eric Stephani — Mon, 02 Jul 2012 16:05:06 GMT

Cool. Thanks for posting this. Was exactly what I needed.

re: Getting out of single user mode

Scott Bradford — Wed, 13 Jun 2012 11:21:37 GMT

This totally worked for me. I had a database that was in single-user mode, and executing the statement to put it back into multi-user mode was continuing to fail *because* the db was in single-user mode.