Wednesday, August 15, 2012

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

Monday, June 11, 2012

Ordering and Ranking a data set

useful technique to pre-order data set


select
DENSE_RANK() over (order by name_first),
RANK() over (order by name_first),
ROW_NUMBER() over (order by name_first),
ROW_NUMBER() over (order by name_last),
*
from users
order by name_first, name_last

Thursday, May 03, 2012

Database Encryption with DB Certificates


SQL Servers Database Encryption on Swim lanes
This document is create on May 2nd, 2012 and is valid for Swim Lanes. Certificates are installed on LD4PRDCLT101 and this version of the certificate and key should be used across all swim lanes if needed.
Create a new Master Key
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
Go
--To Drop the masterkey
--DROP MASTER KEY

Create a new Certificate per Database that need to be encrypted
--database encryption key
CREATE CERTIFICATE DatabaseName1_Cert WITH SUBJECT = 'DatabaseName1 Certificate'
Go
CREATE CERTIFICATE DatabaseName2_Cert WITH SUBJECT = 'DatabaseName2 Certificate'
Go
CREATE CERTIFICATE DatabaseName3_Cert WITH SUBJECT = 'DatabaseName3 Certificate'
Go

--To Drop the certificate
-- DROP CERTIFICATE DatabaseName1_Cert
Backup Master Key and certificates and use for restoring databases on a different server
      Use master
GO
Backup master key to file = 'C:\MasterKey.key' encryption by password = 'Password' ;

BACKUP CERTIFICATE DatabaseName1_Cert TO FILE = 'c:\DatabaseName1_Cert_file.cert'
WITH PRIVATE KEY ( FILE = 'c:\DatabaseName1_Cert_key.cert' ,
ENCRYPTION BY PASSWORD = 'Password'
);
BACKUP CERTIFICATE DatabaseName2_Cert TO FILE = 'c:\DatabaseName2_Cert_file.cert'
WITH PRIVATE KEY ( FILE = 'c:\DatabaseName2_Cert_key.cert' ,
ENCRYPTION BY PASSWORD = 'Password'
);
BACKUP CERTIFICATE DatabaseName3_Cert TO FILE = 'c:\DatabaseName3_Cert_file.cert'
WITH PRIVATE KEY ( FILE = 'c:\DatabaseName3_Cert_key.cert' ,
ENCRYPTION BY PASSWORD = 'Password'
);

Create certificates from a backup certificate on another server, (note:  the master key should be created before this can be done)
CREATE CERTIFICATE DatabaseName1_Cert
FROM FILE = 'C:\DatabaseName1_Cert_file.cert'
WITH PRIVATE KEY (FILE = 'C:\DatabaseName1_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

CREATE CERTIFICATE DatabaseName2_Cert
FROM FILE = 'C:\DatabaseName2_Cert_file.cert'
WITH PRIVATE KEY (FILE = 'C:\DatabaseName2_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

CREATE CERTIFICATE DatabaseName3_Cert
FROM FILE = 'C:\DatabaseName3_Cert_file.cert'
WITH PRIVATE KEY (FILE = 'C:\DatabaseName3_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')




Encrypt a Database
USE [DatabaseName1]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DatabaseName1_Cert
GO
ALTER DATABASE [DatabaseName1]
SET ENCRYPTION ON
GO
USE [DatabaseName2]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DatabaseName2_Cert
GO
ALTER DATABASE [DatabaseName2]
SET ENCRYPTION ON
GO
USE [DatabaseName3]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DatabaseName3_Cert
GO
ALTER DATABASE [DatabaseName3]
SET ENCRYPTION ON
GO
Restore the Databases on a separate server, the key and certificates should be already created and restored. If the certificates are not created properly the restore will fail.
RESTORE DATABASE [DatabaseName1_retored]
FROM  DISK = N'c:\DatabaseName1.bak' WITH  FILE = 1, 
MOVE N'Data' TO N'E:\DATA_Release\DatabaseName1.mdf', 
MOVE N'Log' TO N'E:\LOG_Release\DatabaseName1.LDF', 
NOUNLOAD,  REPLACE,  STATS = 10

RESTORE DATABASE [DatabaseName2_retored]
FROM  DISK = N'c:\DatabaseName2.bak' WITH  FILE = 1, 
MOVE N'Data' TO N'E:\DATA_Release\DatabaseName2.mdf', 
MOVE N'Log' TO N'E:\LOG_Release\DatabaseName2.LDF', 
NOUNLOAD,  REPLACE,  STATS = 10

RESTORE DATABASE [DatabaseName3_retored]
FROM  DISK = N'c:\DatabaseName3.bak' WITH  FILE = 1, 
MOVE N'Data' TO N'E:\DATA_Release\DatabaseName3.mdf', 
MOVE N'Log' TO N'E:\LOG_Release\DatabaseName3.LDF', 
NOUNLOAD,  REPLACE,  STATS = 10
View which Databases are encrypted
SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)
System Tables to view the encryption keys
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys


Remove Encryption from Database
ALTER DATABASE [DatabaseName1];
SET ENCRYPTION OFF;
GO

use [DatabaseName1]
Go
DROP DATABASE ENCRYPTION KEY;
GO

Wednesday, April 25, 2012

list of failed jobs


SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0