Moving the tempdb database

There are cases when you might want to move tempdb database from an existing drive to a new drive.

1. When the drive is full and you are in a situation where you cannot extend that drive.
2. Move tempdb to a separate drive to increase its performance.

This is a simple process and cannot be done by detaching and attaching the database, as we cannot attach or detach a system database.
Also we need to restart the SQL services.

Here is the process how we can move the tempdb to a new location.

1. First get the list of tempdb files by using this query
select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'
2. Then for each tempdb file that you need to move, execute statements like below

Alter Database tempdb modify
file (NAME = 'tempdev' , 
FILENAME = 'Drive:\Path\tempdb.mdf') -- Mention the new location

Alter Database tempdb modify
file (NAME = 'templog' , 
FILENAME = 'Drive:\Path\templog.ldf') -- Mention the new location

3. Stop SQL Services
4. Start SQL Services
5. Verify the new Location
   select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'

Start or Stop SQL Services using SQLCMD

In one of my previous post "SQLCMD Mode - Run your queries against several servers in a single query window.", i had explained on how to connect and execute queries in SQLCMD mode.
Hope that was useful.

In this post i will explaining on how to stop and start SQL Server services using SQLCMD mode.
First of all, Open a new query window and Change the query execution mode to SQLCMD.
Then type the below command in the new query window that is opened in the SQLCMD mode.
1. To start the services, use the below commands
/* Start Services */

!!NET START MSSQL$MSSQLSERVER

!!NET START SQLAGENT$MSSQLSERVER

2. To stop the services, use the below commands
/* Stop Services */

!!NET STOP SQLAGENT$MSSQLSERVER

!!NET STOP MSSQL$MSSQLSERVER

If you are trying to start or stop a default instance, then then will no changes to the above query.
If you are trying to start or stop a named instance, then Replace MSSQLSERVER with your instance name.
For example, if you are trying to start or stop the named instance "SQL2008", then the commands will look as below.

/* Stop Services */

!!NET STOP SQLAGENT$SQL2008

!!NET STOP MSSQL$SQL2008

/* Start Services */

!!NET START MSSQL$SQL2008

!!NET START SQLAGENT$SQL2008

Quick flash back on MS SQL Server Code Names

Code Name	Final name
SQL95	SQL Server 6.0
Hydra	SQL Server 6.5
Sphinx	SQL Server 7.0
Shiloh	SQL Server 2000 (32-bit)
Liberty	SQL Server 2000 (64-bit)
Yukon	SQL Server 2005
Katmai / Akadia	SQL Server 2008
Kilimanjaro	SQL Server 2008R2
Denali	SQL Server 2012
Hekaton	SQL Server 2014

Reference: http://en.wikipedia.org/wiki/List_of_Microsoft_codenames#SQL_Server_family

SQL Server 2008 : T-SQL Enhancement

As we all know, SQL Server 2008 came up with a huge number of changes which are helpful for both developers as well as DBA's. In this post I will be demonstrating about once such enhancement that is very helpful for data insertion.

The usual syntax for inserting data to a table is 

Insert into tbl_TSQLEnhancement values (1,'Usual','SansSQL')

Insert into tbl_TSQLEnhancement values (2,'Usual','Blog')

Insert into tbl_TSQLEnhancement values (3,'Usual','T-SQL')

Insert into tbl_TSQLEnhancement values (4,'Usual','SQL Server 2008')

If we are inserting multiple records at a time then we will have to write statements as shown above. But from SQL Server 2008 onwards, it is made easy by giving an option to insert more than one record in a single T-SQL statement as shown below.

Insert into tbl_TSQLEnhancement values

       (1,'New','SansSQL')

      ,(2,'New','Blog')

      ,(3,'New','T-SQL')

      ,(4,'New','SQL Server 2008')

Cool feature right?

So now it's time to try it out yourself.

/* Create Table */

Create Table tbl_TSQLEnhancement

(Id int not null,

Method nvarchar(10),

Name nvarchar(20))

/* Insert Data to table using Usual method */

Insert into tbl_TSQLEnhancement values (1,'Usual','SansSQL')

Insert into tbl_TSQLEnhancement values (2,'Usual','Blog')

Insert into tbl_TSQLEnhancement values (3,'Usual','T-SQL')

Insert into tbl_TSQLEnhancement values (4,'Usual','SQL Server 2008')

/* Insert Data to table using New method */

Insert into tbl_TSQLEnhancement values

       (1,'New','SansSQL')

      ,(2,'New','Blog')

      ,(3,'New','T-SQL')

      ,(4,'New','SQL Server 2008')

/* Retrive Data */

Select * from tbl_TSQLEnhancement

/* Cleanup Process */

Drop table tbl_TSQLEnhancement

Difference between Getdate() and GetUTCDate()

GETDATE(): 

GETDATE()

 function returns the current database system timestamp. This value is derived from the operating system of the computer on which the instance of SQL Server is running. 

If you are connected to the SQL server remotely then the timestamp displayed will be the timestamp of the SQL server machine and not your local machine.

Usage: SELECT GETDATE() as [ServerTime]

GETUTCDATE(): 

GETUTCDATE() function returns the current UTC time. This value is derived from the operating system of the computer on which the instance of SQL Server is running. 

This can be used to store the timestamp that is independent of Time Zones.

Usage: 

SELECT GETUTCDATE() as [UTCTime]