Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

29Jun/100
SQL Server CPU Utilization

Some helpful SQL stats from this article.

-- From the SQL Server Performance Dashboard (sys.dm_os_ring_buffers)
-- Query CPU utilization for each four minute intervals
declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
select record_id,
      dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
      SQLProcessUtilization,
      SystemIdle,
      100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
      select
            record.value('(./Record/@id)[1]', 'int') as record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
            timestamp
      from (
            select timestamp, convert(xml, record) as record
            from sys.dm_os_ring_buffers
            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            and record like '%%') as x
      ) as y
order by record_id desc

-- 4:12 (two procs CPU=8,9,9,9)
DECLARE @count int
SELECT @count =100000000
WHILE @count > 0
	BEGIN
	SELECT @count=@count-1
	--PRINT @count
END
10Jun/090
How to Turn on xp_cmdshell for SQL 2005

The xp_cmdshell function can be very helpful for running commands on an SQL server. However, turning on the function comes with risks. I'll discuss those risks later... If you try to run xp_cmdshell on a server where it is not turned on, you may receive this error message:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

To turn xp_cmdshell on, log in to the SQL server as sa, and run this command:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
8Jun/090
Script to Add a New SQL Differential Backup Job With Daily Incremental Zip Archives

This script builds on the differential backup script, adding a zipped incremental backup. This script will create a zip file for each day of the week. I've used this script when I have a simple database type but need the flexibility of multiple daily differential backups. You will have to turn on xp_cmdshell to run this script.

-- This script creates a differential backup job to backup the DBName database as DBName_Differential.bak (daily at 5:00 PM). This script also creates daily incremental zip archives.

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Differential.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [PBSA] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak'' WITH  DIFFERENTIAL ,  DESCRIPTION = N''DBName_Differential.bak'', NOFORMAT, INIT,  NAME = N''DBName_Differential.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'xp_cmdshell ''C:\PROGRA~1\WinZip\winzip32.exe -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.bak - %date:~0,3%.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak"''', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Differential.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=193000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
7Jun/090
Script to Add a New SQL Differential Backup Job

I know you've been waiting for this... This script will add a differential backup job to Microsoft SQL Server 2005. Use this script in conjunction with a full backup.

-- This script creates a differential backup job to backup the DBName database as DBName_Differential.bak (daily at 5:00 PM)

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Differential.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [DBName] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak'' WITH DIFFERENTIAL, DESCRIPTION = N''DBName_Differential.bak'', NOFORMAT, INIT,  NAME = N''DBName_Differential.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'CmdExec', 
	@command=N'"C:\Program Files\WinZip\winzip32.exe" -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak"', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Differential.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=170000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
6Jun/090
Script to Add a New SQL Backup Job

This script is for the times when I like to automate the task of adding database backup jobs to Microsoft SQL Servers. I'll sometimes add a dozen jobs at once. This script saves time and keeps things consistent. The script adds two tasks and a schedule:

1. Database backup
2. Zip backup file

Using WinZip (you'll need a license) to zip the backup file makes it easier to move the file to an offsite location. This script also makes it easy to add or remove steps. Enjoy...

-- This script creates a backup job to backup the DBName database as DBName_Full.bak (daily at 10:00 PM)

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Full.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Full.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Full.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [DBName] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Full.bak'' WITH  DESCRIPTION = N''DBName_Full.bak'', NOFORMAT, INIT,  NAME = N''DBName_Full.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Full.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'CmdExec', 
	@command=N'"C:\Program Files\WinZip\winzip32.exe" -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Full.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Full.bak"', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Full.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Full.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=220000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO