Problems with TEMPDB

[CTekGirl]

Production Database Size 7897.37gb
Test Database Size 7269.12gb

TEMPDB Size 6668.68gb

I have run DBCC CHECKDB and SHOWCONTIG on production database. (tuning and checking for errors) I have rebuilt the indexes of 3 of the largest tables(scripted).

Would this create a problem with tempdb?

I would like to shrink the size of tempdb. In the properties it states that the space used is only 2mb. However the size on the disk is still over 6gb. Is there anyway to shrink it?

Are any of these issues related to the following error.
A user attempted to run a process in an application that updates some journal tables. This is error that come up:

08601 [Microsoft][ODBC SQL Server Driver][SQL Server]I/O error 2(The system cannot find the file specified.) detected during write at offset 0x000000f7830000 in file 'd:\Microsoft SQL Server\MSSQL\data\tempdb.mdf'.
08200 Cannot connect to requested database
JOB WAS CANCELLED DUE TO A SEVERE ERROR

I confirmed through the properties the location of the mdf file on the server.


Any assistance would be appreciated.

 

[nigelrivett]

Tempdb is recreated every time the server is restarted so a stop and start should shrink it - delete / rename the file if necessary.

That error sounds like a disk problem or maybe a page chaining error i.e a database corruption.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[CTekGirl]

Is it possible that the TEMPDB is corrupted? If so should I just recreate it? Are there any preventative measures to keep this from happening? I am trying to do some type of weekly/monthly maint of the database.

 

[CTekGirl]

The process of deleting or renaming this file did not have positive results. It actually prevented the database from starting up again through Windows. I wouldn't recommend this option.

I took a different route.

I started the database in single user mode using the command line sqlservr.exe -c -f

I went to query analyzer ran DBCC CHECKDB and I did not get any errors on tempdb or master.

I created a smaller tempdb to replace the 6gb file that was removed.

Exited from single user mode. Attempted to restart in Windows. The services started, but database did not come up and the tempdb grew from 2mb back to over 6gb.

I checked the ERRRORLOG again and this is the error that I am seeing:

2004-03-27 04:48:17.97 spid5 Error: 823, Severity: 24, State: 8
2004-03-27 04:48:17.97 spid5 I/O error 2(The system cannot find the file
specified.) detected during write at offset 0x000001636e0000 in file
'd:\Microsoft SQL Server\MSSQL\data\tempdb.mdf'..
2004-03-27 04:48:18.32 spid5 CREATE DATABASE failed. Some file names listed
could not be created. Check previous errors.
2004-03-27 04:48:18.32 spid5 WARNING: problem activating all tempdb files.
See previous errors. Restart server with -f to correct the situation.

As previously stated restarting the server in single more (with -f) did not resolve this problem.

My research on this error did not have a have bright outlook. It is a hardware error based on information from Microsoft. I checked the device manager and I noticed that a SCSI Controller was not working properly. I am uncertain if that is the issue but it looked like SQL Server was not going to come up on this disk. The event viewer also showed this same error.

I decided to move SQL Server and the database files to a different disk. I left the original installation of SQL Server in Place. But reinstalled it on a different disk. I was able to maintain it as a (local) server by removing 2 registry keys before installing.(NOTE: I wouldn't recommend this unless you know EXACTLY what you are doing). I exported the keys so that I could replace them if I need to switch back to the other installed copy. I needed to this because I didn't have an available server with space. I was hopeful that this error would not come up again.
I went through all the administrative steps of reattaching the databases under the new installation of SQL Server.
Fortunately the only thing that was lost from the Production and Development Databases were passwords.

If anyone has any ideas on how to retain passwords in a situation like this please let me know. I have only read how to do this if two instances of SQL Server are up and running at the same time.

At this point the server/database has been running for 48 hours with no errors on the disk. I have contacted our network admin and they will be looking into the error with the controller and the disk.

I also tested it by stopping and starting the server, connecting to the database through an application and running a process(through an application) that updates the table. The ERRORLOG does not show any database errors.

For informational purposes I will update this posting with the results from the Network Admin. I would not consider moving this environment until we are certain all disk errors have been resolved.