question

vivekchandra09 avatar image
vivekchandra09 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
tempdbshrink-log-filelog-file-sizet-log corruption
driveinfo.png (7.0 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is SQL Server even online or has it completely scrambled?
0 Likes 0 ·
SQL Server is online.
0 Likes 0 ·
rvsc49 avatar image
rvsc49 answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DazyParker avatar image
DazyParker answered
I will suggest you to check the process of this video: https://www.youtube.com/watch?v=sB4a5qCxRgA
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.