I'm running a large, nightly batch job which processes thousands of records. It runs a complex query through a view, and pulls the results into my Java application in batches of 50 rows (using an order by and "top x" statement). The entire job runs on the same database connection, but each batch is updated in its own JDBC transaction.
At a random point during the job's execution (which varies between executions), I get the following SQL server error:
SQL Error: 21, SQLState: S1000
Warning: Fatal error 823 occurred at Apr 28 2010 6:15PM. Note the error and time, and contact your system administrator.
The message appearing in the windows Application event log which accompanies it is a little scary:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 823
Date: 4/28/2010
Time: 6:15:41 PM
User: N/A
Computer: (our server)
Description:
The operating system returned error 2(The system cannot find the file specified.) to SQL Server during a write at offset 0x000000285e0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\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.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Looking in the filesystem, I verified that a tempdb file exists with that name, at the location which is shown on "C:". There is 15.4GB of free space available on drive C.
Running DBCC CHECKDB on both 'tempdb' and my application database 'PATH7' turned up nothing:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AIMS_PATH7'.
However, it also tells me:
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.
Not sure why this is.
Help! What can I do about this error?
This job works in our production application, and it hasn't been changed in this release. It just started acting up after I created a new database instance to run it against.
