-2

I am trying to delete a datapase in azure portal. After removing from SQL pool, I am getting the following error:

Msg 37106, Level 16, State 1, Line 7 The database '{databasename}' on server {'servername}' is in use by job account 'jobagent'. The database cannot be deleted or renamed while associated with a job account.

How should I address fixing this?

Yaakov Ellis
  • 40,752
  • 27
  • 129
  • 174
user3247561
  • 1
  • 1

1 Answers1

1

If you don't have other databases on it, you could simply drop the server and recreate it. That will get you around the issue.

Else Follow below steps delete database

You can kill a process by a right click on the process in the grid and selecting the Kill Process menu item. You will be asked for a confirmation to kill the related process and then will kill the open connection to the database over this process. This action is just like running to kill sql process t-sql command for a single process.

Way to drop all active connections of a database can be implemented by generating dynamic sql commands that runs a list of "Kill @spId" commands.

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'Works'
--SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

-- SELECT @SQL
EXEC(@SQL)

A very similar to the sql code above, an other code block can be used by using the COALESCE as shown below

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'Works'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--SELECT @SQL
EXEC(@SQL)

You can also refer this article

Abhishek K
  • 3,047
  • 1
  • 6
  • 19