Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tempdb issues Expand / Collapse
Author
Message
Posted Tuesday, December 20, 2011 7:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 4:17 PM
Points: 1,319, Visits: 1,718
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
Post #1224426
Posted Tuesday, December 20, 2011 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:24 AM
Points: 193, Visits: 1,109
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
Post #1224436
Posted Tuesday, December 20, 2011 7:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 4:17 PM
Points: 1,319, Visits: 1,718
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
tempdev C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
templog C:\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
Post #1224440
Posted Tuesday, December 20, 2011 9:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 4:17 PM
Points: 1,319, Visits: 1,718
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
Post #1224592
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse