1

Topic: Problem with basis recovery

Hello.
The version of the server of Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 1:38:57 PM
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
After reboot of the server the basis left in a mode (recovery Waiting). Tried to use script often meeting in search:
ALTER DATABASE scout_storage SET EMERGENCY;
GO
ALTER DATABASE scout_storage SET SINGLE_USER;
GO
DBCC CHECKDB (scout_storage)
GO
ALTER DATABASE scout_storage SET MULTI_USER;
GO
ALTER DATABASE scout_storage SET ONLINE;
In the message there are errors:
The message 5120, level 16, state 101, line 1
It was not possible to open a physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.KGKRSQL\MSSQL\DATA\scout_storage.mdf". An error of an operating system 32: "32 (Process cannot receive file access as this file is occupied by other process.)".
The message 5120, level 16, state 101, line 2
It was not possible to open a physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.KGKRSQL\MSSQL\DATA\scout_storage.mdf". An error of an operating system 32: "32 (Process cannot receive file access as this file is occupied by other process.)".
The message 945, level 14, state 2, line 2
It was not possible to open a database "scout_storage" owing to inaccessibility of files, shortage of storage or a place on a disk. Particulars see in the SQL Server error log.
The message 5120, level 16, state 101, line 2
It was not possible to open a physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.KGKRSQL\MSSQL\DATA\scout_storage.mdf". An error of an operating system 32: "32 (Process cannot receive file access as this file is occupied by other process.)".
The message 5120, level 16, state 101, line 2
It was not possible to open a physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.KGKRSQL\MSSQL\DATA\scout_storage.mdf". An error of an operating system 32: "32 (Process cannot receive file access as this file is occupied by other process.)".
The message 5120, level 16, state 101, line 2
It was not possible to open a physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.KGKRSQL\MSSQL\DATA\scout_storage_log.ldf". An error of an operating system 32: "32 (Process cannot receive file access as this file is occupied by other process.)".
The message 5181, level 16, state 5, line 2
It is impossible to restart repeatedly a database "scout_storage". Resetting to the previous state.
The message 5069, level 16, state 1, line 2
It was not possible to carry out instruction ALTER DATABASE.
The method from article http://blog.vb76.com/vosstanovlenie-baz … -_log-ldf/ tried
But unfortunately it too did not give a positive effect. I can not understand as to calculate process which hinders to fulfill basis recovery.
Apparently the problem arose because of an evolved file of dens (here https://habrahabr.ru/sandbox/40161 / that describes that similar) - scout_storage_log.ldf grew to 4,01 Gb.
The method from a sandbox  as does not work. A file access error scout_storage.mdf.

2

Re: Problem with basis recovery

neorus ;
process explorer-> ctrl+f

3

Re: Problem with basis recovery

Unfortunately I tried this variant. Except the DB at present nobody communicates with a file.
[img=https://i.imgur.com/X7D38nO.png]

4

Re: Problem with basis recovery

What is written in ?
Whether the basis in an offline is translated?

5

Re: Problem with basis recovery

In an offline the mode translated basis. Truth now it is reverse not  - produces an error
[img=https://i.imgur.com/KokCpfx.png]
Put a broad gull a file

6

Re: Problem with basis recovery

neorus wrote:

In an offline the mode translated basis. Truth now it is reverse not  - produces an error
[img=https://i.imgur.com/KokCpfx.png]
Put a broad gull a file

Show

select * from master.sys.master_files as mf

7

Re: Problem with basis recovery

While basis in an offline, try to copy files in other place.
Then create other basis through create database for attach
Now it is possible old , and new to rename into the old

8

Re: Problem with basis recovery

In  a certain basis scout_storage_bad figures still.
Whether it files is based on the same?
And that  it also holds these files

9

Re: Problem with basis recovery

You mean to fulfill such request:
USE master;
GO
CREATE DATABASE scout_storage +
ON PRIMARY (NAME = scout_storage +;
(NAME = ' C:\Program Files\Microsoft SQL Server\MSSQL11.KGKRSQL\MSSQL\DATAscout_storage.mdf);
GO
?

10

Re: Problem with basis recovery

Yasha123 wrote:

in  a certain basis scout_storage_bad figures still.
Whether it files is based on the same?
And that  it also holds these files

Basis scout_storage_bad my attempt to solve a problem last time. As errors about employment as a result were strewed renamed this basis. Created new with the same name and tore  the fresh. A trouble in that that that time I replaced the password sa, and here to make it in a script  forgot. Accordingly 2 weeks I lived without , therefore and I try to revive current basis scout_storage.

11

Re: Problem with basis recovery

neorus, once again, result results of a script

select * from master.sys.master_files as mf

Can you that basis with the same names of files recovered, and now try to use the second time the same files.

12

Re: Problem with basis recovery

Minamoto;
[img=https://i.imgur.com/rgDe6uf.png]
[img=https://i.imgur.com/MxF8cAP.png]
[img=https://i.imgur.com/PpR2UtG.png]
After the next reboot and resetting of files into place - the basis passed in the status the suspicious

13

Re: Problem with basis recovery

Minamoto;
You are probably right, basis renaming in scout_storage_bad was led through equipment Ms by a SQL server management studio. Basis files were renamed in scout_storage_bad.mdf and scout_storage_bad_log.ldf
Anyway these files are in directory DATA.
By the way the recovery method which before in any did not work on scout_storage_bad now fulfilled normally and basis in the normal status, alas there there is no data for 2 weeks and it would be desirable to repair the leaking.

14

Re: Problem with basis recovery

neorus, , just you also did not show the necessary data - on a screenshot the full path is cut off.
Copy, interpose simply the table here.

15

Re: Problem with basis recovery

Minamoto;
I will not understand how to pull out in the form of a file result and here to put
[img=https://i.imgur.com/9e87pSA.png]
Wrote to you to mail specified in a profile.

16

Re: Problem with basis recovery

Well so once again the question is readable:

Yasha123 wrote:

in  a certain basis scout_storage_bad figures still.
Whether it files is based on the same?
And that  it also holds these files

Your basis scout_storage_bad physically also is those mdf, ldf from initial basis.
You 2 bases managed  to the same physical files.
Certainly, as soon as the basis scout_storage_bad rises;
The server and  access to these files

17

Re: Problem with basis recovery

Yasha123;
And whence there was a communication?
I renamed basis. The file name of basis and a file of dens changed.
Then created new and tore in it  from bak a file (type the full backup copy). Whence communication undertook?

18

Re: Problem with basis recovery

neorus wrote:

Yasha123;
And whence there was a communication?
I renamed basis. The file name of basis and a file of dens changed.
Then created new and tore in it  from bak a file (type the full backup copy). Whence communication undertook?

Probably, when  tore;
Did not specify WITH MOVE.
It was as a result tore by default;
I.e. on that way where was.
It turned out that already and the new basis looks at old files.
Only the such could happen only in that case;
When the old basis in an offline was.
Otherwise it would not turn out to re-record its files with impunity

19

Re: Problem with basis recovery

Yasha123;
Would understand like what a trouble. But even if 2 bases look at one files. But application conducted record only in basis scout_storage. The second basis simply lay in a mode recovery waiting. It could affect on  the created?

20

Re: Problem with basis recovery

Yasha123 wrote:

it is passed...
Probably, when  tore;
Did not specify WITH MOVE.

I fairly tried. . Special dances with tambourines are necessary what that.
Here that turns out if to try to be recovered in offline-base files:

 Msg 1834, Level 16, State 1, Line 2
The file ' D:\database\Data\test_offline.mdf ' cannot be overwritten. It is being used by database ' test_offline '.
Msg 3156, Level 16, State 4, Line 2
File ' test_offline ' cannot be restored to ' D:\database\Data\test_offline.mdf '. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 2
The file ' D:\database\Data\test_offline_log.ldf ' cannot be overwritten. It is being used by database ' test_offline '.
Msg 3156, Level 16, State 4, Line 2
File ' test_offline_log ' cannot be restored to ' D:\database\Data\test_offline_log.ldf '. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally. 

It, actually, does not change a situation, to me it is simply curious that was specific it is necessary to make to receive two bases looking at one files.

21

Re: Problem with basis recovery

neorus wrote:

Yasha123;
Would understand like what a trouble. But even if 2 bases look at one files. But application conducted record only in basis scout_storage. The second basis simply lay in a mode recovery waiting. It could affect on  the created?

You tangled me.
What basis lay, and what worked?
Let's admit, the basis scout_storage_bad lay;
And scout_storage all was written to basis.
Then the problem at you began, when someone rebooted the server;
And instead of scout_storage in online of the first quitted scout_storage_bad.
Then now send in an offline scout_storage_bad;
After that scout_storage quits in online.

22

Re: Problem with basis recovery

Deduced basis scout_storage_bad in offline
Now the status scout_storage became suspicious.
What script it now to repair better?

23

Re: Problem with basis recovery

Minamoto wrote:

it is passed...
I fairly tried. . Special dances with tambourines are necessary what that.
Here that turns out if to try to be recovered in offline-base files:

 Msg 1834, Level 16, State 1, Line 2
The file ' D:\database\Data\test_offline.mdf ' cannot be overwritten. It is being used by database ' test_offline '.
Msg 3156, Level 16, State 4, Line 2
File ' test_offline ' cannot be restored to ' D:\database\Data\test_offline.mdf '. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 2
The file ' D:\database\Data\test_offline_log.ldf ' cannot be overwritten. It is being used by database ' test_offline '.
Msg 3156, Level 16, State 4, Line 2
File ' test_offline_log ' cannot be restored to ' D:\database\Data\test_offline_log.ldf '. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally. 

It, actually, does not change a situation, to me it is simply curious that was specific it is necessary to make to receive two bases looking at one files.

Well I now will try.
Though I remember a situation, when as experiment
I could recover the master of one server (2008) on other (2012).
Both servers local, on one  lived.
And here a copy of the master 2008 , the server (2012) rose;
But bases at it already were not the, and from 2008.
And here what for it was necessary for me 2008.
And so at it any  the basis in online did not quit;
And all bases here this error wrote that already files are occupied

24

Re: Problem with basis recovery

Yasha123 wrote:

it is passed...
Well I now will try.
Though I remember a situation, when as experiment
I could recover the master of one server (2008) on other (2012).
Both servers local, on one  lived.
And here a copy of the master 2008 , the server (2012) rose;
But bases at it already were not the, and from 2008.
And here what for it was necessary for me 2008.
And so at it any  the basis in online did not quit;
And all bases here this error wrote that already files are occupied

It turned out through attach.
Here I see now this magic error:

 Set offline failed for Database ' test_offline '.
Unable to open the physical file "D:\database\Data\test_offline.mdf". Operating system error 32:
"32 (Process cannot receive file access as this file is occupied by other process.)".
Unable to open the physical file "D:\database\Data\test_offline_log.ldf". Operating system error 32:
"32 (Process cannot receive file access as this file is occupied by other process.)".
Could not restart database "test_offline". Reverting to the previous status.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5120) 

And the basis thus passed in Recovery pending.
If the second basis to deduce in offline, the first is successfully deduced in online that is logical.

25

Re: Problem with basis recovery

Minamoto;
Well here actually also it was convinced as a teapot hand can create a situation which then cannot repeat .
Once again I will describe a situation from the very beginning.
12.02
Application ceased to work. When manager SQL launched. The basis scout_storage had a status suspicious. Those support accompanying a sentence advised to fulfill a script
EXEC sp_resetstatus [scout_storage];
ALTER DATABASE [scout_storage] SET EMERGENCY
DBCC checkdb ([scout_storage])
ALTER DATABASE [scout_storage] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([scout_storage], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [scout_storage] SET MULTI_USER
But there was an error - the file is occupied. As I suppose it was necessary before to launch a script - to deduce it in an offline a mode. I only disconnected application which wrote to this basis.
As a result I decided to rename basis in scout_storage-bad is to me it was possible to make after the next reboot of the server. The status of basis and remained waits for recovery.
Then I created in manual without a script new basis scout_storage and tore there last  from bak a file (a mode of a copy full).
And all worked before reboot after 2 weeks.
I can not understand why at renaming the basis name in the manager was replaced, files in a folder too were renamed, and here links remained on former scout_storage. Probably because of that that renamed too hands through the manager.
I.e. at me now here such situation:
[img=https://i.imgur.com/7ZTVfOc.png]
[img=https://i.imgur.com/t56kvB0.png]
How it is possible to return basis in working state?