Friday, March 9, 2012

Error 5123 Cannot Attach DB

I'm getting Error 5123 "Cannot Open file because file is being used by
another process" when I try re-attaching a DB (.mdf) I had just detached.
I'm afraid I issued a DBCC command (which may be locking the file) right
before I detached the Database: DBCC OPENTRAN (oldoaiapp)
Does anyone know how I can identify and kill whatever process is attached to
my .mdf database? I obviously cannot use Query Analyzer as I cannot see the
database anymore.Hi,
Probably your MDF or LDF file will be used by either Backup process or Anti
virus software. Verify both first and attach again.
If you still have issues then try to copy the files to a new folder and use
SP_ATTACH_DB to attach the database.
Incase if you still have issues then restart sql server service.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:B94C2796-66C4-4641-B3C5-5B7F597CC7B2@.microsoft.com...
> I'm getting Error 5123 "Cannot Open file because file is being used by
> another process" when I try re-attaching a DB (.mdf) I had just detached.
> I'm afraid I issued a DBCC command (which may be locking the file) right
> before I detached the Database: DBCC OPENTRAN (oldoaiapp)
> Does anyone know how I can identify and kill whatever process is attached
> to
> my .mdf database? I obviously cannot use Query Analyzer as I cannot see
> the
> database anymore.
>|||Hari,
I cannot copy the files as I get the same lock error. I have determined it
is indeed SQL Server which is locking the files. I have rebooted the SQL
Server as well, and that does not clear the file locks. I'm afraid the DBCC
OpenTran (oldoaiapp) is what caused the lock.
I tried the Query Analyzer command below and am getting the error below (the
filename path IS correct):
EXEC sp_attach_db @.dbname = 'OldOAIApp',
@.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
@.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
Error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
"Hari Prasad" wrote:

> Hi,
> Probably your MDF or LDF file will be used by either Backup process or Ant
i
> virus software. Verify both first and attach again.
> If you still have issues then try to copy the files to a new folder and u
se
> SP_ATTACH_DB to attach the database.
> Incase if you still have issues then restart sql server service.
> Thanks
> Hari
> SQL Server MVP|||Hi,
Can you stop MSSQL Server service and copy both files into a new folder.
Then rename the copied files to a different name
and try to attach the database.
Also verify the file names and directory you mention is correct.
Thanks
Hari
SQL Server MVP
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
> Hari,
> I cannot copy the files as I get the same lock error. I have determined
> it
> is indeed SQL Server which is locking the files. I have rebooted the SQL
> Server as well, and that does not clear the file locks. I'm afraid the
> DBCC
> OpenTran (oldoaiapp) is what caused the lock.
> I tried the Query Analyzer command below and am getting the error below
> (the
> filename path IS correct):
> EXEC sp_attach_db @.dbname = 'OldOAIApp',
> @.filename1 = N'C:\SQLData\MSSQL\Data\POAIAPP.mdf',
> @.filename2 = N'C:\SQLData\MSSQL\Data\POAIAPP_log.ldf'
> Error:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'C:\SQLData\MSSQL\Data\POAIAPP.mdf' may be incorrect.
> "Hari Prasad" wrote:
>
>|||Thanks Hari,
I will try the stopping of SQL Server and do the file move/renames after
hours. I think this will work and hopefully SQL Server won't choke on
restart!
Cheers,
Leslie
"Hari Prasad" wrote:

> Hi,
> Can you stop MSSQL Server service and copy both files into a new folder.
> Then rename the copied files to a different name
> and try to attach the database.
> Also verify the file names and directory you mention is correct.
> Thanks
> Hari
> SQL Server MVP
> "Leslie" <Leslie@.discussions.microsoft.com> wrote in message
> news:A9250A47-F159-4DD0-B7B3-B3AE24638468@.microsoft.com...
>
>

No comments:

Post a Comment