January 30, 2016 at 11:02 pm
Hi All,
SQL Server services are not starting up and giving the below error
2016-01-30 18:35:36.20 spid9s Error: 17207, Severity: 16, State: 1.
2016-01-30 18:35:36.20 spid9s FCB::Open: Operating system error 32(error not found) occurred while creating or opening file 'G:\Data\templog.ldf'. Diagnose and correct the operating system error, and retry the operation.
2016-01-30 18:35:36.20 spid9s Error: 17204, Severity: 16, State: 1.
2016-01-30 18:35:36.20 spid9s FCB::Open failed: Could not open file G:\Data\templog.ldf for file number 2. OS error: 32(error not found).
2016-01-30 18:35:36.20 spid9s Error: 5120, Severity: 16, State: 101.
2016-01-30 18:35:36.20 spid9s Unable to open the physical file "G:\Data\templog.ldf". Operating system error 32: "32(error not found)".
2016-01-30 18:35:36.21 spid9s Error: 1802, Severity: 16, State: 4.
2016-01-30 18:35:36.21 spid9s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2016-01-30 18:35:36.21 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2016-01-30 18:35:36.21 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
January 31, 2016 at 3:30 am
When the SQL Server service (re)starts, it will always drop and recreate the tempdb database. Based on the error messages, you did something that made this impossible.
The error messages indicate that tempdb (or at least one of its files - tempdog.ldf) wants to be allocated on the G: drive. This fails with an error message that suggests that there iss not enough disk space. There are numerous scenarioss imaginable that may have caused this, but I guess you actually want a solution right now.
Here is a step by step guide: https://www.xtivia.com/start-sql-server-lost-tempdb-data-files/[/url].
After following that guide, once your instance is back up and running, try to figure out the actual required size of tempdb for your instance, and determine on which drives they should go; ensure that those drives will always have enough room for both the regular size and unexpected growth. Create multiple files for the data (all with equal size and autogrow paramteres) and a single file for the log, all big enough that they normally never need to grow; choose an autogrow setting that finds the middle ground between numerous tiny allocations or one single way-too-big allocation if growth is ever needed. Set trace flags 1117 and 1118 flags to optimize allocation and growth (see https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/[/url]). Then restart the instance once more to make all these changes take effect.
And then ensure that nobody else ever touches your drives anymore.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply