0

I got an issue. I have a database (Say, DBASE) which have three files - Two data files (namely D:\Data1.mdf and D:\Data2.ndf) and a log file (E:\log.ldf) and all are in different file group. The secondary data file i.e., Data2.ndf is in recovery pending state. Remaining two db files are good and my database is online. When I took a look on database properties (GUI), I found there the size of Data2.ndf as 0MB where it's actual size is 10GB. We have given a try in below methods to fix the issue, but no luck.

  1. resetdbstatus
  2. Taking database to offline, then back to online.
  3. Detach and attach operations
  4. No errors from checkdb, unfortunately we don't anything in error log (as they are recycled)

Can someone help me in fixing the issue please?

FYI - Version : SQL 2012 SP1 Enterprise.

Improve this question
2
  • Have you tried backing it up, dropping it, and restoring it (i.e. recreating it from backup)? This will take a lot longer than detach/reattach, so if it's a production database you could maybe try this on a dev or test server. It sounds to me like this is a metadata glitch, as your database itself is online and read-writeable. Maybe a good issue for raising a support ticket with Microsoft if you have $$ ?
    – Thomas Pullen
    Jun 3, 2016 at 5:27
  • Thomas, We got this issue in production - sorry for not mentioning before. I didn't tried recreating the database from backup, thinking that it's not a good idea to do this on production as database size is huge and more importantly business is not at all impacted due to this issue. As per your advice, we'll try to do some testing on lower environment - Thanks for that. Just a thought - Is this something related to file group settings?
    – Sunil Kumar
    Jun 3, 2016 at 5:42

1 Answer 1

Reset to default
3

Since you mentioned earlier that your DB gets restored daily and it has different filegroups, it seems to me the case of piecemeal restore. You need to check the script used for restore and make sure it is recovering all the filegroups present in the DB backup.

It looks like currently the script is only recovering primary filegroup and skipping the others which is causing the DB to recover but without the skipped filegroups. So you can query all your data which is present in PRIMARY filegroup but not the skipped ones. Just recover all the filegroups using the example given in below link and you should be fine.

http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/sql-server-filegroup-piecemeal-restores-1/

Improve this answer
1
  • 1
    Hi ProdDBA, Thank you so much. It really helped me in fixing the issue. Thank you once again.
    – Sunil Kumar
    Jun 6, 2016 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.