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.


Create a free website or blog at WordPress.com.