asked
Tempdb corruption
We are having some corruption in TempDB file (F:\) drive. When I run the sp_helpfile against tempdb, I got this: name fileid filename filegroup size maxsize growth usage tempdev 1 F:\tempdb.mdf PRIMARY 1138688 KB Unlimited 1024 KB data only templog 2 F:\TempDB\log\templog.ldf NULL 768 KB Unlimited 10% log only tempdev1 3 D:\Data0\tempdb.ndf PRIMARY 9930624 KB Unlimited 10% data only templog_1 4 E:\log\tempdb_1.ldf NULL 1280 KB 2147483648 KB 10% log only tempdev2 5 D:\Data0\tempdb_1.ndf PRIMARY 9930624 KB Unlimited 10% data only I am not able to access F drive where most of the TempDB is stored. I got the following link to fix it:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6fa6e803-18f5-461e-b875-97c8b02d9e31/change-location-of-tempdb-files?forum=sqlgetstarted What to modify in this query to work on our server? ![alt text][1] [1]: /storage/temp/4016-driveinfo.png
Assuming you can connect to the master, you could run this script to move all the tempdb files to the E:\ for now. You do not need to 2 log files for tempdb, but you can remove one of them later: use master go Alter database tempdb modify file (name = tempdev, filename = 'E:\Data0\tempdb.mdf') go Alter database tempdb modify file (name = templog, filename = 'E:\Data0\templog.ldf') go Alter database tempdb modify file (name = tempdev1, filename = 'E:\Data0\tempdb.ndf') go Alter database tempdb modify file (name = tempdev2, filename = 'E:\Data0\tempdb_1.ndf') go Alter database tempdb modify file (name = templog_1, filename = 'E:\Data0\tempdb_1.ldf') go Then, restart Sql Server. This will rebuild Tempdb.
20 People are following this question.
