Unanswered: Need help - Error: 823, Severity: 24, State: 11
I got the following errors........
Error: 823, Severity: 24, State: 11
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset 0x000002983a0000 in file 'e:\MSSQL\data\tempdb.mdf'..
Error: 15457, Severity: 0, State: 1
Seems like a bad disk. It is a Raid 0 drive.
I have asked a drive check to be performed for this drive. I am not sure if it can be done while the SQL Server is running.
I am thinking of shrinking the tempDB and let it grow again so that it may not expand on the bad sectors.
I could also move the tempDB to another drive temporarily, but would require me to reboot our production server.
Also, my concern is that if the drive is bad, it will affect the other system databases as they are sitting on the same drive. The sytem databases have passed integrity checks over the weekend and seem to be okay.
Anyone has any ideas on what steps I should take or the best way to resolve this problem.
Not too sure, but you could check to see that virus scan and any backup software remembers to ignore *.MDF, *.LDF, and *.NDF files. Also you may want to check on tempdb to make sure that auto-close is off (though, I am not sure you can set it to auto-close). Good luck, and let us know if you find the problem.
Sorry to get back late here, we had some other problems I was working on. Well, our hardware people said that there was nothing wrong with the drive in question. So, it had to be the tempdb problem.
I ran the DBCC CheckDB with All_ErrorMsgs and it returned me no errors. There were no non-system objects in the tempdb and the space used in the 35GB tempdb database was only 4MB. Looking at the time it required to run and the results, it seems to me that the the DBCC command checks only on the space that has been allocated to the tables, etc. So, I ran the DBCC CheckDB with Physical_Only with the hopes that it will scan all the pages even if they are empty, but it came with the same results. No errors.
So, I restarted the server in minimum configuration mode (at the dos prompt with "SQLServr.Exe -c -f" and then using Query Analyzer, I altered the database files (mdf and log) size of the tempdb using the 'alter database' command. And restarted the SQLServer.
If this had not worked, I was going to try to use "DBCC ShrinkFile" and shrink the tempdb database files.