DBA and SysAdmin World

March 28, 2009

Moving tempdb database to another disk

Filed under: SQL Server — Tags: , — solihinho @ 2:22 AM
move Issue
These weeks errors occurred several times on my SQL Server event viewer “I/O error (bad page ID) detected during read at offset 0x000000XXXXX in file “C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf”. This error may be related to our hardware. We tried to change our disk on drive C but the error still occurred. To escalate this error we tried to move tempdb data file on drive C to another disk which is have different disk controller.

Step by step

1. Determine logical name of file that we want to move

USE tempdb
GO
sp_helpfile

On below sample, the logical name are tempdev and templog.

 image 

2. Move the data file using ALTER DATABASE statement

USE master
GO
ALTER DATABASE tempdb modify file (name = [logical name], filename = '[new location of file]')

e.g.:

ALTER DATABASE tempdb modify file (name = tempdev, filename = 'D:\SQLDATA\tempdb.mdf')
or
ALTER DATABASE tempdb modify file (name = templog , filename = 'D:\SQLLOG\tempdb_log.ldf')

3. This alteration will confirm after we restart the SQL Server service. Stop than restart the SQL Server service. You should delete  the old files manually.

Result
After this action, the error does not occur any more Happy. Meanwhile we still investigate why the error occurred. We also open ticket to Microsoft to help us investigate this issue.


Advertisements

6 Comments »

  1. Interesting!
    Output DBCC CHECKDB?
    RAID Configuration? Recent services packs and patches?

    CU
    tosc

    Comment by tosc — March 30, 2009 @ 4:31 PM

    • Hi Tosc .. Last time when I tried DBCC CHECKDB no error found .. Drive C using RAID 1 and our SQL server using SP4 .. This doesn’t occur in another SQL server with similiar patches. Now our tempdb locate in SAN and I haven’t tried to put it back in drive C.

      Comment by solihinho — March 30, 2009 @ 11:37 PM

  2. Hi solihinho,
    it looks like a file corruption of your temddb. You know that the tempdb is created at startup!
    CU
    tosc

    Comment by tosc — March 31, 2009 @ 2:23 PM

    • Hi tosc .. I think so .. If tempdb is created at startup, does it mean tempdb re-create if I restart my SQL server? I just got the same error when restart the SQL server. Thanks tosc ..

      Comment by solihinho — March 31, 2009 @ 11:28 PM

  3. Hi solihinho,
    did you copy any file of the tempdb when you using your script :-) I m sure that you doesn t copy anything.

    Comment by tosc — April 2, 2009 @ 2:05 AM

    • Now I understand what do you want to tell me :) .. Thanks Tosc ..

      Comment by solihinho — April 4, 2009 @ 11:17 AM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: