Who Detach database on SQL Server

Recently we are facing some complaint from a customer that one of their database gets detached automatically once a week and they have no idea why this is happening.

The other problem is that when they try to attach the SQL Servers says the file is already use even though the datafile is not attached to any database.

Technically speaking SQL database can be detached only when user has sysadmin rights and can only be detached or attached with manual intervention  i.e, either by script (Job,.Net Application or by Powershell etc.) or through SQL SSMS. When a database is

We can track this event from default trace as shown below.

DECLARE @path NVARCHAR(260)
 SELECT @path=path FROM sys.traces WHERE is_default = 1
 SELECT TE.name AS EventNamem, DT.TextData,DT.HostName,dt.StartTime,DT.ApplicationName,
 DT.LoginName
 FROM dbo.fn_trace_gettable (@path, DEFAULT) DT
 INNER JOIN sys.trace_events TE
 ON DT.EventClass = TE.trace_event_id
 where TextData LIKE 'DBCC DETACHDB%'

DetachDB

It has been seen in SQL Server when you detach a database SQL service account lost permission on file so I would suggest you take the database offline and then detach database, this will not reset permission on data\log files, else you have to remove un-necessary permission and have to add SQL  Service Account again.even because of this reason you will get error like file already in use even though it’s not in use. In that case remove un-necessary permission and add SQL Service Account Permission.

MDFPer

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s