June 12, 2015 at 5:45 am
Hi - I have a DB shown as recovery pending when running the following:
SELECT Name, state_desc
FROM sys.databases;
The DB was created by someone outside of our team using the Full Recovery model & I can see that no transaction log backups have been taken for this, causing the log to growth to a large size.
The MDF is only 5,120kb but the TRN has grown to 10,773,120kb
When I checked the Server I could see the data area had run out of space so I have freed up some space for this so now have 2.5gb available as a short term solution.
The MDF & LDF files are still visible & when checking the SQL log the DB is being reported as having a Full Transaction Log.
Essentially I want to change the Recovery Model from Full to Simple, Reduce the size of the transaction log & bring the DB back online. Luckily this DB is only used by a handful of users but I still need to get it up & running asap.
I would really appreciate any help on this asap. Many thanks in advance.
June 12, 2015 at 5:54 am
If the database is in the recovery pending state then something has happened that is preventing the recovery process from starting.
Has the server been restarted?
June 12, 2015 at 6:04 am
Hi - The DB is on a Multi SQL Estate so restarting would affect other DB's so haven't done this as yet. Is there another alternative to try?
Many thanks
Dax
June 12, 2015 at 6:19 am
I wasn't suggesting that you restart the server, I was just wondering if it had been to send the database into the recovery pending mode.
Have you tried copying the MDF file to another server and attaching it using sp_attach_single_file_db ?
June 12, 2015 at 6:22 am
Open up the error log, find and post all messages relating to this database. Do nothing else at this point.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2015 at 6:26 am
Hi - Thanks for replying so quickly. I have tried copying the MDF but a message is retuned to say the file is in use.
Regards
Dax
June 12, 2015 at 6:28 am
OK, so as Gail said go through the error log and post all messages that you can find relating to that db.
We need to find out why the database has gone into recovery pending.
June 12, 2015 at 6:36 am
2015-06-12 05:36:39.59 spid293 Error: 17053, Severity: 16, State: 1.
2015-06-12 05:36:39.59 spid293 D:\Database\Log\gems_ews\gems_ews_log.ldf: Operating system error 112(failed to retrieve text for this error. Reason: 15105) encountered.
2015-06-12 06:01:07.65 spid218 Error: 9002, Severity: 17, State: 2.
2015-06-12 06:01:07.65 spid218 The transaction log for database 'gems_ews' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Many thanks
Dax
June 12, 2015 at 6:37 am
Flailing around and trying stuff at random's a great way to make matters worse and possibly even destroy the database. The first step in any corruption or similar scenario MUST be to identify the exact cause of the problem.
Even if you had managed to copy the mdf, it most likely would not have attached because the log's not there and the log can't always be cleanly rebuilt.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2015 at 6:40 am
dax.latchford (6/12/2015)
2015-06-12 05:36:39.59 spid293 Error: 17053, Severity: 16, State: 1.2015-06-12 05:36:39.59 spid293 D:\Database\Log\gems_ews\gems_ews_log.ldf: Operating system error 112(failed to retrieve text for this error. Reason: 15105) encountered.
2015-06-12 06:01:07.65 spid218 Error: 9002, Severity: 17, State: 2.
2015-06-12 06:01:07.65 spid218 The transaction log for database 'gems_ews' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Error 112 is 'file in use by another process'. Is your antivirus properly configured to skip all database files?
Are there *any* other messages before or after those two that in any way mention the Gems database or have high severity errors in them? There should be more.
If you query sys.databases, what is the exact value for status_desc? What is log_reuse_wait_desc?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2015 at 6:44 am
I thought that 112 was not enough space on disk?
June 12, 2015 at 6:47 am
DBA From The Cold (6/12/2015)
I thought that 112 was not enough space on disk?
Ack, you're right. Mixed it up with OS error 32.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2015 at 6:49 am
Please let me know if this is a bad idea but what I'd do is....
Make sure there is space available on the disk.
Take the database offline
Bring the database back online
If there is space available on the disk it should allow for the recovery process to start
June 12, 2015 at 7:08 am
Hi - Thanks so much for all your help with this. I have run the following SQL:
Query1
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'gems_ews';
Returned value: 1 LOG_BACKUP
Query2
SELECT Name, state_desc
FROM sys.databases;
Returned value: Name: gems_ews state_desc: RECOVERY_PENDING
I have also created a new database at an alternative location using the most recent backup from yesterday morning with successful results. I can access the restored DB & can access all tables/columns.
June 12, 2015 at 7:17 am
Anything else in the error log? If you're not sure, post the entire contents from the time the DB ran out of space until now.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply