question

Wilfred van Dijk avatar image
Wilfred van Dijk asked

datafile missing - which one?

I created a test database with 2 datafiles. I stopped the instance, dropped a datafile (called 'foetsie' which is a dutch funny name for 'gone') and started the instance again. The database is now in 'RECOVERY PENDING'. A look at master.sys.master files confirms the state (first part of query result). So I am curious if MSSQL can tell me which file is missing. I looked at master.sys.master_files, but here all the datafiles are ONLINE??? (second part of query result) So how do I know which file is missing? Testing with MSSQL2014![alt text][1] [1]: /storage/temp/2140-capture.png
recoverydatafiles
capture.png (10.0 KiB)
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You looked at the log? I think it's there as part of the startup description, but I sure could be wrong.
1 Like 1 ·
This check is part of our MSSQL Healthcheck which my company provides. While I was analyzing this check, I noticed this strange behaviour. So yes, you could check the logfile but I was expecting a correct state in the sys.master_files just like the state in master.sys.databases which is updated at a MSSQL start.
0 Likes 0 ·
Yes, there's a message in the log, but why is master_files not updated?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Grant is correct, the log file content as the service starts up shows which file is missing for the database. I'm guessing that the values in master_files are those 'last known' before the service shut down and as such are out of date.![alt text][1] [1]: /storage/temp/2141-missingdatafile.png

missingdatafile.png (15.5 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
As a result, the policy "Data File\@IsOffline" is also useless ...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
Yesterday, I did a piecemeal restore and in that case the status of the missing datafiles is correct (RECOVERY_PENDING)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.