Date: Sat, 29 Oct 2022 05:13:02 +0300
Message-Id: <360806vHZqwV$AHV6UHMC$QDwwkxcb$@studio75.com.mx>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0077_LOSLROHO.8LFCKYLO"
X-Mailer: Microsoft Outlook 16.0
Thread-Index: AdjrPAwKUbC3EQNNEM2KIfyOV1ahXA==
Content-Language: en-us
This is a multipart message in MIME format.
------=_NextPart_000_0077_LOSLROHO.8LFCKYLO
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
patrickashoursqlblogger https://search.yahoo.com/search?p=patrick_ashour.sqlblogger@blogger.com Patrick
------=_NextPart_000_0077_LOSLROHO.8LFCKYLO
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40"><head><META HTTP-EQUIV=3D"Content-Type" CONTENT=
=3D"text/html; charset=3Dus-ascii"><meta name=3DGenerator content=3D"Micros=
oft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:2.0cm 42.5pt 2.0cm 3.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body link=3D"#0563C1" vlink=3D"#9=
54F72"><div class=3DWordSection1><p class=3DMsoNormal><span style=3D'font-s=
ize:21.1pt;font-family:Tahoma'>patrickashoursqlblogger<o:p></o:p></span></p=
><p class=3DMsoNormal><span style=3D'font-size:14.3pt;font-family:Tahoma'><=
o:p> </o:p></span></p><p class=3DMsoNormal><span style=3D'font-size:14=
.3pt;font-family:Tahoma'><o:p> </o:p></span></p><p class=3DMsoNormal><=
span style=3D'font-size:10.2pt;font-family:Tahoma'><a href=3D"https://bit.l=
y/3DdkhL3">https://search.yahoo.com/search?p=3Dpatrick_ashour.sqlblogger@bl=
ogger.com</a><o:p></o:p></span></p><p class=3DMsoNormal><span style=3D'font=
-size:19.1pt;font-family:Tahoma'><o:p> </o:p></span></p><p class=3DMso=
Normal><span style=3D'font-size:19.1pt;font-family:Tahoma'><o:p> </o:p=
></span></p><p class=3DMsoNormal><span style=3D'font-size:10.3pt;font-famil=
y:Arial'>Patrick<o:p></o:p></span></p></div></body></html>
------=_NextPart_000_0077_LOSLROHO.8LFCKYLO--
Friday, October 28, 2022
Thursday, June 16, 2022
Good afternoon, patrick ashour sqlblogger
Patrick ashour sqlblogger
https://www.google.com/search?q=patrick_ashour.sqlblogger@blogger.com
Patrick
Patrick
Thursday, July 18, 2019
Wednesday, October 05, 2016
Friday, January 29, 2016
Tuesday, October 14, 2014
SQL - find problems - Standard solutions to random problems
Specialized Performance Troubleshooting (Part 1: How to troubleshoot Forwarded Records)
http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/04/specialized-performance-troubleshooting-part-1-how-to-troubleshoot-forwarded-records.aspx
Specialized Performance Troubleshooting (Part 2: How to troubleshoot Memory problems in SQL Server)
http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/11/specialized-performance-troubleshooting-part-2-how-to-troubleshoot-memory-problems-in-sql-server.aspxSpecialized Performance Troubleshooting (Part 3: How to identify storage issues at a SQL Server box)
http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/18/specialized-performance-troubleshooting-part-3-how-to-identify-storage-issues-at-a-sql-server-box.aspx
How to troubleshoot SQL Server performance issues with simple tools (Part 1: How to collect a detailed Perfmon trace)
http://blogs.msdn.com/b/john_daskalakis/archive/2013/10/07/how-to-troubleshoot-sql-server-performance-issues-with-simple-tools-part-1-how-to-collect-a-detailed-perfmon-trace.aspx
http://blogs.msdn.com/b/john_daskalakis/
Monday, September 08, 2014
proc sp_alert_failed_jobs
/*
exec sp_alert_failed_jobs @pastdays = -3 , @exludejob ='''exportbill_dts'',''daily_update_erptables'''
*/
alter proc sp_alert_failed_jobs
(
@pastdays int = -1,
@exludejob varchar(1000) =''
)
as
begin
--declare @pastdays int = -1
--select @exludejob
if(object_id('msdb..__tmp_failed_jobs')>1)
drop table __tmp_failed_jobs
create table __tmp_failed_jobs (name varchar(1000), run_date int, run_time int, step_id int, step_name varchar(1000), message ntext)
declare @sql nvarchar(max)
set @sql = 'select j.name,h.run_date,h.run_time,h.step_id, h.step_name, h.message
From msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
where h.run_status = 0
and h.run_date >= convert(int, CONVERT(varchar, dateadd(dd,'+convert(varchar,@pastdays)+',GETDATE()),112))
and j.name not in ('+@exludejob+')
order by h.run_date desc, h.step_id asc'
--print @sql
insert into __tmp_failed_jobs exec sp_executesql @sql
--select * From __tmp_failed_jobs
if(select COUNT(*) from __tmp_failed_jobs) >0
begin
declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
set @subject = 'Failed Jobs - SQL ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
set @recipientslist = 'patrickalexander@hotmail.com;ACheckDB@acheckamerica.com;kcinty@acheckamerica.com;qkong@acheckamerica.com'
DECLARE @tableHTML NVARCHAR(MAX) , @subjectstring nvarchar(200);
SET @tableHTML =
N'
N'' +
' ;
--print @tableHTML
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'default',
@recipients =@recipientslist,
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subject;
end
end
exec sp_alert_failed_jobs @pastdays = -3 , @exludejob ='''exportbill_dts'',''daily_update_erptables'''
*/
alter proc sp_alert_failed_jobs
(
@pastdays int = -1,
@exludejob varchar(1000) =''
)
as
begin
--declare @pastdays int = -1
--select @exludejob
if(object_id('msdb..__tmp_failed_jobs')>1)
drop table __tmp_failed_jobs
create table __tmp_failed_jobs (name varchar(1000), run_date int, run_time int, step_id int, step_name varchar(1000), message ntext)
declare @sql nvarchar(max)
set @sql = 'select j.name,h.run_date,h.run_time,h.step_id, h.step_name, h.message
From msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
where h.run_status = 0
and h.run_date >= convert(int, CONVERT(varchar, dateadd(dd,'+convert(varchar,@pastdays)+',GETDATE()),112))
and j.name not in ('+@exludejob+')
order by h.run_date desc, h.step_id asc'
--print @sql
insert into __tmp_failed_jobs exec sp_executesql @sql
--select * From __tmp_failed_jobs
if(select COUNT(*) from __tmp_failed_jobs) >0
begin
declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
set @subject = 'Failed Jobs - SQL ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
set @recipientslist = 'patrickalexander@hotmail.com;ACheckDB@acheckamerica.com;kcinty@acheckamerica.com;qkong@acheckamerica.com'
DECLARE @tableHTML NVARCHAR(MAX) , @subjectstring nvarchar(200);
SET @tableHTML =
N'
Failed jobs on '+@@SERVERNAME+'
' +N'
| Job Name | Date | Time | ' +StepID | ' +StepName | ' +Message | <
|---|
--print @tableHTML
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'default',
@recipients =@recipientslist,
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subject;
end
end
Tuesday, November 26, 2013
Job History Collection
--TABLE
USE [DBACollection]
GO
/****** Object: Table [dbo].[JobHistoryCollection] Script Date: 11/26/2013 10:30:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JobHistoryCollection](
[ServerName] [nvarchar](128) NULL,
[JobName] [sysname] NULL,
[Enabled] [tinyint] NULL,
[CurrentStatus] [varchar](9) NULL,
[CurrentStepNbr] [int] NULL,
[LastRunTime] [datetime] NULL,
[LastRunOutcome] [varchar](11) NULL,
[LastRunDuration] [int] NULL,
[CollectionTime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[JobHistoryCollection] ADD CONSTRAINT [DF_JobHistoryCollection_CollectionTime] DEFAULT (getdate()) FOR [CollectionTime]
GO
--PROC
USE [DBACollection]
GO
/*
exec pr_JobHistoryCollection
select * from JobHistoryCollection order by collectiontime desc
select jobname, COUNT(*) as cc, MAX(lastrunduration) mx,Min(lastrunduration) mn,Avg(lastrunduration) av
from JobHistoryCollection
where collectiontime < GETDATE() and collectiontime > DATEADD(dd, -7, GETDATE())
group by jobname
order by mx desc
*/
alter proc pr_JobHistoryCollection
as
begin
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
if(object_id('tempdb..#tmp')>1)
drop table #tmp
create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);
insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLA4SW1';
UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);
--select * from #tmp
insert into JobHistoryCollection
(ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration)
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from
(
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
h.run_duration
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where x.running != 1
) a
except
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from JobHistoryCollection
end
--JOB
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'AUDIT - JobHistoryCollection',
@enabled=1,
@notify_level_eventlog=0,
@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'AUDIT - JobHistoryCollection', @server_name = @@servername
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AUDIT - JobHistoryCollection', @step_name=N'exec sp',
@step_id=1,
@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'exec sp_JobHistoryCollection',
@database_name=N'DBACollection',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AUDIT - JobHistoryCollection',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@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'AUDIT - JobHistoryCollection', @name=N'Every 15 min',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20131126,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
USE [DBACollection]
GO
/****** Object: Table [dbo].[JobHistoryCollection] Script Date: 11/26/2013 10:30:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JobHistoryCollection](
[ServerName] [nvarchar](128) NULL,
[JobName] [sysname] NULL,
[Enabled] [tinyint] NULL,
[CurrentStatus] [varchar](9) NULL,
[CurrentStepNbr] [int] NULL,
[LastRunTime] [datetime] NULL,
[LastRunOutcome] [varchar](11) NULL,
[LastRunDuration] [int] NULL,
[CollectionTime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[JobHistoryCollection] ADD CONSTRAINT [DF_JobHistoryCollection_CollectionTime] DEFAULT (getdate()) FOR [CollectionTime]
GO
--PROC
USE [DBACollection]
GO
/*
exec pr_JobHistoryCollection
select * from JobHistoryCollection order by collectiontime desc
select jobname, COUNT(*) as cc, MAX(lastrunduration) mx,Min(lastrunduration) mn,Avg(lastrunduration) av
from JobHistoryCollection
where collectiontime < GETDATE() and collectiontime > DATEADD(dd, -7, GETDATE())
group by jobname
order by mx desc
*/
alter proc pr_JobHistoryCollection
as
begin
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
if(object_id('tempdb..#tmp')>1)
drop table #tmp
create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);
insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLA4SW1';
UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);
--select * from #tmp
insert into JobHistoryCollection
(ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration)
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from
(
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
h.run_duration
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where x.running != 1
) a
except
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from JobHistoryCollection
end
--JOB
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'AUDIT - JobHistoryCollection',
@enabled=1,
@notify_level_eventlog=0,
@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'AUDIT - JobHistoryCollection', @server_name = @@servername
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AUDIT - JobHistoryCollection', @step_name=N'exec sp',
@step_id=1,
@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'exec sp_JobHistoryCollection',
@database_name=N'DBACollection',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AUDIT - JobHistoryCollection',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@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'AUDIT - JobHistoryCollection', @name=N'Every 15 min',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20131126,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
Wednesday, September 25, 2013
Retrieve job status
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_job_status] Script Date: 09/25/2013 11:49:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec [sp_job_status] --EVERYTHING
exec [sp_job_status] @jobstatus = 0 --FAIL
exec [sp_job_status] @jobname = 'CSOD Service Queue Collector'
exec [sp_job_status] @jobname = 'CSOD Service Queue Collector' , @jobstatus = 1 --Success
*/
ALTER procedure [dbo].[sp_job_status]
(
@isrunning bit = 0 ,
@jobname nvarchar(255) = null ,
@jobstatus int = null
)
as
begin
/*
@jobstatus
WHEN NULL THEN 'ALL'
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
*/
--set @jobname = 'CSOD Service Queue Collector'
--set @isrunning = 0 -- 0:all 1:running
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]'))
DROP TABLE [tempdb].[dbo].[Temp1]
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
where (@jobstatus is null or h.run_status = @jobstatus)
and (@jobname is null or j.name = @jobname )
--x.running = 1 -- to see running jobs
end
GO
/****** Object: StoredProcedure [dbo].[sp_job_status] Script Date: 09/25/2013 11:49:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec [sp_job_status] --EVERYTHING
exec [sp_job_status] @jobstatus = 0 --FAIL
exec [sp_job_status] @jobname = 'CSOD Service Queue Collector'
exec [sp_job_status] @jobname = 'CSOD Service Queue Collector' , @jobstatus = 1 --Success
*/
ALTER procedure [dbo].[sp_job_status]
(
@isrunning bit = 0 ,
@jobname nvarchar(255) = null ,
@jobstatus int = null
)
as
begin
/*
@jobstatus
WHEN NULL THEN 'ALL'
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
*/
--set @jobname = 'CSOD Service Queue Collector'
--set @isrunning = 0 -- 0:all 1:running
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]'))
DROP TABLE [tempdb].[dbo].[Temp1]
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
where (@jobstatus is null or h.run_status = @jobstatus)
and (@jobname is null or j.name = @jobname )
--x.running = 1 -- to see running jobs
end
Tuesday, August 13, 2013
T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives
T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives
http://www.sanssql.com/2011/02/t-sql-queries-to-find-sql-server.html
Thursday, July 18, 2013
Friday, May 10, 2013
Wednesday, May 08, 2013
Script User and Role Object Permissions in SQL Server
DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'CSODMGMT\SQLREOnly'
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
/*
credit : http://www.sql-server-performance.com/2002/object-permission-scripts/2/
*/
SET @DatabaseUserName = 'CSODMGMT\SQLREOnly'
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
/*
credit : http://www.sql-server-performance.com/2002/object-permission-scripts/2/
*/
Wednesday, April 24, 2013
sp_kill_user
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_kill_user] Script Date: 04/24/2013 09:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_kill_user 'dw_user'
*/
ALTER proc [dbo].[sp_kill_user]
(
@username nvarchar(1000) = 'dw_user'
)
as
begin
SET NOCOUNT ON
DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE loginame = @username
AND spid != @@SPID
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE loginame = @username
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
END
GO
/****** Object: StoredProcedure [dbo].[sp_kill_user] Script Date: 04/24/2013 09:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_kill_user 'dw_user'
*/
ALTER proc [dbo].[sp_kill_user]
(
@username nvarchar(1000) = 'dw_user'
)
as
begin
SET NOCOUNT ON
DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE loginame = @username
AND spid != @@SPID
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE loginame = @username
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
END
Monday, April 15, 2013
Thursday, April 11, 2013
Wednesday, April 03, 2013
Table Partitioning on a varchar field
exec sp_who3
exec sp_whoisactive
drop partition scheme CoreCollectionPartitionScheme
drop PARTITION FUNCTION [SwimlaneRangePartitionFunction]
CREATE PARTITION FUNCTION [SwimlaneRangePartitionFunction] (nvarchar(100))
AS RANGE RIGHT FOR VALUES ('LA4PRDCLT101', 'LA4PRDCLT201', 'LA4PRDCLT301',
'LA4PRDCLT401', 'LD4PRDCLT101', 'LD4PRDCLT102', 'LD4PRDCLT201');
CREATE PARTITION SCHEME CoreCollectionPartitionScheme
AS PARTITION SwimlaneRangePartitionFunction
TO ([primary],sl1,sl2,sl3,sl4,sl5,sl51,sl6);
GO
drop table [user_demographics_partition_by_swimlane]
go
CREATE TABLE [dbo].[user_demographics_partition_by_swimlane](
[user_id] [int] NULL,
[swimlane] [nvarchar](100) NULL,
[portal] [nvarchar](200) NULL,
[status_id] [int] NULL,
[user_type_id] [int] NULL,
[gender] [varchar](100) NULL,
[user_country] [varchar](100) NULL,
[user_state] [nvarchar](50) NULL,
[user_zipcode] [nvarchar](50) NULL,
[birth_dt] [datetime] NULL,
[hire_dt] [datetime] NULL,
[term_dt] [datetime] NULL,
[timezone] [int] NULL,
[date_stamp] [datetime] NULL,
[user_country_profile_base] [varchar](100) NULL,
[user_state_profile_base] [nvarchar](50) NULL,
[user_zipcode_profile_base] [nvarchar](50) NULL
) ON CoreCollectionPartitionScheme ([swimlane])
GO
exec sp_whoisactive
drop partition scheme CoreCollectionPartitionScheme
drop PARTITION FUNCTION [SwimlaneRangePartitionFunction]
CREATE PARTITION FUNCTION [SwimlaneRangePartitionFunction] (nvarchar(100))
AS RANGE RIGHT FOR VALUES ('LA4PRDCLT101', 'LA4PRDCLT201', 'LA4PRDCLT301',
'LA4PRDCLT401', 'LD4PRDCLT101', 'LD4PRDCLT102', 'LD4PRDCLT201');
CREATE PARTITION SCHEME CoreCollectionPartitionScheme
AS PARTITION SwimlaneRangePartitionFunction
TO ([primary],sl1,sl2,sl3,sl4,sl5,sl51,sl6);
GO
drop table [user_demographics_partition_by_swimlane]
go
CREATE TABLE [dbo].[user_demographics_partition_by_swimlane](
[user_id] [int] NULL,
[swimlane] [nvarchar](100) NULL,
[portal] [nvarchar](200) NULL,
[status_id] [int] NULL,
[user_type_id] [int] NULL,
[gender] [varchar](100) NULL,
[user_country] [varchar](100) NULL,
[user_state] [nvarchar](50) NULL,
[user_zipcode] [nvarchar](50) NULL,
[birth_dt] [datetime] NULL,
[hire_dt] [datetime] NULL,
[term_dt] [datetime] NULL,
[timezone] [int] NULL,
[date_stamp] [datetime] NULL,
[user_country_profile_base] [varchar](100) NULL,
[user_state_profile_base] [nvarchar](50) NULL,
[user_zipcode_profile_base] [nvarchar](50) NULL
) ON CoreCollectionPartitionScheme ([swimlane])
GO
Thursday, January 31, 2013
Database last restore status report
use master
go
drop procedure sp_db_restore_status_report
go
create procedure sp_db_restore_status_report
as
begin
/*
select *
from master.sys.databases a WITH (nolock)
where a.name like 'ces_%'
order by a.name
SELECT top 1 * FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = 'ces_aaanational')
ORDER BY RESTORE_DATE DESC
*/
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
Create table #tmp (db nvarchar(1000), restore_Date datetime)
insert into #tmp
select a.name ,
(
SELECT top 1 restore_date FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = a.name)
ORDER BY RESTORE_DATE DESC
) as last_restore_date
from master.sys.databases a WITH (nolock)
where a.name like 'ces_%'
--select * From #tmp
if(OBJECT_ID('tempdb..#tmp_total')>1)
drop table #tmp_total
select
(
select COUNT(*)
From #tmp
) as total_dbs,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-2,getdate())
) as restored_last_2_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-8,getdate())
) as restored_last_8_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-24,getdate())
) as restored_last_24_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') < DATEADD(hh,-24,getdate())
) as restored_older_24_hours
into #tmp_total
--select * From #tmp_total
DECLARE @tableHTML NVARCHAR(MAX) , @subjectstring nvarchar(200);
SET @tableHTML =
N'
N'' +
' ;
print @tableHTML
set @subjectstring = 'Database Restore Report from '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients ='palexander@csod.com;ayegudkin@csod.com;jferris@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
end
go
drop procedure sp_db_restore_status_report
go
create procedure sp_db_restore_status_report
as
begin
/*
select *
from master.sys.databases a WITH (nolock)
where a.name like 'ces_%'
order by a.name
SELECT top 1 * FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = 'ces_aaanational')
ORDER BY RESTORE_DATE DESC
*/
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
Create table #tmp (db nvarchar(1000), restore_Date datetime)
insert into #tmp
select a.name ,
(
SELECT top 1 restore_date FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = a.name)
ORDER BY RESTORE_DATE DESC
) as last_restore_date
from master.sys.databases a WITH (nolock)
where a.name like 'ces_%'
--select * From #tmp
if(OBJECT_ID('tempdb..#tmp_total')>1)
drop table #tmp_total
select
(
select COUNT(*)
From #tmp
) as total_dbs,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-2,getdate())
) as restored_last_2_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-8,getdate())
) as restored_last_8_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-24,getdate())
) as restored_last_24_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') < DATEADD(hh,-24,getdate())
) as restored_older_24_hours
into #tmp_total
--select * From #tmp_total
DECLARE @tableHTML NVARCHAR(MAX) , @subjectstring nvarchar(200);
SET @tableHTML =
N'
Database Restore Report from '+@@SERVERNAME+'
' +N'
| Total Databases | Restored in last 2 hours | Restored in last 8 hours | ' +Restored in last 24 hours | ' +Restored over 24 hours |
|---|
print @tableHTML
set @subjectstring = 'Database Restore Report from '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients ='palexander@csod.com;ayegudkin@csod.com;jferris@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
end
Wednesday, January 09, 2013
sp_delete_files
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_delete_files] Script Date: 01/09/2013 07:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_delete_files @folder = 'B:\SQL_Performance\TraceCollection\TraceFiles\'
,@ext = 'trc'
,@minutesgoback = -60
*/
ALTER proc [dbo].[sp_delete_files]
(
@folder varchar(100)
,@ext varchar(10)
,@minutesgoback int = -6000
)
as
begin
set nocount on;
declare @run int = 1 , @reccount int = 0
declare @sql nvarchar(4000), @filename varchar(100), @tablename varchar(100), @param_definition nvarchar(4000), @folderlogs varchar(100)
--set @folderlogs = @folder +'\logs\'
set @folderlogs = @folder
------------------------------------------------
set @sql = 'DIR ' + '"' + @folderlogs + '\*.'+@ext+'"'
--print @sql
if(OBJECT_ID('tempdb..#tmp_tbl')>0)
drop table #tmp_tbl
CREATE TABLE #tmp_tbl (Name varchar(400), Work int IDENTITY(1,1))
INSERT #tmp_tbl EXECUTE master.dbo.xp_cmdshell @sql
DELETE #tmp_tbl WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.'
--select convert(datetime,SUBSTRING(Name,1,20)),SUBSTRING(Name,40,100),dateadd(MINUTE,@minutesgoback,GETDATE()), *
--From #tmp_tbl
--where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())
---------------------------------------------------
declare tmp_cur cursor for
SELECT SUBSTRING(Name,40,100) AS Files FROM #tmp_tbl where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())
open tmp_cur
Fetch next from tmp_cur into @filename
while @@fetch_status = 0
begin
print @filename
set @tablename = REPLACE(@filename,'.'+@ext,'')
print 'deleteing '+@tablename
set @filename = ltrim(RTRIM(@filename))
set @sql = 'del '+ @folder+ @filename
if @run = 0
print @sql
if @run = 1
EXECUTE master.dbo.xp_cmdshell @sql
Fetch next from tmp_cur into @filename
print '---'
end
close tmp_cur
deallocate tmp_cur
------------------------------------------------------
end
GO
/****** Object: StoredProcedure [dbo].[sp_delete_files] Script Date: 01/09/2013 07:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_delete_files @folder = 'B:\SQL_Performance\TraceCollection\TraceFiles\'
,@ext = 'trc'
,@minutesgoback = -60
*/
ALTER proc [dbo].[sp_delete_files]
(
@folder varchar(100)
,@ext varchar(10)
,@minutesgoback int = -6000
)
as
begin
set nocount on;
declare @run int = 1 , @reccount int = 0
declare @sql nvarchar(4000), @filename varchar(100), @tablename varchar(100), @param_definition nvarchar(4000), @folderlogs varchar(100)
--set @folderlogs = @folder +'\logs\'
set @folderlogs = @folder
------------------------------------------------
set @sql = 'DIR ' + '"' + @folderlogs + '\*.'+@ext+'"'
--print @sql
if(OBJECT_ID('tempdb..#tmp_tbl')>0)
drop table #tmp_tbl
CREATE TABLE #tmp_tbl (Name varchar(400), Work int IDENTITY(1,1))
INSERT #tmp_tbl EXECUTE master.dbo.xp_cmdshell @sql
DELETE #tmp_tbl WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.'
--select convert(datetime,SUBSTRING(Name,1,20)),SUBSTRING(Name,40,100),dateadd(MINUTE,@minutesgoback,GETDATE()), *
--From #tmp_tbl
--where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())
---------------------------------------------------
declare tmp_cur cursor for
SELECT SUBSTRING(Name,40,100) AS Files FROM #tmp_tbl where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())
open tmp_cur
Fetch next from tmp_cur into @filename
while @@fetch_status = 0
begin
print @filename
set @tablename = REPLACE(@filename,'.'+@ext,'')
print 'deleteing '+@tablename
set @filename = ltrim(RTRIM(@filename))
set @sql = 'del '+ @folder+ @filename
if @run = 0
print @sql
if @run = 1
EXECUTE master.dbo.xp_cmdshell @sql
Fetch next from tmp_cur into @filename
print '---'
end
close tmp_cur
deallocate tmp_cur
------------------------------------------------------
end
Thursday, December 20, 2012
re-size tempdb file counts and sizes
USE master
GO
DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @NUM_OF_FILES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT
SELECT @NUM_OF_FILES = 12 -- Number of tempdb files you want to have.
SELECT @SIZE = 10240 -- Size in MB
SELECT @GROWTH = 10
SELECT @ISPERCENT = 1
PRINT @NUM_OF_FILES
SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 and FILE_ID = 1)
PRINT @BASEPATH
SET @FILECOUNT = (SELECT COUNT(*)
FROM master.sys.master_files
WHERE database_id = 2 AND TYPE_DESC = 'ROWS')
WHILE @FILECOUNT > @NUM_OF_FILES
BEGIN
SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
REMOVE FILE tempdev' + RTRIM(CAST(@FILECOUNT as CHAR))
EXEC(@SQL_SCRIPT)
PRINT 'Removed ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@FILECOUNT as CHAR)) + '.ndf'
SET @FILECOUNT = @FILECOUNT - 1
END
WHILE @NUM_OF_FILES > @FILECOUNT
BEGIN
SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
ADD FILE
(
FILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'',
NAME = tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + ',
SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB,
FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
IF @ISPERCENT > 0
SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
SET @SQL_SCRIPT = @SQL_SCRIPT + ')'
EXEC(@SQL_SCRIPT)
PRINT 'Created ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'
SET @NUM_OF_FILES = @NUM_OF_FILES - 1
END
WHILE @FILECOUNT > 0
BEGIN
SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + ',
SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB ,
FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
IF @ISPERCENT > 0
SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
SET @SQL_SCRIPT = @SQL_SCRIPT + ')'
EXEC(@SQL_SCRIPT)
PRINT 'Modified ' + @BASEPATH + 'tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + '.ndf'
SET @FILECOUNT = @FILECOUNT - 1
END
GO
GO
DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @NUM_OF_FILES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT
SELECT @NUM_OF_FILES = 12 -- Number of tempdb files you want to have.
SELECT @SIZE = 10240 -- Size in MB
SELECT @GROWTH = 10
SELECT @ISPERCENT = 1
PRINT @NUM_OF_FILES
SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 and FILE_ID = 1)
PRINT @BASEPATH
SET @FILECOUNT = (SELECT COUNT(*)
FROM master.sys.master_files
WHERE database_id = 2 AND TYPE_DESC = 'ROWS')
WHILE @FILECOUNT > @NUM_OF_FILES
BEGIN
SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
REMOVE FILE tempdev' + RTRIM(CAST(@FILECOUNT as CHAR))
EXEC(@SQL_SCRIPT)
PRINT 'Removed ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@FILECOUNT as CHAR)) + '.ndf'
SET @FILECOUNT = @FILECOUNT - 1
END
WHILE @NUM_OF_FILES > @FILECOUNT
BEGIN
SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
ADD FILE
(
FILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'',
NAME = tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + ',
SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB,
FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
IF @ISPERCENT > 0
SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
SET @SQL_SCRIPT = @SQL_SCRIPT + ')'
EXEC(@SQL_SCRIPT)
PRINT 'Created ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'
SET @NUM_OF_FILES = @NUM_OF_FILES - 1
END
WHILE @FILECOUNT > 0
BEGIN
SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + ',
SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB ,
FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
IF @ISPERCENT > 0
SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
SET @SQL_SCRIPT = @SQL_SCRIPT + ')'
EXEC(@SQL_SCRIPT)
PRINT 'Modified ' + @BASEPATH + 'tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + '.ndf'
SET @FILECOUNT = @FILECOUNT - 1
END
GO
Subscribe to:
Comments (Atom)