Posts by Hemantgiri Goswami

Moving MSDB to a New Location

In recent past we had a situation where in, we were required to move MSDB, Model and Master databases to a new location, the reason being a faulty drive. While moving the system databases to the new location we needed to be extra cautious. Let’s see the process step-by-step.

Download free eBook for SQL Server 2012

Microsoft has released one more free eBook, this time it is on SQL Server 2012. This is the second draft of this book. SQL Server MVP Ross Mistry and Stacia Misner has covered Administration and BI part, there are 10 chapters in total divided in two parts, they are:

When DBCC INPUTBUFFER disappoints

Today we have an alert from one of the client server about blocking,Yes, I’ve found the culprit SPID that is occupying more resources, using DBCC INPUTBUFFER(SPID). so, instead of seeing what exactly this SPID is doing I have received SP_EXECUTESQL, surprised!!! No, it’s bound to happen when SPID is running dynamic TSQL (using SP_EXECUTESQL) and/or cursor. But, yes, at the same time I would like to know what it is running behind the scene. There are two options that came to my mind

Using NOLOCK Hint

Use NOLOCK hint to avoid block – this is what I have often heard/see in many forums I participate in, during local user group events and meetings. I have always advised that its not that good idea to use hints, as it may cause data corruption and blocking. And anyways, there are many things that you can do to avoid blocking, like:

Troubleshooting SSPI handshake error

I have seen lots of questions floating in the msdn forums about SSPI Handshake error, the main question is around error code 0×80090311 which is indicative of the authentication failure error. Apart from this, there are many other reasons for SSPI Handshake error to appear in error logs. I cover them here.

Page 2 of 212