SQL DB stuck in Single User Mode

I recently ran into an issue with SQL 2012 database. One of the automated process changed database into a single user mode and then failed. Luckily this database was only a QA database so bringing it backup was not a priority. I tried to bring it back by

   1:  ALTER DATABASE [DB_Name] SET MULTI_USER;

   2:  GO

I would get an error

Msg 1205, Level 13, State 68, Server XXXXXXXXXXX, Line 1
Transaction (Process ID XX) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Server XXXXXXXXXXX, Line 1
ALTER DATABASE statement failed.
I ran sp_whoisactive with @show_sleeping_spids = 2 but couldn’t see which SPID was connected to the database. Then researched on the internet and found that I can trying to use sp_who2 and find the spids. There were too many results to go through. I found this neat script

use master
GO

select 
    d.name, 
    d.dbid, 
    spid, 
    login_time, 
    nt_domain, 
    nt_username, 
    loginame
from sysprocesses p 
    inner join sysdatabases d 
        on p.dbid = d.dbid
where d.name = '[DB_Name]'
GO

This scripts picks up all the sessions connected to the database. I tried to kill one of the process and got

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

So no solution till I ran across this article by Pearl Knows on SQL Server Central http://goo.gl/z7EXUT. Great article.

I was able to use the article to get out of this jam.

I used High Deadlock Priority to get connection.

USE [master]
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [DB_Name] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [DB_Name] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Hopefully this will help you get out of a jam.

Thanks!

SSAS Port setup and fixing issues

 

Recently installed SSAS 2008 R2 on a server. I couldn’t connect to it from SSMS via a separate computer. Tried to figure but couldn’t resolve it. Then I realized that I haven’t set the port and maybe that might be the issues.

I launched SSMS on the box where SSAS is installed.

• Connected to it
• Right click and select properties
• Check Show Advanced (All) Properties in the bottom
• Scroll down to PORT and change the Value.
• Click OK

 

 

 

But in IT nothing is that simple. I was supposed to enter 2393 but ended up entering 2382. Don’t ask why it happened but it happened. So right after you change the port, it needs to be restarted

I restarted the service and it would not come online. I looked in the event log and found this error

-
System-

Provider

[ Name]
MSOLAP$P1

-
EventID
0

[ Qualifiers]
49441

Level
2

Task
289

Keywords
0x80000000000000

-
TimeCreated

[ SystemTime]
2014-02-13T19:25:41.000000000Z

EventRecordID
41775

Channel
Application

Security

-
EventData

The following system error occurred: Only one usage of each socket address (protocol/network address/port) is normally permitted. The following system error occurred: Only one usage of each socket address (protocol/network address/port) is normally permitted.

 

 

So basically 2382 is port where SQL listens for connection request so that is why the service was not starting. I looked online and after much research, I found that file “msmdsrv.ini” file. If open the file, change the value to 0 (dynamic) or the port you want to assign which in this case was 2393. Once I changed the file saved it, the server restarted.

 

I hope this is helpful