DBA DAILY RUNNING SCRIPTS

DBA DAILY RUNNING SCRIPTS

Checking free space in all drives

EXEC master..xp_fixeddrives

To Run a failed job by a single T-SQL statement

USE msdb

EXEC msdb.dbo.sp_start_job 'BACKUPTEST'

Example:

USE msdb

EXEC sp_stop_job @job_name = 'My_Job'

waitfor delay '00:00:05'

EXEC sp_start_job @job_name = 'My_Job'

STOP a currently executing job - SQL Server Agent Stored Procedure

EXEC msdb.dbo.sp_stop_job N'BACKUPTEST' ;

Check running status of a job

SQL Server 2008 T-SQL - Running = 1 means currently executing

DECLARE @job_id uniqueidentifier = '4C004358-62E0-4933-9268-90F54EF36AA9'

EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id

If current_execution_status=1 then RUNNING

EXEC msdb.dbo.sp_help_job  @job_id='0D823348-2DDC-4816-93EA-307D6E736437'

Get job INFO on application job - execute system stored procedure

EXEC msdb.dbo.sp_help_job

    @job_name = N'BACKUPTEST',

    @job_aspect = N'ALL' ;

SQL Server Agent disable job

EXEC msdb.dbo.sp_update_job

    @job_name = N'Back Up Database - AdventureWorks2008',

    @enabled = 0 ;

To find out full hard disk details with space utilized (In MB) and percentage, space utilized in MB and % for a system.

EXEC xp_cmdshell 'Dir "Driver"'

SQL Server Performance issues - From where to start in crises 

This Query will gives you the detailed information about waiting tasks

select a.spid, b.name, a.cmd, a.memusage, a.cpu, a.program_name, a.nt_username, a.loginame, a.hostname, a.status

from master.dbo.sysprocesses a,master.dbo.sysdatabases b

where a.dbid=b.dbid and blocked<>0

This Query will gives you the which database is using the most CPU cycles. In case you are hosting more than 1 databases on your server and you wanted to
know, which database is most resources

§  More Used DB:

select db_name(dbid), count(dbid) as processes ,sum(cpu) as usage from

sysprocesses group by dbid order by usage desc

This Query will gives you the which database is causing the most Blocking.

§  More Blocked DB

select db_name(dbid), count(dbid) as processes ,sum(cpu) as usage from

sysprocesses where blocked != 0 group by dbid order by usage desc

Gives the process that causes deadlock

select a.spid,b.name,a.cmd,a.memusage,a.cpu,a.program_name,a.nt_username,a.loginame,a.hostname,a.status

from master.dbo.sysprocesses a, master.dbo.sysdatabases b

where a.dbid=b.dbid and spid in (select blocked from master.dbo.sysprocesses where blocked<>0)

go

Processes running for more than 10 min

Select sp.spid, sd.name, sp.waittime/(1000 * 60) as wait_time_mins ,

sp.cpu, sp.memusage, sp.login_time, sp.cmd, sp.program_name,

sp.nt_username, sp.loginame, sp.hostname, sp.status, sp.waitresource, sp.blocked

from sysprocesses sp, sysdatabases sd

where sp.dbid = sd.dbid and sp.status = 'runnable' and

sp.waittime/(1000 * 60) >= 10

Gives the waiting tasks

select a.spid, b.name,a.cmd, a.memusage, a.cpu,a.program_name, a.nt_username, a.loginame, a.hostname, a.status

from master.dbo.sysprocesses a,master.dbo.sysdatabases b

where a.dbid=b.dbid and blocked <> 0

go

Processes with Runnable status

Select sp.spid, sd.name, sp.cmd, sp.memusage, sp.cpu, sp.login_time, sp.program_name, sp.nt_username, sp.loginame,

sp.hostname, sp.status, sp.waitresource,

sp.waittime from sysprocesses sp, sysdatabases sd

where sp.dbid = sd.dbid and sp.status = 'runnable'

Processes running more than 30 min (All databases)

Select sp.spid, sd.name, sp.cmd, sp.memusage, sp.cpu, sp.login_time,sp.program_name,sp.nt_username, sp.loginame,sp.hostname, sp.status, sp.waitresource,sp.waittime from sysprocesses sp, sysdatabases sd

where sp.dbid = sd.dbid and datediff(minute, sp.login_time, getdate()) >= 30and sp.status = 'runnable'

go

Processes running more than 30 min

Select sp.spid, sd.name, sp.cmd, sp.memusage, sp.cpu, sp.login_time,sp.program_name,sp.nt_username, sp.loginame,sp.hostname, sp.status, sp.waitresource,sp.waittime from sysprocesses sp, sysdatabases sd

where sp.dbid = sd.dbid and datediff(minute, sp.login_time, getdate()) >= 30and sp.status = 'runnable'

SQL Server Performance Counters

select * from sysperfinfo

where object_name in

('SQLServer:General Statistics','SQLServer:Locks','SQLServer:Databases','SQLServer:Access Methods','SQLServer:SQL Statistics','SQLServer:Memory Manager')

and

counter_name in ('User Connections','Lock Requests/sec','Lock Timeouts/sec','Number of Deadlocks/sec','Average Wait Time(ms)','Active Transactions','Transactions/sec','Full Scans/sec','Page Splits/sec','SQL Compilations/sec','SQL Re-Compilations/sec','Lock Memory (KB)')

Notes:

There might be a possibility that this query will give you error, this is because in this page single quote (‘) is displayed as (‘), which is a different character, please this (‘) in case required.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘‘’.

These queries are mostly applies to SQL Server 2000 but all will works with 2005 and 2008.

In SQL Server 2005 and 2008, we can use DMV to get more info.