Showing posts with label move tempdb files. Show all posts
Showing posts with label move tempdb files. Show all posts

Monday, 12 June 2017

Use SQL Server Temporary Drive for Temp Database

Configure SQL Server to use Azure VM Temporary drive to store Tempdb database


By default, SQL Server uses C drive to store its Tempdb Database. Both its data file and lof file goes in C Drive only. Tempdb database grows heavily in certain scenarios like when database backup is happening, log shipping is active on a server, index rebuilding(if sort in tempdb is on) is getting performed , some bulk operations are going on in the background etc.
This article demonstrates the process to configure SQL Server to use Azure VM Temporary drive to store Tempdb database. Throughout this document we will be using D as a temporary drive.
Our configuration process includes-

  • Modify PowerShell script
  • Execute Alter database commands on SQL Server
  • Configure the Start Mode of SQL Server and SQL Agent startup as Manual.
  • Create a PowerShell script to recreate the folder in D:\ if needed and start SQL server
  • Create a schedule task at system startup to execute the PowerShell script.


Before proceeding for the movement you need to identify following information

1.        SQL Server and SQL server Agent Service Names
2.        Name of user under which above services are running.
3.        Azure VM Temporary drive should have enough space available to hold Tempdb database


1.        Prerequisites
·         PowerShell script
############## Script for Azure Recovery Services Monitoring ##############
$SQLService="sql_server_service_name" #### Ex -  SQL Server (MSSQLSERVER)
$SQLAgentService="sql_server_agent_service_name" #### Ex - SQL Server Agent (MSSQLSERVER)
$tempfolder="D:\SQLTEMP"
if (!(test-path -path $tempfolder)) {
New-Item -ItemType directory -Path $tempfolder
$Acl = Get-Acl $tempfolder
$permission = 'user_name','FullControl','ContainerInherit, ObjectInherit', 'None', 'Allow'   #### Ex - 'NT AUTHORITY\NetworkService'
$Ar = New-Object  system.security.accesscontrol.filesystemaccessrule $permission
$Acl.SetAccessRule($Ar)
Set-Acl $tempfolder $Acl
}
Start-Service $SQLService
Start-Service $SQLAgentService

2.   Steps to configure
2.1. Modify the above created PowerShell script-
Modify information marked in yellow as per requirement. To highlight, you need to modify

a.    SQL Server Service Name (sql_server_service_name)
b.   SQL Server Agent Service Name (sql_server_agent_service_name)
c.    User Name for above services (user_name)
2.2. Execute Alter database commands on SQL Server -
Using SSMS connect to your SQL Server instance. Execute the following T-SQL commands to change location of the TempDB files:
USE MASTER
GO
ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= 'D:\SQLTEMP\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'D:\SQLTEMP\templog.ldf')
GO
2.3.    Configure the Start Mode of SQL Server and SQL Agent startup as Manual-
Using Configuration Manager set the Start Mode of SQL Server and SQL Agent as Manual.

Temporary drive in AZURE

2.4. Create a PowerShell script to recreate the folder in D:\ if needed and start SQL server
Copy and paste above PowerShell script and save it as a PowerShell file in the “C:\myfolder” drive (OS drive), for example as “C:\myfolder\SQL-startup.ps1”.
2.5. Create a schedule task at system startup to execute the PowerShell script
Using Task Scheduler create a Basic Task that executes when the computer starts and executes the script in the PowerShell file. For this, specify:
Program/script:                PowerShell
Arguments:                  –file ‘C:\myfolder\SQL-startup.ps1’
schedule task at system startup to execute the PowerShell script


With this, every time your Server gets rebooted, a new folder in D Drive will be created (if in case somehow it does not get created itself) for your temp database files. This will enable smooth operations of your SQL Server system as well.

So we have seen that we can Configure SQL Server to us Temporary drive to store Tempdb database operations and make an ample use of Azure's Temporary Drive