REQUEST_FOR_DEADLOCK_SEARCH

I don’t want to make the readers feel bored of reading some repetitive things. So I am not explaining what is a deadlock how it occurs instead I felt it would be good if I mention what and how  SQL Server engine does internally about Deadlock.

Let me take you through a DMV called sys.dm_os_wait_stats. If you notice the columns that it uses, you will see something like …no Exactly like REQUEST_FOR_DEADLOCK_SEARCH. This is the guy we will be centering today.

 

What is this Doing????

This is basically a sleepless thread/process that wakes up every five seconds to see if we have any deadlocks. Wow…what a nice Guy!!!  If it finds any, it terminates one of the sessions. That’s true, it KILLS one of the two (multiple) sessions to let the other session have all the resources it needs.  So we don’t have to worry, right?

Well, SQL Server does have to worry a bit, even if it’s not going to prison.  So how does it decide which transaction to be killed?

It decides this by looking at the LOG USED value. Now what is that?

 Say if you have high Transaction Log usage (more Transactions), you will have a high log used value for that session (SPID). Consider another session with low value on LOG USED. It’s obvious that the second one will be easier to rollback. Because if SQL Server kills a transactions, any work it has done must be rolled back to bring the database to consistent state.  So it chooses a low value SPID and kills.

So what happens when the value are the same? 

It uses sort of LIFO (Last In First Out) method and kill whoever came in second.

How it Works????

It wakes up every 5 seconds to check for deadlocks. If it finds any, it follows above process to decide how to resolve it.  However, second time it wakes up right away, to make sure it is not a nested deadlock.  If there is one it kills that and goes back to sleep.  Next time it wakes up @4.90 seconds (I believe it’s actually 10ms).  It will keep going down to as small as 100ms; that is, it will wake up even 10 times per second to deal with Deadlock Issues.

 

So hope this was not a boring(For someone) but instead shed some light on the internal thread.

Have a Gr8 Day!! Njoy

Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)

I found this error while I was watching for alerts in my new environment. I know this is a common and a silly error for DBA's on a senior level. I have intended to share this because I think it may at least help Newbies...
Issue

The Restore Job part of  a Maintenance Plan was failing with the above error. It was trying to get the backup file from one of the Shared Network Location. The job was running under SQL Server Service account and the error was

Executed as user: xxxx Cannot open backup device '\\xxx\d$\project\filetransfer\xxx.bak'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed

.

Solution
Even after giving FULL permissions to service account for the folder and even Root (D Drive) it was failing. But when we changed the way we were calling the backup location it worked.

RESTORE DATABASE XXX
FROM DISK = '\\XXX\project\filetransfer\xxx.bak'.

Since the folder 'project' was shared, we omitted D$ and gave the shared folder name directly, which infact was the issue here.

Moral Of the Story:- Backups and Restores with Network locations are always Scary!!! :)



Hope this helps someone...Cheers and Njoy your day!!!