How to move TEMPDB files to different drives in SQL Server

Today, we are going to see how to move the TEMPDB files from one location to another.
First off, we should know the number of files and the its current location.
Query


SELECT NAME,PHYSICAL_NAME FROM SYS.MASTER_FILES WHERE DATABASE_ID=2

Query to move the files to new location

You may change the @NEWPATH variable value as per your new location.

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @NEWPATH VARCHAR(MAX) = 'D:\DATA\DATA'
SET @SQL= @SQL + (SELECT 'ALTER DATABASE TEMPDB MODIFY FILE (NAME = ' + NAME + ', FILENAME = ''' + 
				@NEWPATH + RIGHT(PHYSICAL_NAME,CHARINDEX('\',REVERSE(PHYSICAL_NAME))) + ''');'
FROM SYS.MASTER_FILES WHERE DATABASE_ID = 2 FOR XML PATH(''))

EXEC ( @SQL )

Once you executed the above query, you need to restart the server to see the changes.

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

You can also go to the previous file location and delete the old files from the folder.

It is important to know that the moving of TEMPDB files no longer supporting using backup & restore OR  detaching & attaching(system databases).
Advertisements

T-SQL Script to restore backup files from a folder in SQL Server

Very often, myself or my friends were in need of a script to restore the backups from a predefined folder. It was hard to write the script in the hour of need. Hence, I thought I will share the script that has been created recently. Hope this script would be useful for you as well.


DECLARE @BACKUPFILENAME VARCHAR(MAX) = ''
DECLARE @MDFLOGICALNAME NVARCHAR(100)
DECLARE @LDFLOGICALNAME NVARCHAR(100)
DECLARE @DEST_PATH NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DATABASENAME SYSNAME
CREATE TABLE #TRAILUPGRADEBACKUPFILES (ID INT IDENTITY(1,1),FILENAME SYSNAME,RESTORE_DRIVE CHAR(1))

/*********************************************************************************************************************************/
--The below code fetches the file list(backup file names) to be restored from the pre-defined folder
DECLARE @CommandShell TABLE ( Line VARCHAR(512)) 
DECLARE  @CMD VARCHAR(512) ,@BackupFilePath NVARCHAR(256)
SET @BackupFilePath = 'C:\TEMP_TOBEDELETED\' 
SET @CMD = 'DIR /B ' + @BackupFilePath +  ' /TC' 
				
INSERT INTO @CommandShell EXEC MASTER..xp_cmdshell   @CMD 
    
-- Delete lines not containing filename
DELETE 
FROM   @CommandShell 
WHERE  Line is null

If((Select Top 1 Line From @CommandShell) = 'Access is denied.') 
    PRINT 'Folder access is not provided for network path'
				
INSERT INTO #TRAILUPGRADEBACKUPFILES (FILENAME)
	Select * From @CommandShell A WHERE CHARINDEX('.BAK',LINE) > 0
/*********************************************************************************************************************************/

CREATE TABLE #TFILELISTONLY 
(
    TLOGINNAME SYSNAME,TPHYSICALNAME VARCHAR(MAX),
    TTYPE VARCHAR(1),TFILEGROUPNAME  VARCHAR(MAX),
    TSIZE BIGINT,TMAXSIZE BIGINT,TFIELD  VARCHAR(MAX),
    TCREATELSN VARCHAR(MAX),TDROPLSN VARCHAR(MAX),
    TUNIQUEID VARCHAR(MAX),READONLYLSN VARCHAR(MAX),
    READWRITELSN VARCHAR(MAX),BACKUPSIZEINBYTES VARCHAR(MAX),
    SOURCEBLOCKSIZE VARCHAR(MAX),FILEGROUPID VARCHAR(MAX),
    LOGGROUPGUID VARCHAR(MAX),DIFFERENTIALLSN VARCHAR(MAX),
    DIFFERENTIALBASEGUID VARCHAR(MAX),ISREADONLY VARCHAR(MAX),
    ISPRESENT VARCHAR(MAX),TDEHUMBPRINT VARCHAR(MAX),
)

WHILE EXISTS(SELECT 1 FROM #TRAILUPGRADEBACKUPFILES)
BEGIN
	
	SET @BACKUPFILENAME = (SELECT TOP 1 FILENAME FROM #TRAILUPGRADEBACKUPFILES ORDER BY FILENAME ASC)
	--Restore Path needs to be provided in the below
	SET @DEST_PATH = 'C:\DATA\'
		
	SET @DATABASENAME = (SUBSTRING(@BACKUPFILENAME,0,CHARINDEX('.',@BACKUPFILENAME)))
				
	--To check if the database is already present in the environment, If present, Drop the database
	IF Exists(SELECT 1 FROM master.sys.databases WHERE name = @DATABASENAME)
	BEGIN
	
		EXEC('DROP Database '+@DATABASENAME)
		Print @BACKUPFILENAME + ' has been successfully dropped.'
		
	END

	INSERT INTO #TFILELISTONLY
		EXEC ('RESTORE FILELISTONLY FROM DISK ='''+ @BackupFilePath +  @BACKUPFILENAME+'''')

	SET @MDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='D')
	SELECT @LDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='L')
        
	SELECT @SQL ='RESTORE DATABASE ' + @DATABASENAME + ' FROM DISK = ''' +  @BackupFilePath + @BACKUPFILENAME + ''' 
		WITH MOVE ''' + @MDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.MDF'', 
		MOVE '''  + +  @LDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.LDF'''
	
	EXEC (@SQL)
	
	Print @BACKUPFILENAME + ' has been successfully restored.'
		
	EXEC ('ALTER DATABASE [' + @DATABASENAME + '] SET RECOVERY SIMPLE;')

	DELETE FROM #TFILELISTONLY


	DELETE FROM #TRAILUPGRADEBACKUPFILES WHERE FILENAME =@BACKUPFILENAME	

END

DROP TABLE #TFILELISTONLY,#TRAILUPGRADEBACKUPFILES

How to identify start up Parameters in SQL Server using T-SQL

Here is a very simple script to identify the startup parameter in SQL Server using T-SQL.


SELECT
    registry_key,
    value_name,
    value_data
FROM sys.dm_server_registry 
WHERE 
    registry_key LIKE N'%MSSQLServer\Parameters';

If you are aware of any other methods, I request you to share for other’s benefits.

EDIT: (On 22nd Aug 2016)
One of my colleague executed the above script and reported me back that he is getting the below error:

Msg 208, Level 16, State 1, Line 9
Invalid object name 'sys.dm_server_registry'.

The issue was that the version of his SQL Server was SQL Server 2008R2 (RTM).
This is a new DMV introduced from service pack (SP1) and available only for later versions of SQL Server 2008R2(SP1).

How to check SQL Services status in SQL Server

As a DBA, it is very important for all of us to make sure SQL Services are up and running.But Is there an easy way to get this information in SQL Server?

The answer is simple, yes. Microsoft has introduced a new DMVs – sys.dm_server_services. dm_server_services DMV is available right from SQL Server 2008R2 (SP1). This has made life easy for most of DBAs from writing a bunch of codes.

SELECT 
	SERVICENAME, STARTUP_TYPE_DESC, STATUS_DESC,
        LAST_STARTUP_TIME,SERVICE_ACCOUNT,
        IS_CLUSTERED ,CLUSTER_NODENAME 
FROM SYS.DM_SERVER_SERVICES

ServiceCheck

Notes:

1. Login needs to be provided with VIEW SERVER STATE PERMISSION
2. Support is available for SQL Server 2008 R2(SP1) and later versions

Read Also:
SYS.DM_SERVER_SERVICES

Workload Group Stats counters and SQL Server Edition

Problem Statement

During one of our performance evaluation, we wanted to assess the resource utilization for one of servers using the Perfmon Counters.

If you are not familiar with perfmon counters, please have a look at the below link for the improtance and significance of perfmon counter in performance evaluation.

https://technet.microsoft.com/en-us/magazine/2008.08.pulse.aspx

In our assessment, in addition to the usual counters, we wanted to collect Workload information from SQL Server.Hence, we enabled the below counters:


SQLServer:Workload Group Stats/ CPU Usage %
SQLServer:Resource Pool Stats/ CPU Usage %

After configuring the above counters in the perfmon, it is observed that the above counters are not collecting the information from the server. However, when we configured it locally, it was collecting the counter values.

These counter values can also be queried in SSMS using the below queries:


Select * From [master].[sys].[dm_os_performance_counters]
Where 
(object_name ='SQLServer:Workload Group Stats' and counter_name ='CPU Usage %') OR
(object_name ='SQLServer:Resource Pool Stat' and counter_name ='CPU Usage %')

Cause/Reason:

On further analysis, we identified that SQLServer:Workload Group Stats object contains performance counters that report information about Resource Governor workload group statistics.

As Resource Governor can only be supported for Datacenter, Enterprise, Developer and Evaluation editions, those counters can not be collected information on any other editions, eventually we have Standard edition in our environment.

Lesson Learned:

When you configure perfmon counters, always check the associated features and its supporting version.

How to track DBCC commands issued in SQL Server using default trace

If you are a production DBA and responsible for your SQL Server box, sometimes, you may need to know certain information like who run the DBCC commands or when did it triggered or How did it triggered etc.

This post provides a quick way to identify those information using the below query to track DBCC commands that were issued in a SQL Server.

The query uses FN_TRACE_GETTABLE function to read information from the default trace in SQL Server.

SELECT
	T.DatabaseName,
	T.TextData,
	T.HostName,
	T.ApplicationName,
	T.LoginName,
	T.StartTime,
    CASE T.IsSystem
		WHEN 1 THEN 'YES'
		ELSE 'NO'
		END AS IsSystem
FROM sys.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150), 
	( SELECT TOP 1 f.[VALUE]                                                 
		  FROM    sys.FN_TRACE_GETINFO(NULL) f
		  WHERE   f.property = 2
		)), DEFAULT) T
WHERE T.EventClass = 116

The above code shows the way to track *ONLY* DBCC commands(EventClass = 116). However, you may be able to change the Query to filter the EVENTCLASS to identify other Audit events.

DBCC_Tracker

To add, How do you identify your default trace?

SELECT * FROM sys.traces WHERE is_default = 1

Alternatively,

SELECT *, f.[VALUE]                                                 
		  FROM    sys.FN_TRACE_GETINFO(NULL) f
		  WHERE   f.property = 2

If you want to explore on other events, you can collect the Event trace id from the below query and replace the EventClass filter value in the above query.

Select * From sys.trace_events

How to troubleshoot DISK/IO performance issues in SQL Server

It is always my favorite subject troubleshooting performance issues in SQL Server. I really enjoy learning new methods and approaches to troubleshoot the performance issues in SQL server. There is no hard rule or a single window approach for all issues, may be the best part in troubleshooting that I would love at most. However, I learned it is always a very tough job when it come at DISK or IO level issues.

Of late, I faced a curious case of IO performance issue in one of our performance test environment, showing high response time resulted to a delay in processing. As always, the analysis started with LIVE Monitoring queries to identify the requests that are running and for any blocks on the server.While analyzing, it is observed that the requests are in suspended mode with a wait type “PAGEIOLATCH_**”.

PAGEIOLATCH_** wait typically represents an issue with DISK/IO. However, there could be many other reason for which cause PAGEIOLATCH_** eg. non-optimal index, bad coding etc. So the first thing we wanted to make sure that there are no issues with code or design (index or procedure code) and no lock/blocks occurring due to the same.

As we could not find blocking/locking in our case, we further moved to analyze the issue at resource level. As a very first step, the performance counter has been enabled on the server to enable the analysis. By enabling the counters, we would be able to analyze the resource utilization at server level.

Please find sample templates(XML) to collect major performance counters for SQL Server & .NET.You may download the below templates and change the extension to XML and configure in perfmon tool(easy to configure with template, you may be able to add more counters to the list as per your requirement).
Perfmon_SQLServer_Template and Perfmon_dotNet_Template

The attached template will collect the information on various counter for Memory/processor/Disk etc. As we are finding the issues are associated with DISK, the scope of our analysis on the counter evaluation restricts at DISK level for this blog post, you may need to carry out the analysis for other resource levels as well.

What we need to look out?

DMV Analysis on Wait types (sys.dm_os_wait_stats)

— The wait type analysis for PAGEIOLATCH_**.

SELECT
    *
   ,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms'
FROM sys.dm_os_wait_stats 
WHERE waiting_tasks_count > 0 and wait_type in ('PAGEIOLATCH_SH','PAGEIOLATCH_EX')
ORDER BY wait_time_ms DESC

In our case, we identified there are lots of queries in suspended with PAGEIOLATCH_** wait type in our server and the [Avg Wait in ms] was above 40 ms. I personally would recommend to investigate the [Avg Wait in ms] with value more than 20 ms and above for any issues(it may not always indicate a DISK issue).

Perfmon counter analysis

— There are many counters related with DISK, out of which the below counters are my favorites to start the analysis with DISK.

Performance Threshold

Note:The above thresholds are not a general thumb rule, but it can be used as a reference.Read the comments in the picture above carefully to understand more about.

DMV Analysis on IO Stall (sys.dm_io_virtual_file_stats)

— IO stall read/write information gives us for every IO read/write latency for a period of time and it is a cumulative average.

select database_id, file_id
    ,io_stall_read_ms
    ,num_of_reads
    ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
    ,io_stall_write_ms
    ,num_of_writes
    ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
    ,io_stall_read_ms + io_stall_write_ms as io_stalls
    ,num_of_reads + num_of_writes as total_io
    ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc

It is observed that [avg_io_stall_ms] value is considerably higher with one of our drives(D).

To confirm, we moved all the data files from D drive to E drive and observed a huge differences in performance. You may find the data points as below.

PAGEIOLATCH_** wait type Information(Before Vs After)

PAGEIOLATCH_Comparison

IO Stall Information(Before Vs After)
DISK_IO_STALL_Compare

Hope, the above methods would help you to troubleshoot issues at disk level.However, these observations needs to be shared and verified with a storage engineer to confirm the issue with DISK/IO.

I would also like to request you to have your comments to share more approaches you may dealt in the past as part of our learning and sharing.