How to move tempdb

As a DBA sometimes we have to move our tempdb to other drive due to I/O or space issue.

Here we will look into how to move tempdb
Since tempdb is recreated every time we start server we dont have to move files physically.

Get name of files for tempdb database.

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(‘tempdb’);
GO

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = , FILENAME = );

Now when we will restart server again at that time tempdb will be created at new location.

How to know when SQL Server was started?

Many times we want to know when SQL Server was last started.
For this here is a simple way

As we know tempdb always recreated when SQL Server starts , so from create_date of tempdb we can get idea when the server was started.

select create_date from sys.databases where name = ‘tempdb’

Job Monitoring Part – 1 Get execution / enabled /disabled status

As a DBA we we have to regular play with jobs. Monitor jobs is a important activity for any DBA.

Here I am going to discuss an undocumented proc SP_GET_COMPOSITE_JOB_INFO which can be a great help for all of us.

To visit other parts click here.

Job Monitoring Part 2

Job Monitoring Part 3

As we all know that we can monitor jobs by connecting to ther server using management studio -> SQL Server Agent -> Job Activity Monitor
But what if we can do that by using TSQL ?

Here is answer for that
We can find this procedure in MSDB Database.

Syntax
EXEc sp_get_composite_job_info

@job_id UNIQUEIDENTIFIER = NULL,

@job_type VARCHAR(12) = NULL, — LOCAL or MULTI-SERVER

@owner_login_name sysname = NULL,

@subsystem NVARCHAR(40) = NULL,

@category_id INT = NULL,

@enabled TINYINT = NULL,

@execution_status INT = NULL,

@date_comparator CHAR(1) = NULL, — >, < or =” @date_created” datetime =” NULL,” datetime =” NULL,” int =” NULL” style=”color: rgb(0, 0, 0);”>USE MSDB
Exec sp_get_composite_job_info
Will provide basic info all the jobs on the server.

To find job execution status

So if we want to know which jobs are running right now

Exec sp_get_composite_job_info @execution_status = 1

Which will give info for the jobs which are running now.

Other Value of Execution Status are

0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
[6 = WaitingForStepToFinish],
7 = PerformingCompletionActions

To find jobs enabled/disabled

If we want to know which jobs are enabled/disabled

Exec sp_get_composite_job_info @enabled = 1 — For enabled jobs

Exec sp_get_composite_job_info @enabled = 0 — For disabled jobs

Will provide results for jobs which are enabled/disabled.

New DateTime function in SQL Server 2008

SQL Server 2008 comes with some new datetime function ,
lets get some overview of it

SYSDATETIME

Provide current date and time in datetime2 datatype

select GETDATE()

Result
2009-03-11 10:21:06.643

select SYSDATETIME()

Result 2009-03-11 10:21:06.6452500

Here we can see difference in Time. Getdate is using datetime datatype and can provide data up to micro second while SYSDATETIME is using datetime2 datatype and can provider result up to 100 nanoseconds.

SYSUTCDATETIME

Provide result in datetime2 datatype

select GETUTCDATE()

Result
2009-03-11 10:33:01.550

select SYSUTCDATETIME()

Result
2009-03-11 10:33:01.5515000

Here we can see difference in Time. GETUTCDATE is using datetime datatype and can provide data up to micro second while SYSUTCDATETIME is using datetime2 datatype and can provider result up to 100 nanoseconds.

SYSDATETIMEOFFSET

SELECT SYSDATETIMEOFFSET()

Result
2009-03-11 16:04:13.2390000 +05:30
Provides current datetime in datetime2 datatype with timezone difference.