How to move Tempdb to another Drive.

USE TempDB GO EXEC sp_helpfile--By this we can find where the .mdf and .ldf files are placed defaultly. GO USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'D:\30-04-2009BAK\Tempdb\datatempdb.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'E:\TempdbLog\datatemplog.ldf') GO After running the above query Stop and Restart the services. 
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'D:\DATA\tempdb.mdf')
ALTER DATABASE TempDB MODIFY FILE
(NAME = temp2, FILENAME = 'D:\DATA\tempdb_mssql_2.ndf')
ALTER DATABASE TempDB MODIFY FILE
(NAME = temp3, FILENAME = 'D:\DATA\tempdb_mssql_3.ndf')
ALTER DATABASE TempDB MODIFY FILE
(NAME = temp4, FILENAME = 'D:\DATA\tempdb_mssql_4.ndf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'D:\DATA\templog.ldf')
GO
After this change stop and start the services and you can see the data files of temp databases in respective path.

Comments

Post a Comment

Popular posts from this blog

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'...
Read more

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

The transaction log for database is full due to 'OLDEST_PAGE'

When I see the 'OLDEST_PAGE' in log_reuse_wait_desc column and not allowing to me to shrink the log file of database I followed the below steps 1) When you see 'OLDEST_PAGE' run CHECKPOINT on that particular database 2) Take the Transaction log backup of that particular database 3) Try to shrink the log file of database These steps worked for me when I encountered this issue in my environment.
Read more