Missed Backups

October 17, 2011

Sometimes we all miss our failed job alerts it can lead to trouble though when you need to maintain compliance.

Here is a proc which will let you know if a database has not been backed up in more then X days.

/******************************************************************************************************
* Created By: MotherofAllGeeks
* Created on: 9/2011
* Purpose: Verify that Backups have been completed in X days
* exec proc_CheckLastBackupDate 1,’Mom@motherofallgeeks.com’, ‘MyMailProfle’
******************************************************************************************************/
CREATE PROCEDURE [dbo].[proc_CheckLastBackupDate]

@daystoCheck int,

@recipients varchar(255),
@profile varchar(255)
AS
BEGIN
SET
NOCOUNT
ON
BEGIN
 
 
 
TRY
 
/*Find out if a database has not been backed up for more then X days */
DECLARE @Database TABLE (DBName varchar(255), backupDiff int)
 
INSERTINTO @DatabaseSELECT A.name,datediff(dd,B.LastBackupDate,GETDATE()) DayDiffFROMsys.databases A 
 
INNERJOIN(SELECT a.database_name,max(convert(date,a.backup_finish_date )) LastBackupDateFROM msdb..backupset aINNERJOINmaster.sys.databases b on a.database_name = b.nameWHERE a.type='D'GROUPBY database_name)
 
B
on A.name = B.database_nameWHEREdatediff(dd,B.LastBackupDate,GETDATE())> @daystoCheck
 
IF (@@ROWCOUNT> 1)BEGIN
 
DECLARE @tableHTML NVARCHAR(MAX), @currentDate date, @usagepercentage int
 
SET @tableHTML =N'<Table border =1 cellpadding=5>'+N'<tr><th>DBname</th>'+N'<th>Last Backup</th>'+N'</tr>'+CAST(
( SELECT
td
=convert(varchar(30),database_name),'',
td
=convert(varchar(60),LastBackupDate),''
 
 
FROM (select database_name,max(a.backup_finish_date) LastBackupDatefrom msdb..backupset aINNERJOIN @Database n on A.database_name = n.DBNamewhere a.type='D'GROUPBY database_name)a
 
FORXMLPATH('tr'),TYPE)asNVARCHAR(max))+N'</table>'
 
declare @subject varchar(255)SELECT @subject ='The Following Databases May not have been backed Up on '+@@servernameexec msdb.dbo.sp_send_dbmail
@profile_name = @profile,
--'sqladmin',
 
-- @recipients = 'Maryanne_frake@adp.com',
@recipients
= @recipients,
@subject
= @subject,
@body
= @tableHTML,
@body_format
='HTML'
 
END
END
 
TRY
BEGIN
 
 
 
CATCH
 
DECLARE @ErrorMessage varchar(max)
 
 
SELECT @ErrorMessage ='ErrorNumber:'+convert(varchar(7),ERROR_NUMBER())+'<br>'+'ErrorSeverity:'+convert(varchar(7),ERROR_SEVERITY())+'<br>'+'ErrorState:'+convert(varchar(7),ERROR_STATE())+'<br>'+'Calling Procedure:'+CONVERT(VARCHAR(25),OBJECT_NAME(@@PROCID))+'<br>'+'ErrorProcedure:'+ERROR_PROCEDURE()+'<br>'+'ErrorLine:'+convert(varchar(7),ERROR_LINE())+'<br>'+'ErrorMessage:'+ERROR_MESSAGE()+'<br><hr>'RAISERROR(@ErrorMessage,16,1)
END
 
CATCH
 
 
END
 
GO
 
 

1 Comment | Maintenance, Monitoring | Tagged: , , , , | Permalink
Posted by motherofallgeeks

Long Running SQL Agent Jobs

October 14, 2011

Did you ever run into the situation where you had a job running into your production hours and you didn’t get a notification because the job didn’t actually fail?
I have experienced just that and here is a little script to get you a notification for a job which is running too long. Create this stored procedure in msdb then schedule it to run from SQL Server Agent. The Threshold for what is considered too long is a parameter. For me I check every two hours for a job which has been running longer then 60 minutes. Tailor it to work for your environment. Script was tested in SQL 2008 R1 Sp2

/************************************************************************************

– Proc Name : [[Proc_LongRunningJobs]]

– Description : This procedure will report Long Running SQL Agent Jobs

– Author : Mother of All Geeks

– exec [Proc_LongRunningJobs] 60,’MyMailProfile’, ‘mom@motherofallgeeks.com’

***********************************************************************************/

CREATE

PROCEDURE [dbo].[Proc_LongRunningJobs]

(

@Threshold int= 60

,

@MailProfile

varchar(100),

@recipients

varchar(255))

as

BEGIN

SET

NOCOUNT

ON

BEGIN

TRY

DECLARE

@Check varbinary (255

)

SELECTTOP 1 @Check = ja.job_idFROM msdb.dbo.sysjobactivity jaINNERJOIN sysjobs j on ja.job_id= j.job_idWHERE j.enabled=1 – and start_execution_date is not null and(stop_execution_date ISNULL)andDATEDIFF(MI,start_execution_date,GETDATE())isnotnulland(DATEDIFF(mi,start_execution_date,GETDATE())> @Threshold )and run_requested_date >CONVERT(date,getdate()-1)ORDERBY name – start_execution_date DESC

IF (@Check isnotnull)BEGINDECLARE @longRunning NVARCHAR(MAX)SET @longRunning =‘<Font><b>Long Running Jobs </b></font> <BR>’+N’<Table border =1 cellpadding=3>’+N’<th>SQLInstance</th>’+N’<th>JobName</th>’+N’<th>StartDateTime</th>’+N’<th>CurrentRunTimeinMin</th>’+N’</tr>’+CAST(

( SELECT

td

=isnull(@@SERVERNAME,”),”,

td

=isnull(name ,”),”,

td

=isnull(start_execution_date,”),”,

td

=isnull(DATEDIFF(MI,start_execution_date,GETDATE()),”),”FROM msdb.dbo.sysjobactivity jaINNERJOIN sysjobs j on ja.job_id= j.job_idWHERE j.enabled=1 – and start_execution_date is not null and(stop_execution_date ISNULL)andDATEDIFF(MI,start_execution_date,GETDATE())isnotnulland(DATEDIFF(mi,start_execution_date,GETDATE())> @Threshold )and run_requested_date >CONVERT(date,getdate()-1)and run_requested_date isnotnullORDERBY name – start_execution_date DESC

FORXMLPATH(‘tr’),TYPE)asNVARCHAR(max))+N’</table>’/*EMAIL THE ALERT*/

DECLARE @subject varchar(max), @body varchar(max)SET @subject =‘Long Running Job On: ‘+convert(varchar(25),@@servername)SET @body =isnull(@longRunning,”)exec msdb..sp_send_dbmail@profile_name = @MailProfile, @recipients = @recipients, @subject = @subject, @body_format =‘HTML’, @body = @body,@query_result_header = 0END

END

TRY

BEGIN

CATCH

DECLARE @ErrorMessageCatch varchar(max)SELECT @ErrorMessageCatch =‘ErrorNumber:’+isnull(convert(varchar(7),ERROR_NUMBER()),‘ ‘)+‘ ErrorSeverity:’+isnull(convert(varchar(7),ERROR_SEVERITY()),‘ ‘)+‘ ErrorState:’+isnull(convert(varchar(7),ERROR_STATE()),‘ ‘)+‘ Calling Procedure:’+isnull(CONVERT(VARCHAR(25),OBJECT_NAME(@@PROCID)),‘ ‘)+‘ ErrorProcedure:’+isnull(ERROR_PROCEDURE(),‘ ‘)+‘ ErrorLine:’+isnull(convert(varchar(7),ERROR_LINE()),‘ ‘)+‘ ErrorMessage:’+isnull(ERROR_MESSAGE(),‘ ‘)

SELECT @ErrorMessageCatchRAISERROR(@ErrorMessageCatch,16,1)

END

CATCH

END

–

GO

Leave a Comment » | Maintenance, Monitoring, SQL Server, Stored Procedures | Permalink
Posted by motherofallgeeks

Policy Evaluation Notifications

February 15, 2011

In SQL Server 2008 R1 a nice feature called Policies has been implemented. I like to use this feature to make sure no one is changing my database standards. You can create a policy to check for your standards then create the below stored proc and schedule it via a job to notify you of those policy failures.  Depending on how often your policies are evaluated you may have to change the date parameters in the query.
Enjoy!

use msdb

go
CREATE TYPE PolicyType AS TABLE
(PolicyName varchar(8000),
PolicyTarget varchar(8000),
ConditionName varchar(1000),
ConditionDesc varchar(1000)
)
GO
/******************************************************************************************************/
IF exists(SELECT 1 from sys.objects where name = ‘Get_Policy_Failure_Details’)
DROP PROCEDURE Get_Policy_Failure_Details
GO
/******************************************************************************************************
* Created By Mother of All Geeks
* Purpose is to provide more detailas about an evaluation failure in a policy
* Note A row is written to syspolicy_policy_execution_history_details only on exception
CREATE TYPE PolicyType AS TABLE
(PolicyName varchar(8000),
PolicyTarget varchar(8000),
ConditionName varchar(1000),
ConditionDesc varchar(1000)
)
SAMPLE EXEC CALL
DECLARE @FailedPolicies PolicyType
INSERT INTO @FailedPolicies
SELECT distinct Pol.name,
PolHistDet.target_query_expression,
cond.name,
cond.description –, PolHist.start_date,PolHist.end_date, PolHistDet.execution_date
FROM msdb.dbo.syspolicy_policies AS Pol
JOIN msdb.dbo.syspolicy_conditions AS Cond
ON Pol.condition_id = Cond.condition_id
JOIN msdb.dbo.syspolicy_policy_execution_history AS PolHist
ON Pol.policy_id = PolHist.policy_id
JOIN msdb.dbo.syspolicy_policy_execution_history_details AS PolHistDet
ON PolHist.history_id = PolHistDet.history_id
WHERE PolHistDet.result = 0
and PolHistDet.execution_date > convert(date,gETDATE())
and datediff(mi,PolHistDet.execution_date,GETDATE()) < 10
exec Get_Policy_Failure_Details ‘MOFG@motherofallgeeks.com’,MyMailProfile, @FailedPolicies
*******************************************************************************************************/

CREATE PROCEDURE Get_Policy_Failure_Details

(@TO varchar(255)
,@profile_name varchar(255)
,@FailedPolicies POLICYTYPE READONLY
)
AS

BEGIN
DECLARE @PolicyName varchar(60)
DECLARE @Target varchar(255)
DECLARE @execution_date datetime
DECLARE @histId int
DECLARE @detailHistId int
IF exists (SELECT 1 from @FailedPolicies)
– If there is a policy in failure
BEGIN
DECLARE @body varchar(max)
SELECT @body = ‘The Following Policy has failed evaluation:
‘

SELECT @body = COALESCE(@body + ‘

  • ‘+
    PolicyName+ ‘ ‘+PolicyTarget +’ ‘ +’ ‘+ConditionName + ‘ ‘ +
    ConditionDesc , ‘

    • ‘)
    FROM @FailedPolicies

    exec msdb..sp_send_dbmail @profile_name = @profile_name
    , @recipients = @TO
    , @subject = ‘Policy Evaluation Failure’
    , @body_format = ‘HTML’
    , @body = @body

    END
    ELSE
    RETURN
    END

    1 Comment | Maintenance, Monitoring, Policy Management, Uncategorized | Permalink
    Posted by motherofallgeeks

    Index This Index That

    December 30, 2008

    So let me ask you this… Do you really know how SQL Server storage engine works? You would be surprised how many people can’t answer simple questions about indexes on interviews. Sorry Folks if you can’t tell me what the difference between and clustered and a non-clustered index your interview score just went down.

    That being said this is a very simple question I like to ask people on interviews. If your going to rebuild the indexes on a large table which has no Forgein keys. How would you do it and why? You cannot use any  dbcc or online index operations. We are going old school. To keep it simple assume your table is 5GB, has a clustered index and 5 Non-clustered indexes some of which are composite.

    When I ask this my goal is to find out if you truly understand how the data is stored. If you do then the answer is quite easy.

    I’ll give you the answer I am looking for first then I will explain why.

    “You would drop the NON-Clustered indexes first. Then drop the Clustered index. Next you would create the CLustered index then the Non-CLustered indexes.”

    Now the why.

    A clustered index in the simplest explaination is that it is your data. A classic example of a clustered index is a phone book. Now of course we can get into B-Tree structures but lets save that for another post. That being said If I drop my clustered index first the operation will also drop and recreate my non-clustered indexes and can potentially take alot of time. To speed up the process drop the NC indexes first. (AFTER YOU SCRIPTED them of course)

    Leave a Comment » | Indexes, Maintenance, Performance | Tagged: , , | Permalink
    Posted by motherofallgeeks

    I like to Move it Move it

    December 6, 2008

    I’m sure of all of us have had to move our databases around from time to time. I just came into a situation where we are doing a POC test for DR mirroring software called Double Take. I had to uninstall and reinstall my SQL destination (or failover machine) because the first install was not done properly.  For example the System databases needed to mimic the source server. So I started along my install and I specified the path for the databases.

    After the install was done I checked and I forgot that the install will create an MSSQL directory on top of whatever path you specify in the install. UGH!!!

    For example I wanted my system and user databases to go Z:\SQLServer\MSSQL5\DATA  and that is what I told the install so the install put the databased here Z:\SQLServer\MSSQL5\DATA \MSSQL1\DATA. What an annoyance!!!!

    Rather then redoing the install I thought I will just move the system databases. Well I haven’t done this task in SQL 2005 yet and I thought ok I will go and just modify the Startup parameters as I would in SQL 2000. Not the CASE.

    Here is what I need to do.

    For Non Master databases

    ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = Z:\SQLServer\MSSQL5\DATA \model.mdf’)
    ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = Z:\SQLServer\MSSQL5\DATA \ modellog.ldf’)

    Repeat for tempdb and msdb if needed
    Stop SQL Server

    Move the files

    Start SQL Server and the databases will be in the new location and you can delete the old files.

    Moving Master is a little trickier

    Perform the same ALTER database statement executed above for master

    ALTER DATABASE master MODIFY FILE ( NAME = master, FILENAME = x:\newlocation\master.mdf’)
    ALTER DATABASE master MODIFY FILE ( NAME = mastlog, FILENAME = x:\newlocation\mastlog.ldf’)

    Next you need to change the startup parameter in the REGISTRY.  (You can search the registery for ERRORLOG or master.mdf to find the proper key for your server) Edit the values for the new location

    Stop the SQL Server Service

    Move the Files

    Start the SQL Server Service in minimal config mode -f and trace flag -T3608 (prevents auto recovery)

    From the command prompt run the following. You need to be in the location of the sqlservr.exe.

    net start MSSQLServer /f/T3608

    net start MSSQL$InstanceA /f/T3608

    Connect VIA Query Analyzer and Alter mssqlsystemresource
    ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = data, FILENAME = x:\newlocation\mssqlsystemresource.mdf’)
    ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = log, FILENAME = x:\newlocation\mssqlsystemresource.ldf’)

    Move the mssqlsystemresource files.
    Set mssqlsystemresource database to read only.  ALTER DATABASE mssqlsystemresource SET READ_ONLY
    Stop and Start the SQL Server Service in normal mode.

    Leave a Comment » | Maintenance | Tagged: , , , , | Permalink
    Posted by motherofallgeeks

    Follow

    Get every new post delivered to your Inbox.