June 12, 2015 at 7:27 am
Will do, in a meeting now until around 16:00 so will be back. Thank you both for all your help.
June 12, 2015 at 9:41 am
Add another log file to the db. Put it on a drive with plenty of space. Allow SQL time to get caught up on log writes, and the db should come back online itself. At the first convenient time in the future, you can shrink the log (if you need to or just want to) and remove the extra log file. Don't take the db offline, as you may not be able to get it back online with an incomplete log.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2015 at 9:49 am
2015-06-11 23:45:47.74 spid120 Error: 18204, Severity: 16, State: 1.
2015-06-11 23:45:47.74 spid120 BackupDiskFile::CreateMedia: Backup device 'E:\\gems_ews\gems_ews_backup_2015_06_11_234515_4578504.bak' failed to create. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
2015-06-11 23:45:47.74 Backup Error: 3041, Severity: 16, State: 1.
2015-06-11 23:45:47.74 Backup BACKUP failed to complete the command BACKUP DATABASE gems_ews. Check the backup application log for detailed messages.
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 05:36:39.59 spid293 Error: 9002, Severity: 17, State: 2.
2015-06-12 05:36:39.59 spid293 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
Loads of entries then showing the following:
2015-06-12 05:36:39.59 spid293 Error: 9002, Severity: 17, State: 2.
2015-06-12 05:36:39.59 spid293 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
2015-06-12 08:13:18.93 spid294 Error: 17053, Severity: 16, State: 1.
2015-06-12 08:13:18.93 spid294 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 08:13:18.93 spid294 Error: 9002, Severity: 17, State: 2.
2015-06-12 08:13:18.93 spid294 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
2015-06-12 08:13:18.94 spid294 Error: 9002, Severity: 17, State: 2.
2015-06-12 08:13:18.94 spid294 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
2015-06-12 08:13:19.00 spid294 Error: 3314, Severity: 21, State: 4.
2015-06-12 08:13:19.00 spid294 During undoing of a logged operation in database 'gems_ews', an error occurred at log record ID (561:206:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2015-06-12 08:13:19.05 spid294 Database gems_ews was shutdown due to error 9002 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
2015-06-12 08:13:19.05 spid294 Error: 9001, Severity: 21, State: 5.
2015-06-12 08:13:19.05 spid294 The log for database 'gems_ews' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2015-06-12 08:13:19.07 spid294 Error: 3314, Severity: 21, State: 4.
2015-06-12 08:13:19.07 spid294 During undoing of a logged operation in database 'gems_ews', an error occurred at log record ID (562:536:7). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2015-06-12 08:13:19.07 spid294 Error: 9001, Severity: 21, State: 1.
2015-06-12 08:13:19.07 spid294 The log for database 'gems_ews' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2015-06-12 08:13:19.08 spid294 Error: 3314, Severity: 21, State: 5.
2015-06-12 08:13:19.08 spid294 During undoing of a logged operation in database 'gems_ews', an error occurred at log record ID (559:497:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2015-06-12 08:13:19.30 spid19s Starting up database 'gems_ews'.
2015-06-12 08:13:25.80 spid19s 27 transactions rolled forward in database 'gems_ews' (46). This is an informational message only. No user action is required.
2015-06-12 08:13:25.85 spid19s Error: 17053, Severity: 16, State: 1.
2015-06-12 08:13:25.85 spid19s 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 08:13:25.85 spid19s Error: 9002, Severity: 17, State: 2.
2015-06-12 08:13:25.85 spid19s 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
2015-06-12 08:13:25.86 spid19s Error: 3314, Severity: 21, State: 1.
2015-06-12 08:13:25.86 spid19s During undoing of a logged operation in database 'gems_ews', an error occurred at log record ID (561:206:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2015-06-12 08:13:38.52 Logon Error: 18456, Severity: 14, State: 38.
2015-06-12 08:13:38.52 Logon Login failed for user 'WEST-NORFOLK\gemsservice'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.10.51.3]
Many thanks
Dax
June 12, 2015 at 9:54 am
Again, add another log file to the db, and do it quickly. That should allow SQL to complete pending transactions. See my last post for additional details.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2015 at 9:57 am
ScottPletcher (6/12/2015)
Again, add another log file to the db, and do it quickly.
Can't add files to a DB that's RECOVERY_PENDING, it's not online, it can't be opened.
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 10:00 am
This is actually pretty nasty. You've hit a bug where the log reservation wasn't enough for a rollback. That's not supposed to happen.
What backups do you have and what is the data loss allowance for this database?
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 10:17 am
Hi - It's my understanding that this DB isn't live yet so is in Development. I can restore back to yesterday morning at 08:00 which I'm sure will be sufficient.
As I've said previously I have managed to create the DB successfully on my client & can access the data. Obviously when the DB is restored it needs over 10gb currently to allow for the Log file which I don't have on the Server where it resides.
what options do we have available?
June 12, 2015 at 10:31 am
Can you confirm that restoring to yesterday is acceptable?
If you can (and ONLY if that's completely acceptable), then try taking the DB offline and bringing it back online. It may work, it may not (which is why only if it's acceptable to restore to yesterday), I don't like those 'error in undo of a logged operation'
If that amount of data loss is not acceptable, I'll give you another possible option when I get power back (in about 4 hours)
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 10:35 am
Hi - Many thanks for that, I'm going to be travelling down to Cambridge soon so will e-mail the key user to see what is acceptable & will come back to you.
May not get an answer until Monday though. Thanks again for all your help, you've been a lifeline!
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply