Friday, August 17, 2012

modify sysjobs trigger to send alert

USE [msdb]
GO
/****** Object:  Trigger [dbo].[trig_sysjobs_insert_update]    Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON
  -- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view. 
  IF (EXISTS (SELECT *
            FROM inserted
           WHERE inserted.originating_server_id NOT IN
                    (SELECT v.originating_server_id
                     FROM sysoriginatingservers_view AS v)))
  BEGIN
   RAISERROR(14379, -1, -1, 'dbo.sysjobs')
   ROLLBACK TRANSACTION
    RETURN
  END
  else
  begin

    Declare @Body1 nvarchar(max)
    Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients = 'palexander@csod.com',
    @body = @Body1,
    @subject = 'SQL Server Job Modification Alert'

  end
END

Thursday, August 16, 2012

SQL job queries

--JOB INFO
select *
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
inner join sysschedules c on b.schedule_id = c.schedule_id
--where a.name like '%SoapFeedJobs%'
where a.name like '%CES Daily Maintenance - VLINE%'


--JOB HISTORY
select *
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'

 --JOB HISTORY with outcome result
select
Case isnull(h.run_status,'') WHEN '' THEN 'Unknown' 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 msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'

Wednesday, August 15, 2012

lists all the default constraints and the default values for the user tables in the database

SELECT  
        b.name AS TABLE_NAME,
        d.name AS COLUMN_NAME,
        a.name AS CONSTRAINT_NAME,
        c.text AS DEFAULT_VALUEFROM sys.sysobjects a INNER JOIN
        (SELECT name, id
         FROM sys.sysobjects 
         WHERE xtype = 'U') b on (a.parent_obj = b.id)
                      INNER JOIN sys.syscomments c ON (a.id = c.id)
                      INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)                                          
 WHERE a.xtype = 'D'        
 ORDER BY b.name, a.name

function to get time for next job

use msdb
go

/*
select dbo.fn_next_job_time(15, null, null)
select dbo.fn_next_job_time(7, null, null)
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -','Weekend Sat')
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -',null)
select dbo.fn_next_job_time(4, 'CES Weekly Maintenance -',null)
*/
CREATE FUNCTION fn_next_job_time
(
    @interval int = 10,
    @jobname nvarchar(100) = null ,
    @schedulename nvarchar(100) = null
)
RETURNS int
AS
BEGIN

    set @jobname = @jobname + '%'
    set @schedulename = @schedulename + '%'

    declare @curr_hr int, @curr_min int, @curr_sec int, @next_hr int, @next_min int, @next_sec int
    declare @nexttime int
    --set @nexttime = 10004
    --set @nexttime = 206
    --set @nexttime = 5506
    --set @nexttime = 230847
    --set @nexttime = 145712
    --set @nexttime = 41942
    --set @nexttime = 225847
    --set @nexttime = 235847


    select top 1 @nexttime = c.active_start_time
    --a.schedule_id, b.name, c.active_start_time,c.name, *
    from sysjobschedules a
    inner join sysjobs b on a.job_id = b.job_id
    inner join sysschedules c on a.schedule_id = c.schedule_id
    where
    (b.name like @jobname or @jobname is null)
    and
    ( c.name like @schedulename or @schedulename is null)
    order by c.active_start_time desc
   
    select @curr_hr = right(convert(nvarchar(10), @nexttime/10000),2) , @curr_min = right(convert(nvarchar(10),  @nexttime/100),2),  @curr_sec = right(convert(nvarchar(10), @nexttime),2)
    set @next_sec = @curr_sec
    set @next_min = (@curr_min + @interval)%60
    set @next_hr =  @curr_hr + (@curr_min + @interval)/ 60
    set @next_hr = case when @next_hr >= 24 then 0 else @next_hr  end
    --select @nexttime, @curr_hr , @curr_min , @curr_sec
    --select @nexttime, @next_hr , @next_min , @next_sec
   
    select @nexttime = convert(int,
    (case when @next_hr  < 10 then '0'+CONVERT(nvarchar(10),@next_hr ) else convert(nvarchar(10),@next_hr ) end) +
    (case when @next_min  < 10 then '0'+CONVERT(nvarchar(10),@next_min ) else convert(nvarchar(10),@next_min ) end) +
    (case when @next_sec < 10 then '0'+CONVERT(nvarchar(10),@next_sec) else convert(nvarchar(10),@next_sec) end)
    )


   
    RETURN @nexttime

END
GO

Wednesday, July 18, 2012

list spaces used by all DBs in a server : sp_dbspaceall

use master
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dbspaceall]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_dbspaceall]
GO
/*
exec sp_dbspaceall
*/
create procedure sp_dbspaceall
as
begin

Declare @FileSpace table (
[database_id] int,
[db_name] nvarchar(100),
[file_id] int,
[file_name] nvarchar(100),
[space_used] bigint
)
INSERT INTO @FileSpace  EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' as DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'')  as spaceused from sys.sysfiles';

With SpaceInfo (DBName, FileName, FileType,FilePath,CurrentSizeMB,SpaceUsedMB)
as
(

SELECT
DB_NAME(df.[database_id]) as [DBName],
df.[name] as [FileName] ,
df.type_desc as [FileType],
df.physical_name as [FilePath],
(df.[size]*8.0)/1024.0 as [CurrentSizeMB],
(fs.[space_used]*8.0)/1024.0 as [SpaceUsedMB]
FROM 
SYS.MASTER_FILES AS df
INNER JOIN @FileSpace as fs on (df.[database_id]= FS.[database_id] and df.[file_id]=fs.[file_id])
)
SELECT
SI.DBName,
convert(dec(17,2),SUM(SI.CurrentSizeMB)) as [DatabaseSizeMB],
convert(dec(14,2),(1-(SUM(SI.SpaceUsedMB)/SUM(SI.CurrentSizeMB)))*100 )as [%age Free],
convert(dec(17,2),D.DataFileSizeMB) as  DataFileSizeMB,
convert(dec(14,2) ,(1-(D.DataFileSpaceUsedMB/D.DataFileSizeMB))*100) as [%age Free],
convert(dec(17,2),L.LogFileSizeMB) as LogFileSizeMB,
convert(dec(14,2),(1-(L.LogFileSpaceUsedMB/L.LogFileSizeMB))*100)  as [%age Free]
FROM
SpaceInfo SI
CROSS APPLY (SELECT  sum(CurrentSizeMB) as [DataFileSizeMB], SUM(SpaceUsedMB) as [DataFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype='ROWS' and DBName=SI.DBName group by DBName)  as D
CROSS APPLY (SELECT  sum(CurrentSizeMB) as [LogFileSizeMB], SUM(SpaceUsedMB) as [LogFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype ='LOG' and DBName=SI.DBName group by DBName)  as L
GROUP BY
SI.DBName, D.DataFileSizeMB,D.DataFileSpaceUsedMB,L.LogFileSizeMB,L.LogFileSpaceUsedMB
Order by D.DataFileSizeMB desc
end

SQL Query Stress Tool

http://www.mssqltips.com/sqlservertip/2730/sql-query-stress-tool/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012717

Wednesday, July 11, 2012

Preventing from Dropping database

CREATE TRIGGER [ddl_trig_Prevent_Drop_Database]
ON ALL SERVER
FOR DROP_DATABASE
AS

DECLARE @db VARCHAR(800)
SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +
' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)')) + '

To temporarily allow dropping action:
DISABLE TRIGGER ddl_trig_Prevent_Drop_Database ON ALL SERVER;

!!!!!!MAKE SURE TO RE-ENABLE THE TRIGGER CHECK AS SOON AS YOU ARE DONE!!!!!!
ENABLE TRIGGER ddl_trig_Prevent_Drop_Database ON ALL SERVER;
'
RAISERROR(@db, 16, 1)WITH LOG

ROLLBACK

GO

Tuesday, July 03, 2012

Disable Enable indexes

select
    sys.objects.name,
    sys.indexes.name,
    'alter index '+ sys.indexes.name +' on '+sys.objects.name+' rebuild',
    'alter index '+ sys.indexes.name +' on '+sys.objects.name+' disable'
from sys.indexes
    inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
    sys.objects.name,
    sys.indexes.name