December 20, 2011 at 7:18 am
Hi all,
I have scheduled a maintenance plan at 6:00AM today on my prod sql server. This is the first time on all the user databases that we are scheduling this activity. Even though the auto update stats is turned on. After running for 45 mins I ran a perfmon with some set of counters and after few minutes I noticed the following errors:
LogWriter: Operating system error 2(The system cannot find the file specified.) encountered.
Write error during log flush.
The operating system returned error 2(The system cannot find the file specified.) to SQL Server during a write at offset 0x000001d3250000 in file 'T:\Data\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The log for database 'tempdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
During undoing of a logged operation in database 'tempdb', an error occurred at log record ID (11280:976:25). 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.
SQL Server must shut down in order to recover a database (database ID 2). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
A read of the file 'T:\Data\tempdb.mdf' at offset 0x000001d9e80000 succeeded after failing 1 time(s) with error: 2(The system cannot find the file specified.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
This is our peak time right now and i havent heard of any app issues. Hence I am not running any checkdb for tempdb. ALso I am worried to restart the service because if the sQL server doesnt comeup thats a big issue.
Please help.
Thanks a bunch
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 20, 2011 at 7:47 am
Hi Sapen
You need to get in touch with whoever looks after the storage system for that server.
The errors are from SQL Server trying to access tempDBs data and log files and it cant find the disks. Either the disks became detached from the server or the IO system had an error when it tried to access a page from the disks.
Either way get your SAN or Server guys to look into it ASAP. Once they have diagnosed and fixed the issue a restart will re-create tempdb. I'd recommend doing a checkdb of all other database if/when you get the server started again to make sure no other issues have occured.
Mat
December 20, 2011 at 7:51 am
update: There was an auto restart this morning and I dint see any errors after that successful auto restart
But a couple of weeks back I had my SAN Admin carve out a LUN for 40GB from Raid 1 group and moved tempdb to that T:\ drive. The move went well and I restarted services after that. And right now I see the T:\ Drive at 8.0GB.
This morning there was a sql service auto restart because of contention/issues in tempdb and when I query
SELECT name, physical_name FROM sys.master_files WHERE database_id = 2
I see the below result and the space on c:\ drive went down by 8.0GB
name physical_name
tempdevC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
templogC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldf
Can some one please tell me why did that move back to c:\ drive?? and is there a way to fix this permanently.
Thanks a bunch
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 20, 2011 at 9:48 am
Thanks Mat...You were right
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply