T-SQL to get the SQL Server Service Start time and Up time of SQL Server service

There are number of occasions where we need to get to know the SQL Server service start date time and also from what time the SQL Services are up and running generally termed as Uptime. There are many ways to do it like check for temp DB Creation time, using DMF sys.dm_io_virtual_file_stats etc. Below written is small T-SQL script but very useful which provides you this information. Hope this is useful. Comments and suggestions are always welcome. .

USE MASTER

GO

SET NOCOUNT ON

GO

SELECT 'SQL server started at ' +

CAST((CONVERT(DATETIME, sqlserver_start_time, 126)) AS VARCHAR(20))

+' and is up and running from '+

CAST((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))/60 AS VARCHAR(5))

+ ' hours and ' +

RIGHT('0' + CAST(((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))%60) AS VARCHAR(2)),2)

+ ' minutes' AS [Start_Time_Up_Time] FROM sys.dm_os_sys_info

GO

SET NOCOUNT OFF

GO

T-SQL to get various details on currently running Active processes on SQL Server Instance

Couple of day back i was working on something very important which will run some stored procedures to accomplish the task. Now there can be many transactions or different piece of codes in a Stored Procedure. Now just think that you come across a weird situation where you are not getting your results on time or may be its taking more time than expected. Now you can determine which is session and spid your procedure is running, but what if you want to know which piece of code is running which might me causing the issue, since how long it is running etc these kind of various questions comes to mind and we are stuck. Now thanks to DMV's and DMF's which makes our task easier by giving various piece of information which will be very useful for us. So i came with a small piece of TSQL which will answer these kind of questions and gives us the vital information.

This also provides information on estimated completion time, completed percentage for various operations like Backup,Database Integrity Check, Index Re-Organise etc.

USE MASTER

GO

SELECT SPID,ER.percent_complete,

/* This piece of code has been taken from article. Nice code to get time criteria's

http://beyondrelational.com/blogs/geniiius/archive/2011/11/01/backup-restore-checkdb-shrinkfile-progress.aspx

*/CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
/* End of Article Code */
ER
.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,

DB_NAME(SP.DBID) AS DBNAME,

SUBSTRING(est.text, (ER.statement_start_offset/2)+1,

((CASE ER.statement_end_offset

WHEN -1 THEN DATALENGTH(est.text)

ELSE ER.statement_end_offset

END - ER.statement_start_offset)/2) + 1) AS QueryText,

TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,

SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME

FROM SYSPROCESSES SP

INNER JOIN sys.dm_exec_requests ER

ON sp.spid = ER.session_id

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST

ORDER BY CPU DESC

TSQL to get All Primary Key Columns and its relevant details

I was working on database design stuff and came across a situation where in i had to get all the primary keys existing in database for all tables and the primary key columns and column details. It would be very had and insane to go into each table and search for PK and its data. So i came up with this small script which gives you all information on Primary Keys. This can be modified as per your needs to get more information or for foreign keys ets. Hope this is useful for some guys looking for it. It was very helpful for me though :)
SELECT SS.NAME AS [TABLE_SCHEMA], ST.NAME AS [TABLE_NAME]
, SKC.NAME AS [CONSTRAINT_NAME], SC.NAME AS [CONSTRAINT_COLUMN_NAME],
CAST(STY.NAME AS VARCHAR(20)) +'('+
CAST(CASE ST.NAME
WHEN 'NVARCHAR' THEN (SELECT SC.MAX_LENGTH/2)
ELSE (SELECT SC.MAX_LENGTH)
END AS VARCHAR(20)) +')' AS [DATA_TYPE]
FROM SYS.KEY_CONSTRAINTS AS SKC
INNER JOIN SYS.TABLES AS ST
ON ST.OBJECT_ID = SKC.PARENT_OBJECT_ID
INNER JOIN SYS.SCHEMAS AS SS
ON SS.SCHEMA_ID = ST.SCHEMA_ID
INNER JOIN SYS.INDEX_COLUMNS AS SIC
ON SIC.OBJECT_ID = ST.OBJECT_ID
AND SIC.INDEX_ID = SKC.UNIQUE_INDEX_ID
INNER JOIN SYS.COLUMNS AS SC
ON SC.OBJECT_ID = ST.OBJECT_ID
AND SC.COLUMN_ID = SIC.COLUMN_ID
INNER JOIN SYS.TYPES AS STY
ON SC.USER_TYPE_ID = STY.USER_TYPE_ID
ORDER BY TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME;

SQL Script to Create Roles,Schemas, Add users and Associate Schema to Role

/* SQL Script to Create Roles, Schemas and users to the same */

--Creation of Database Role and Adding User's begin

USE [YOURDATABASENAME]

GO

CREATE ROLE [YOURROLENAME]

GO

      --Adding Users Begin

      EXEC sp_addrolemember N'YOURROLENAME', N'USERNAME1'

      GO

      EXEC sp_addrolemember N'YOURROLENAME', N'USERNAME2'

      GO

      --Adding Users End

      --Similarly Multiple users can be added using EXEC sp_addrolemember

--Creation of Database Role and Adding User's End

--Creation of Schema Starts

USE [YOURDATABASENAME]

GO

CREATE SCHEMA [YOURSCHEMANAME] AUTHORIZATION [YOURSCHEMANAME]

GO

--Creation of Schema End

--Association of Created Schema to Role Starts

USE [YOURDATABASENAME]

GO

ALTER AUTHORIZATION ON SCHEMA::[YOURSCHEMANAME] TO [YOURROLENAME]

GO

--Association of Created Schema to Role End

Grouping data from multiple rows into single row

Often we come across situation where we need to group data from multiple rows into single row. E.g.: An Employee works for multiple projects, so in this case if columns are EMPLOYEE_NAME, PROJECT_DURATION, PROJECTS_WORKED and think that employee works for 10 projects. Then you would have 10 rows containing EMPLOYEE_NAME as same and then 10 different projects. If you now want to have a single row with Employee name and then 10 subsequent name delimited in single column, unfortunately we don’t have any function like GROUP_CONCAT as in MySQL for SQL server. But not to worry this can be achieved by using XML PATH. Below is the example .

Insert into EMPLOYEE_PROJECT(EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME)

SELECT EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME=

REPLACE(

REPLACE(

(SELECT REPLACE(PROJECT_NAME, ' ', '')

AS [data()] FROM EMPLOYEE_PROJECT_DETAILS

WHERE EMPLOYEE_NAME=a.EMPLOYEE_NAME

FOR XML PATH('')),' ',' ; '),'',' ')

FROM EMPLOYEE_PROJECT_DETAILS a WHERE EMPLOYEE_NAME IS NOT NULL GROUP BY EMPLOYEE_NAME,PROJECT_DURATION

Small query but very useful.

Happy Coding

Technorati Tags: Group,Grouping,XML PATH

SQL Server Agent Proxies

SQL Server Agent Proxies are very helpful in executing some jobs. I had came across a situation where in I was scratching my head so as to how to accomplish a task which I wanted to run as SQL server Agent Job. First let me give some brief description about proxies.

SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

Now lemme give you a small example where how exactly a proxy can be helpful.

Say you have around 10 database servers,  both SQL Engine and SQL Agent services on these servers run on different Service Account say Machine Specific. Ex Ser1, Ser2, Ser3 and so on. Now here is the task. You want some operations may be a T-SQL or some script combined into SSIS package to be run on all these services. One way to achieve this is schedule a Job on each of these server and what if you want a consolidated data like health of all servers or logins existing on these servers as single report. This is where your Proxy comes in handy. To achieve this task get a Service Account(A windows Domain user Account), give access to this service account on all servers and then create a credential for this service account and create proxy for this credential on any of the one server and execute this Job or package from one single server and get combined data on one server. By doing so what exactly happens is SQL server agent impersonates the associated User account with proxy against the server on which the script/job needs to be run and since you have provided access to this service account on all server you will be able to run the same. Now lemme guide you through a step wise procedure to create a proxy using T-SQL.

--Detailed Steps to create a SQL proxy using T-SQL
--Step 1 - Create a credential for proxy

USE MASTER
GO
--Drop and create the demo credential if it is already existing
IF EXISTS (SELECT * FROM sys.credentials WHERE name = N'ProxyDemoCredentials')
BEGIN
DROP CREDENTIAL [ProxyDemoCredentials]
END
GO
CREATE CREDENTIAL [ProxyDemoCredentials]
WITH IDENTITY = N'YourDomain\ProxyDemoServiceAccount',
SECRET = N'ServiceAccountPasswordHere'
GO 
--End of Step 1

--Step 2 - Create a demo proxy account
USE msdb
GO
--Drop the demo proxy if it is already existing
IF EXISTS (SELECT * FROM msdb.dbo.sysproxies WHERE name = N'ProxyDemo')
BEGIN
  EXEC dbo.sp_delete_proxy
    @proxy_name = N'ProxyDemo'
END
GO
--Creating a demo proxy and assigning the demo credential to the same
EXEC msdb.dbo.sp_add_proxy
  @proxy_name = N'ProxyDemo',
  @credential_name=N'ProxyDemoCredentials',
  @enabled=1
GO
--You Need to Enable a Proxy before using it which can be done using below step.
EXEC msdb.dbo.sp_update_proxy
  @proxy_name = N'ProxyDemo',
  @enabled = 1 
GO 
--End of Step 2

--Step 3 Granting created demo proxy to SQL Agent subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
  @proxy_name=N'ProxyDemo',
  @subsystem_id=11 --subsystem 11 is for SSIS. You can get info using EXEC sp_enum_sqlagent_subsystems 
GO
--End of Step 3

--Step 4 Granting access of security principals(service account) to demo proxy created
USE msdb
GO
--Grant proxy account access to security principals
EXEC msdb.dbo.sp_grant_login_to_proxy
  @proxy_name=N'ProxyDemo'
  ,@login_name=N'YourDomain\ProxyDemoServiceAccount'
  --,@fixed_server_role=N'<fixed_server_role name>' if any roles
  --,@msdb_role=N'<msdb_role name>' if any MSDB roles
GO
--End of Step 4.

Now just use this proxy under Run AS in job step for using SSIS package.

Technorati Tags: proxy,credintials,Login,Proxies,SSIS

Get Information on All Jobs running on a SQL server Instance.

The SQL server Jobs which are running on a Instance are very Important. As a DBA you need to take care of these Jobs and have all information about the Job. Your will get information on Jobs is SysJobs but again not in the way you want. So just thought of pulling the Job information and below is the stored Procedure for the same.

This procedure give information on 

[job_id],
[Jobs],
[Category],
[Owner],
[Schedule Name],
[enabled],
[Frequecy],
[DateCreated],
[StartDate],
[NextRunDate],
[NextRunTime]

USE YourDatabaseName
GO
IF OBJECT_ID('dbo.spGetJobInfo') IS NOT NULL
DROP PROCEDURE dbo.spGetJobInfo
GO
CREATE PROCEDURE dbo.spGetJobInfo
AS
SET NOCOUNT ON
DECLARE @jobid VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
CREATE TABLE #Jobs_Schedule(
[schedule_id] [INT],
[name] [SYSNAME],
[enabled] [INT],
[freq_type] [INT],
[freq_interval] [INT],
[freq_subday_type] [INT],
[freq_subday_interval] [INT],
[freq_relative_interval] [INT],
[freq_recurrence_factor] [INT],
[active_start_date] [INT],
[active_end_date] [INT],
[active_start_time] [INT],
[active_end_time] [INT],
[date_created] [DATETIME],
[desc] VARCHAR(1000),
[next_run_date] [INT],
[next_run_time] [INT],
[schedule_uid]    VARCHAR(1000),
[job_count] [INT]
) ON [PRIMARY]
DECLARE c1 CURSOR FOR SELECT job_id FROM msdb..sysjobs
OPEN c1
FETCH NEXT FROM c1 INTO @jobid
WHILE @@fetch_status=0
BEGIN
INSERT #Jobs_Schedule EXEC ('msdb..sp_help_jobschedule @job_id='''+ @jobid+''',@include_description=1')
FETCH NEXT FROM c1 INTO @jobid
END
CLOSE c1
DEALLOCATE c1

CREATE TABLE #TempJoin
(
job_id UNIQUEIDENTIFIER,
Jobs VARCHAR(250),
Category VARCHAR(250),
OwnerName  VARCHAR(100),
ScheduleName  VARCHAR(250),
ISenabled  CHAR(4),
Frequency VARCHAR(250),
Date_Created  DATETIME,
Active_Start_Date INT,
Next_Run_Date  INT,
Next_Run_Time INT
)
INSERT INTO #TempJoin
(
job_id,
Jobs,
Category,
OwnerName,
ScheduleName,
ISenabled,
Frequency,
Date_Created,
Active_Start_Date,
Next_Run_Date,
Next_Run_Time
)
(
SELECT 
a.[job_id],
a.[Jobs],
a.[Category],
l.[name] AS [Owner],
a.[Schedule Name],
a.[enabled],
temp.[desc] AS [Frequecy],
temp.date_created AS [DateCreated],
temp.active_start_date AS [StartDate],
temp.next_run_date AS [NextRunDate],
temp.next_run_time AS [NextRunTime]
FROM #Jobs_Schedule AS temp 
RIGHT OUTER JOIN 
(SELECT
j.job_id,
j.[name] AS [Jobs], 
j.owner_sid, 
c.[name] AS [Category], 
js.schedule_id, 
s.[name] AS [Schedule Name], 
CASE j.enabled WHEN 1 THEN 'YES' ELSE 'NO' END AS [enabled] 
FROM msdb..syscategories AS c 
RIGHT OUTER JOIN 
msdb..sysjobs AS j ON c.category_id = j.category_id
LEFT OUTER JOIN 
msdb..sysjobschedules AS js ON j.job_id = js.job_id
LEFT OUTER JOIN 
msdb..sysschedules AS s ON js.schedule_id = s.schedule_id)
AS a ON temp.schedule_id = a.schedule_id 
LEFT OUTER JOIN 
sys.server_principals AS l ON a.owner_sid = l.sid)

CREATE TABLE #TempJoin1
(
Name VARCHAR(250),
job_id UNIQUEIDENTIFIER,
LastRunStatus VARCHAR(25),
LastRunDate INT,
LastRunTIme INT,
)
INSERT INTO #TempJoin1
(
Name,
job_id,
LastRunStatus,
LastRunDate,
LastRunTIme
)
(
SELECT  
j.[name] AS [JobName],
j.[job_id],  
run_status = CASE h.run_status  
WHEN 0 THEN 'Failed' 
WHEN 1 THEN 'Succeeded' 
WHEN 2 THEN 'Retry' 
WHEN 3 THEN 'Canceled' 
WHEN 4 THEN 'In progress' 
END, 
h.run_date AS LastRunDate,   
h.run_time AS LastRunTime 
FROM msdb..sysjobhistory h  
INNER JOIN
msdb..sysjobs j ON h.job_id = j.job_id  
WHERE h.instance_id IN  
(SELECT MAX(h.instance_id)  
FROM msdb..sysjobhistory h GROUP BY (h.job_id))
)

SELECT 
t1.Jobs,
t1.OwnerName,
t1.Category,
t1.Date_Created,
t1.Active_Start_Date,
t1.ISenabled,
t2.LastRunDate,
t2.LastRunTIme,
t2.LastRunStatus
FROM #TempJoin t1
INNER JOIN #TempJoin1 t2
ON t1.job_id = t2.job_id

DROP Table #Jobs_Schedule
DROP Table #TempJoin
DROP Table #TempJoin1
GO

Technorati Tags: Jobs,Job,Scheduled Job,SQL server Jobs,SQL server agent

T-SQL to Get Default Audit Level of SQL server Instance

I have come across situations where you need to go and look for Default Audit Level for Installed Instances or servers. You can do this by getting info in server instances. Now what if you have around 100 server and instance. For this I could find a easy way and that’s a T-SQL function to get the default Audit Level for Instance.

Function:

USE YourDatabaseName
GO
CREATE FUNCTION dbo.fn_get_default_path()
RETURNS int
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path int, @value_name nvarchar(20);

    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');

    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';

    SET @value_name = N'AuditLevel'
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;   
    RETURN @path;
END
GO

Now to get the value run the below script:

DECLARE @Temp varchar(max),@Temp1 varchar(max)
SET @Temp = dbo.fn_get_default_path()
If @Temp = 0
Begin
Set @Temp1 = 'NONE'
END
IF @Temp = 1
BEGIN
SET @Temp1 = 'Successful Logins Only'
END
If @Temp = 2
BEGIN
SET @Temp1 = 'Failed Logins Only'
END
IF @Temp = 3
BEGIN
SET @Temp1 = 'Both Successful and Failed Logins'
END
SELECT @Temp1
GO

Technorati Tags: T-SQL,Audit Level,Default Audit Level

Get Free Disk Space for SQL server 2000

As a DBA you always need to monitor various metrics of a Database server. Disk space is one such metric you gotta watch out for. Monitoring Disk space for SQL server 2000 is bit difficult through script. Just happened to write a Script for SQL server 2000 which gives you free disk space in GB for all existing drives on database server.

SET NOCOUNT ON

DECLARE @TEMP AS VARCHAR(4000)

DECLARE @MBfree int

DECLARE @CMD1 varchar(1000)

SET @MBfree = 0

SET @CMD1 = ''

CREATE TABLE #tbl_xp_fixeddrives

(Drive varchar(2) NOT NULL,

[FreeSpace] int NOT NULL)

INSERT INTO #tbl_xp_fixeddrives(Drive, [FreeSpace])

EXEC master.dbo.xp_fixeddrives

SELECT @TEMP ='

CREATE FUNCTION dbo.GetDriveSize (@driveletter CHAR(1))

 RETURNS NUMERIC(20)

 BEGIN

  DECLARE  @rs INTEGER, @fso INTEGER, @getdrive VARCHAR(13), @drv INTEGER,

@drivesize VARCHAR(20)

  SET @getdrive = ''GetDrive("'' + @driveletter + ''")''

  EXEC @rs = sp_OACreate ''Scripting.FileSystemObject'', @fso OUTPUT

  IF @rs = 0

   EXEC @rs = sp_OAMethod @fso, @getdrive, @drv OUTPUT

  IF @rs = 0

   EXEC @rs = sp_OAGetProperty @drv,''TotalSize'', @drivesize OUTPUT

  IF @rs<> 0

   SET @drivesize = NULL

  EXEC sp_OADestroy @drv

  EXEC sp_OADestroy @fso

  RETURN @drivesize

 END'

EXEC(@TEMP)

SELECT drive+':'+' '+

CAST(CAST((FreeSpace/(((dbo.GetDriveSize(drive))/1024)/1024))*100.0 as int) as VARCHAR(4000))+'%'

from #tbl_xp_fixeddrives

DROP TABLE #tbl_xp_fixeddrives

SELECT @TEMP = 'USE '

SELECT @TEMP = @TEMP+'master '+'DROP FUNCTION dbo.GetDriveSize'

EXEC (@TEMP)

SET NOCOUNT OFF

GO