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%'
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.