CodeTempest

How to Get Back Into SQL 2005 When Locked Out

2012-01-31T11:36:00.002-06:00

Sometimes it's just good to see it done live, as it were.

How to Recover From Being Locked Out of a SQL Server 2005 database

This is SQLShare.com, so you have to be logged in to see the video, but it's free.
---

SQL Live Monitor on CodePlex

2012-01-07T03:43:00.002-06:00

Here's a nice, free SQL Monitoring tool I found while poking around on CodePlex.

Do you suck at "PowersPointless?"

2011-12-31T16:06:00.002-06:00

Frankly, everyone who works in tech should see these slides.

You Suck At PowerPoint! by @jessedee

View more presentations from @JESSEDEE

SQL Down Under #48 with Roger Doherty

2011-12-24T16:03:00.000-06:00

I listen to SQL Down Under whenever I can and this one was a particularly good show. All, or most of the stuff that's new in SQL Server 2012 is covered.

Check it out.

Show		Guest	Date	Description	MP3	WMA	Transcript
48		Roger Doherty	21Dec11	SDU Show 48 features SQL Server program manager Roger Doherty introducing SQL Server 2012.	MP3	WMA

SQL Saturday Houston -- April 21rst

2011-12-24T15:58:00.000-06:00

So I am passing this on, as requested. If you are in Houston that weekend, you should attend. I did last year and it was GREAT!.
--------------------------------------------

SQLSaturday#107is coming up on Apr 21, 2012 and we need your help to get the word out! Email or instant message your friends, post it on your blog, tweet it on Twitter - free training is coming soon for those interested in SQL Server!Seriously, let your colleagues know that SQLSaturday is coming up on Apr 21, 2012 at YES Prep Public Schools - North Central Campus, 13703 Aldine-Westfield, Houston, TX, 77039 and we still have seats left. As we get closer to the event we'll have more information on the event logistics, but for now you can just savor the wait:-)

For more information please visit SQLSaturday.com or email us at sqlsaturday107@sqlsaturday.com.
Thanks!
Team SQLSaturday

Quick View of All Users in All Databases

2011-11-17T15:04:00.001-06:00

Run it in Text Mode in SSMS (CTRL+T)

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

OBJECT/SCRIPT NAME:        Get All Users in All DBs.sql

PURPOSE:            Get all the users not sys user for all DBs not sys DBs.

Change History:     11/17/2011 2:26 PM - gmilner: Created

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

EXEC sp_MSforeachdb '
Use [?];

IF DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'')
select
cast(DB_NAME() as NVARCHAR(62)) as DatabaseName,
cast(name as NVARCHAR(62)) as DBUserName,
createdate,
isntgroup, isntname, isntuser,
issqluser, islogin, issqlrole
from sysusers
where islogin = 1
and name not in (''dbo'',
''guest'',
''INFORMATION_SCHEMA'',
''sys'')
AND DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'')
ORDER BY isntuser DESC, isntname DESC, name
'

Microsoft Remote Connectivity Analyzer Helped Me Fix My Outllook

2011-11-10T22:49:00.001-06:00

OK. So you are having problems with some sort of Exchange related connectivity -- and you are a simple DBA, perhaps, and not an Exchange Adminitrator.

This might be useful. It help me get my RPC over HTTP connection straight in my Outlook after it got all whacked yesterday.

This KB (more or less unrelated) pointed me to it:http://support.microsoft.com/kb/820281.

Microsoft Remote Connectivity Analyzer

Quickly Parse Names into First, Middle and Last

2011-09-09T11:26:00.000-05:00

This is a quick and dirty T-SQL solution. You could do a lot better with a regular expression based solution, I am sure, but sometimes you just need "good enough."

Note that PARSENAME() was originally used for parsing TCP/IP addresses so you have to convert the spaces into dots for it to work. Hence the replace ( @THE_NAME , '.' , '' ).

Also note that PARSENAME() works from right to left.

DECLARE @THE_NAME VARCHAR ( 250 )

SET @THE_NAME = 'Mrs. Amanda B. Reckonwith, Ph.D'

SET @THE_NAME =

REPLACE ( REPLACE ( REPLACE ( REPLACE ( REPLACE ( REPLACE ( REPLACE ( REPLACE (

/* get rid of dots and commas in name first*/

replace(replace ( LTRIM ( RTRIM ( @THE_NAME )), '.' ,''), ',' ,''),

'Mrs ' , '' ), 'Mr ' , '' ), 'Fr ' , '' ), 'Ms ' , '' ) , 'Rev ' , '' ), 'Hon ' , '' ), 'Miss ' , '' ), 'Dr ' , '' )

PRINT @THE_NAME

SELECT

PARSENAME ( REPLACE ( replace ( @THE_NAME , '.' , '' ), ' ' , '.' ), 4 ) as FirstName ,

PARSENAME ( REPLACE ( replace ( @THE_NAME , '.' , '' ), ' ' , '.' ), 3 ) as Middle ,

PARSENAME ( REPLACE ( replace ( @THE_NAME , '.' , '' ), ' ' , '.' ), 2 ) as LastName ,

PARSENAME ( REPLACE ( replace ( @THE_NAME , '.' , '' ), ' ' , '.' ), 1 ) as Suffix

Latest DBA "Morning Check Script"

2011-08-19T11:16:00.004-05:00

/*=========================================================================

Purpose: Morning Check Script v.10.sql

Create/Change History:

08/19/2011 10:46 AM - gmilner: Added ability to read SQL AGENT error

logs (v.10)

08/19/2011 10:20 AM - gmilner: Changed this version to alway look

at the last 3 SQL error logs

08/09/2011 12:56 PM - gmilner: Added DEADLOCKS LIST LAST 5 DAYS.

05/26/2011 6:18 PM - gmlner: Added section to detect databases with

broken Tranaction Log Backup Chain.

05/20/2011 10:53 AM - gmilner: Added checks for jobs with no operators

and for NEW SQL Jobs last 7 days.

05/09/2011 3:59 PM - gmilner: Added the section

"Databases with Log File too Large"

02/11/2011 9:55 AM - gmilner: More comprehensive "FREE DRIVE SPACE"

section

12/20/2010 9:25 AM - gmilner: Changed to look at msdb..backupset.type

for <> 'L' to allow for Differential backups if applicable.

04/23/2010 10:48 AM - gmilner: Added check for overlapping SQL Logins

with code from Timothy Ford

03/24/2010 10:29 AM - Changed to be able to run on any server all

at once.

4/14/2008 11:54:54 AM - gmilner: Created.

NOTE: BEST RUN IN TEXT MODE IN SSMS (use CTRL+T)

=========================================================================*/

set nocount on

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

Determine overlapping server role memberships in

SQL Server 2005 and SQL Server 2008

Author: Timothy Ford

http://thesqlagentman.com

source: http://www.mssqltips.com/tip.asp?tip=1991

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

print '-----------------------------------------------------------------------------------------------------'

PRINT ' Overlapping Security Logins on ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

if exists (SELECT *

FROM master.sys.server_principals SPRole

INNER JOIN master.sys.server_role_members SRM

ON SPRole.principal_id = SRM.role_principal_id

INNER JOIN

(SELECT SP.name AS LognName, SP.principal_id

FROM master.sys.server_principals sp_roles

INNER JOIN master.sys.server_role_members SRM

ON sp_roles.principal_id = SRM.role_principal_id

INNER JOIN master.sys.server_principals SP

ON SRM.member_principal_id = SP.principal_id

WHERE sp_roles.type_desc = 'SERVER_ROLE' --AND SP_roles.name <> 'sysadmin'

GROUP BY SP.name, SP.principal_id

HAVING COUNT(SP.name) > 1) AS SPMember

ON SRM.member_principal_id = SPMember.principal_id)

BEGIN

PRINT '***************** OVERLAPPING SECURITY LOGINS ********************'

SELECT CAST(SPMember.LoginName as VARCHAR(35)) AS LoginName,

CAST(SPRole.name AS VARCHAR(35)) AS RoleName

FROM master.sys.server_principals SPRole

INNER JOIN master.sys.server_role_members SRM

ON SPRole.principal_id = SRM.role_principal_id

INNER JOIN

(SELECT SP.name AS LoginName, SP.principal_id

FROM master.sys.server_principals sp_roles

INNER JOIN master.sys.server_role_members SRM

ON sp_roles.principal_id = SRM.role_principal_id

INNER JOIN master.sys.server_principals SP

ON SRM.member_principal_id = SP.principal_id

WHERE sp_roles.type_desc = 'SERVER_ROLE' --AND SP_roles.name <> 'sysadmin'

GROUP BY SP.name, SP.principal_id

HAVING COUNT(SP.name) > 1

) AS SPMember

ON SRM.member_principal_id = SPMember.principal_id

ORDER BY SPMember.LoginName

END

ELSE

PRINT '--- No overlapping Security logins ---'

PRINT ''

DECLARE @THE_COUNT INT

-- show all those missing backups

print '------------------------------------------------------------------------------------------------'

PRINT ' (Online) Databases missing backups in last 24 Hours ON '

+ @@SERVERNAME + ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '------------------------------------------------------------------------------------------------'

SELECT

@THE_COUNT = COUNT(*)

FROM [master].dbo.sysdatabases sdb

LEFT OUTER JOIN (SELECT

a.database_name,

type,

backup_start_date

FROM msdb..backupset a

join msdb..backupmediaset b

on a.media_set_id = b.media_set_id

join msdb..backupmediafamily c

on a.media_set_id = c.media_set_id

WHERE 1 = 1

AND type<>'L'

--AND database_name = ''?''

AND database_name NOT IN ('Pubs','Northwind','tempdb')

AND backup_start_date

BETWEEN GETDATE()-1 AND GETDATE() ) bs

ON sdb.name = bs.database_name

WHERE 1 = 1

AND sdb.name <> 'tempdb'

AND sdb.version <> 0

--and sdb.name = ''?''

and bs.type IS NULL /* no backup!!! */

IF @THE_COUNT > 0

BEGIN

SELECT

CAST(sdb.name AS VARCHAR(40)) AS DBName,

CASE

WHEN bs.type IS NULL

THEN '!!! No Backup!!!'

ELSE bs.type

END AS BackupType,

bs.backup_start_date AS BackupStartDate

FROM [master].dbo.sysdatabases sdb

LEFT OUTER JOIN (SELECT

a.database_name,

type,

backup_start_date

FROM msdb..backupset a

join msdb..backupmediaset b

on a.media_set_id = b.media_set_id

join msdb..backupmediafamily c

on a.media_set_id = c.media_set_id

WHERE 1 = 1

AND a.type<>'L'

--AND database_name = ''?''

AND database_name NOT IN ('Pubs','Northwind',

'tempdb')

AND backup_start_date

BETWEEN GETDATE()-1 AND GETDATE() ) bs

ON sdb.name = bs.database_name

WHERE 1 = 1

AND sdb.name <> 'tempdb'

AND sdb.version <> 0

--and sdb.name = ''?''

and bs.type IS NULL /* no backup!!! */

ORDER BY 1

END

ELSE

PRINT '**** ALL DATABASES HAVE BEEN BACKED UP IN LAST 24 HOURS!!! ***'

PRINT ''

---------------------- Last Backup times for all databases (Full, Diff, Log): --------------------------------------

print '-----------------------------------------------------------------------------------------------------'

PRINT ' Last Backup times for all databases (Full, Diff,Log) '

+ @@SERVERNAME + ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

DECLARE @days INT

SET @days = 7

SELECT CAST(sd.name AS VARCHAR(50)) AS [Database],

MAX(

CASE WHEN bs.type = 'D' THEN 'Full backup'

WHEN bs.type = 'I' THEN 'Differential'

WHEN bs.type = 'L' THEN 'Log'

WHEN bs.type = 'F' THEN 'File/Filegroup'

WHEN bs.type = 'G' THEN 'Differential file'

WHEN bs.type = 'P' THEN 'Partial'

WHEN bs.type = 'Q' THEN 'Differential partial'

ELSE 'Unknown (' + bs.type + ')'

END

) AS [Backup Type],

MAX( bs.backup_start_date) AS [Bak Date]

FROM master..sysdatabases sd

LEFT OUTER JOIN msdb..backupset bs

ON RTRIM(bs.database_name) = RTRIM(sd.name)

LEFT OUTER JOIN msdb..backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE 1 = 1

AND sd.name NOT IN ('Pubs','Northwind','tempdb')

AND bs.backup_start_date > GETDATE() - @days

AND bs.type = 'L'

AND sd.version <> 0

group by sd.name

UNION ALL

SELECT CAST(sd.name AS VARCHAR(50)) AS [Database],

MAX(

CASE WHEN bs.type = 'D' THEN 'Full backup'

WHEN bs.type = 'I' THEN 'Differential'

WHEN bs.type = 'L' THEN 'Log'

WHEN bs.type = 'F' THEN 'File/Filegroup'

WHEN bs.type = 'G' THEN 'Differential file'

WHEN bs.type = 'P' THEN 'Partial'

WHEN bs.type = 'Q' THEN 'Differential partial'

ELSE 'Unknown (' + bs.type + ')'

END

) AS [Backup Type],

MAX( bs.backup_start_date) AS [Bak Date]

FROM master..sysdatabases sd

LEFT OUTER JOIN msdb..backupset bs

ON RTRIM(bs.database_name) = RTRIM(sd.name)

LEFT OUTER JOIN msdb..backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE 1 = 1

AND sd.name NOT IN ('Pubs','Northwind','tempdb')

AND bs.backup_start_date > GETDATE() - @days

AND bs.type = 'I'

AND sd.version <> 0

group by sd.name

UNION ALL

SELECT CAST(sd.name AS VARCHAR(50)) AS [Database],

MAX(

CASE WHEN bs.type = 'D' THEN 'Full backup'

WHEN bs.type = 'I' THEN 'Differential'

WHEN bs.type = 'L' THEN 'Log'

WHEN bs.type = 'F' THEN 'File/Filegroup'

WHEN bs.type = 'G' THEN 'Differential file'

WHEN bs.type = 'P' THEN 'Partial'

WHEN bs.type = 'Q' THEN 'Differential partial'

WHEN bs.type IS NULL THEN '**** NO FULL BACKUP ****'

ELSE 'Unknown (' + bs.type + ')'

END

) AS [Backup Type],

MAX( bs.backup_start_date) AS [Bak Date]

FROM master..sysdatabases sd

LEFT OUTER JOIN

(SELECT *

FROM msdb..backupset bs

WHERE backup_start_date > GETDATE() - @days

AND bs.type = 'D'

) bs

ON RTRIM(bs.database_name) = RTRIM(sd.name)

LEFT OUTER JOIN msdb..backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE 1 = 1

AND sd.name NOT IN ('Pubs','Northwind','tempdb')

AND sd.version <> 0

group by sd.name

ORDER BY [Database], [Backup Type]

----------------------------------------------------------------------------

print '-----------------------------------------------------------------------------------------------------'

PRINT ' Databases with Log File too Large ON ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

--05/09/2011 3:48 PM - Tim Ford

-- Source: http://www.mssqltips.com/tip.asp?tip=2366

--+-----------------------------------------------

--Create necessary temp table for results

--+-----------------------------------------------

IF EXISTS

(

SELECT name

FROM tempdb.sys.[tables]

WHERE name LIKE '#trx_log_size%'

)

DROP TABLE #trx_log_size;

CREATE TABLE #trx_log_size

(

database_name nvarchar(128) NOT NULL PRIMARY KEY ,

[name] nvarchar(128) NOT NULL,

physical_name nvarchar(260) NOT NULL,

size_mb int NOT NULL

)

CREATE INDEX IX_trx_log_size__size_mb

ON #trx_log_size

(

size_mb

)

--+-----------------------------------------------

--Populate temp table with current log file sizes

--+-----------------------------------------------

EXEC [dbo].[sp_foreachdb]

@command = 'INSERT INTO [#trx_log_size] ([database_name], [name],

[physical_name], [size_mb])

SELECT N''?'', name, physical_name, size*8/1024

FROM [?].sys.database_files WHERE type = 1;',

@suppress_quotename = 1

--+-----------------------------------------------

File size::backup size (Full recovery DBs)

ctl+shift+m to replace days param to include in

analysis of backup history

--+-----------------------------------------------

SELECT

CAST(L.[database_name] AS VARCHAR(36)) AS DatabaseName,

CAST(L.[physical_name] AS VARCHAR(50)) AS PhysicalName,

cast(L.[size_mb] as varchar(10)) as [size_mb],

cast(MAX(CEILING(BF.[backup_size]/1024/1024)) as Varchar(10)) AS MaxLDFBakSizeMB,

cast(L.[size_mb] - MAX(CEILING(BF.[backup_size]/1024/1024)) as varchar(10))AS file_excess_mb

FROM msdb.dbo.[backupfile] BF

INNER JOIN msdb.dbo.[backupset] BS ON [BF].[backup_set_id] = [BS].[backup_set_id]

INNER JOIN [#trx_log_size] L ON [BS].[database_name] = L.[database_name]

INNER JOIN master.sys.[databases] SD ON L.[database_name] = SD.[name]

WHERE BS.[type] = 'L'

-- AND BS.[backup_start_date] > DATEADD(d,,GETDATE())

AND SD.[recovery_model_desc] = 'FULL'

GROUP BY SD.[name], L.[database_name], L.[physical_name], L.[size_mb]

HAVING L.[size_mb] > MAX(CEILING(BF.[backup_size]/1024/1024))

AND (L.[size_mb] - MAX(CEILING(BF.[backup_size]/1024/1024))) > 25

ORDER BY L.[size_mb] - MAX(CEILING(BF.[backup_size]/1024/1024)) DESC;

--+-----------------------------------------------

--Clean up your messes when you're done!

--+-----------------------------------------------

DROP TABLE #trx_log_size;

----------------------------------------------------------------------------

PRINT ''

print '-----------------------------------------------------------------------------------------------------'

PRINT ' Databases with Broken Backup Log Chain ON ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

/* these databases have a proble with the backup log chain being broken.

For more info, see http://sqlserversleuth.com/2011/05/19/code-snippet-reestablishing-the-backup-log-chain-with-powershell/

SELECT

CAST(DB_Name([database_id]) AS VARCHAR(64)) AS [DatabaseName]

FROM [sys].[database_recovery_status]

WHERE (

([last_log_backup_lsn] IS NULL)

AND

(Cast(DatabasePropertyEx(DB_Name([database_id]), 'Recovery') AS NVarChar(16)) = N'FULL')

AND

(Cast(DatabasePropertyEx(DB_Name([database_id]), 'Status') AS NVarChar(16)) = N'ONLINE')

)

ORDER BY [DatabaseName] ASC;

----------------------------------------------------------------------------

PRINT ''

---------------------- Check drive space in GB for all drives: --------------------------------------

--SET NOCOUNT ON

print '-----------------------------------------------------------------------------------------------------'

PRINT ' FREE DRIVE SPACE ON ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

/* Disk Capacities in TSQL.sql

Author: Nicholas Williams

Date: 2009 sometime

Description:??? Provides information on the drive capacities for a particular instance.

Requires xp_cmdshell to be active, and the fsutil windows utility. (Present on Windows Server 2003+)

user must have sa permissions. uses a (fast) cursor.... yeah, i know... :(

Results are returned in MB.

xp_cmdshell enabler:

??? sp_configure 'show advanced options',1

?? GO

RECONFIGURE

sp_configure 'xp_cmdshell',1

RECONFIGURE

source: http://www.sqlservercentral.com/scripts/DISK/72323/

EXEC sp_configure 'show advanced options',1

RECONFIGURE with override

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE with override

SET NOCOUNT ON

DECLARE @vcName CHAR(1)

CREATE TABLE #tbl_disks

(

Drive CHAR(1)

,[Space] INT

)

CREATE TABLE ##tbl_Volumes

(

Drive CHAR(1)

,Info VARCHAR(2000)

)

CREATE TABLE ##tbl_Volumes2

(

Info VARCHAR(2000)

)

INSERT INTO #tbl_disks

EXEC master.dbo.xp_fixeddrives

DECLARE file_cursor CURSOR FOR

SELECT Drive FROM #tbl_disks

ORDER BY Drive

OPEN file_cursor

FETCH NEXT FROM file_cursor

INTO @vcName

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC

INSERT INTO ##tbl_Volumes (Drive)

VALUES ('''+@vcName+''')

INSERT INTO ##tbl_Volumes2

EXEC master.dbo.xp_cmdshell ''fsutil volume diskfree '+@vcName+':''

UPDATE ##tbl_Volumes

SET Info = v2.Info

FROM ##tbl_Volumes2 v2

WHERE v2.Info LIKE ''%Total # of bytes%''

AND ##tbl_Volumes.Drive = '''+@vcName+'''

TRUNCATE TABLE ##tbl_Volumes2

FETCH NEXT FROM file_cursor

INTO @vcName

END

CLOSE file_cursor

DEALLOCATE file_cursor

SELECT

CAST(@@SERVERNAME as VARCHAR(35)) ServerName

,v.Drive

, CAST(CAST((CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1),

(LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))

/(1024*1024) AS DECIMAL(38,2))/1024 AS VARCHAR(18)) TotalVolume_GB

,d.[Space]/1024 FreeSpace_GB

,CAST(CAST((CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1),

(LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))

/(1024*1024) - (d.[Space]) AS DECIMAL(38,2))

/1024 AS VARCHAR(18)) as SpaceUsed_GB

,CAST(CAST(CAST((d.[Space] / ((CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1),

(LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))

/(1024*1024) / 100)) AS INT) AS DECIMAL(38,2))

AS VARCHAR(18)) AS PercentFreeSpace

FROM ##tbl_Volumes v

INNER JOIN #tbl_disks d ON v.Drive = d.Drive

PRINT 'Results are returned in MB, unless otherwise specified'

DROP TABLE #tbl_disks

DROP TABLE ##tbl_Volumes

DROP TABLE ##tbl_Volumes2

--SET NOCOUNT OFF

sp_configure 'xp_cmdshell',0

RECONFIGURE with override

sp_configure 'show advanced options',1

RECONFIGURE with override

PRINT ''

---------------------- Check for SQL Agent Job failures last 3 days: --------------------------------------

print '-----------------------------------------------------------------------------------------------------'

PRINT ' FAILED JOBS LAST 3 DAYS ON ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

DECLARE @THE_COUNT INT

SELECT @THE_COUNT =(SELECT COUNT(*)

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobhistory jh

ON j.job_id = jh.job_id

WHERE 1 = 1

AND ( jh.MESSAGE LIKE '%failed%'

AND jh.run_status = 0)

AND CAST(CAST(jh.Run_date AS VARCHAR(25)) AS DATETIME) BETWEEN GETDATE()-3 AND GETDATE()

)

IF @THE_COUNT > 0

SELECT TOP 500

CAST(j.NAME AS VARCHAR(68)) AS JobName,

CAST(j.description AS VARCHAR(60)) AS description,

j.Enabled ,

jh.Step_id,

CAST(jh.Step_name AS VARCHAR(50)) AS Step_name,

jh.Run_status,

CAST(jh.MESSAGE AS VARCHAR(120)) AS StepMsg,

jh.Run_date, jh.run_time

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobhistory jh

ON j.job_id = jh.job_id

WHERE 1 = 1

AND ( jh.MESSAGE LIKE '%failed%'

AND jh.run_status = 0)

AND CAST(CAST(jh.Run_date AS VARCHAR(25)) AS DATETIME)

BETWEEN GETDATE()-3 AND GETDATE()

ORDER BY j.NAME, jh.Run_date, jh.run_time

ELSE

PRINT '*** NO FAILED JOBS LAST 3 DAYS. ***'

PRINT ''

---------------------- Check for SQL Agent Job failures last 3 days: --------------------------------------

print '-----------------------------------------------------------------------------------------------------'

PRINT ' NEW SQL JOBS CREATED IN LAST 7 DAYS on ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

-- source: http://www.mssqltips.com/tip.asp?tip=2390

SELECT CAST([Name] AS VARCHAR(64)) AS JobName, [Date_Created], [Enabled]

FROM MSDB.dbo.sysjobs

WHERE [Date_Created] BETWEEN DATEADD(dd, -7, GETDATE()) AND GETDATE();

PRINT ''

print '-----------------------------------------------------------------------------------------------------'

PRINT ' NEW SQL JOBS WITH NO ALERT OPERATORS ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

-- source: http://www.mssqltips.com/tip.asp?tip=2390

SELECT CAST(j.[Name] AS VARCHAR(64)),

j.[Date_Created], '',

j.[Enabled],

sched.Name as SchedName

FROM msdb.dbo.sysjobs j

LEFT JOIN msdb.dbo.sysjobschedules js

ON j.job_id = js.job_id

LEFT JOIN msdb.dbo.sysschedules sched

ON js.schedule_id = sched.schedule_id

WHERE j.[Notify_Level_Email] = 0

AND j.[Enabled] = 1

AND j.[Name] NOT LIKE '%-%-%-%-%' -- no GUID based names.

/* ---- find and fix the above jobs ---------------------

SELECT [ID], [Name], [Enabled]

FROM MSDB.dbo.sysoperators

WHERE [Enabled] = 1

ORDER BY [Name];

UPDATE S

SET S.[notify_level_email] = 2,

S.[notify_email_operator_id] = --

FROM MSDB.dbo.sysjobs S

WHERE S.[Notify_Level_Email] = 0

AND S.[Enabled] = 1;

PRINT ''

---------------------- check SQL Errorlog --------------------------------------

CREATE TABLE #Errors

(Logdate VARCHAR(23), ProcessInfo VARCHAR(12), MsgText varchar(3000))

INSERT #Errors

EXEC xp_readerrorlog 0

INSERT #Errors

EXEC xp_readerrorlog 1

INSERT #Errors

EXEC xp_readerrorlog 2

CREATE INDEX idx_temp1 ON #Errors(Logdate /* , MsgText */)

-- select * FROM #Errors

print '-----------------------------------------------------------------------------------------------------'

PRINT ' DEADLOCKS LIST LAST 5 DAYS ON ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

SELECT distinct

CAST(Logdate AS DATETIME) AS Logdate, ProcessInfo, MsgText

FROM #Errors

WHERE MsgText LIKE 'Deadlock encountered ....%'

AND CAST(Logdate as DATETIME) > GETDATE() - 5

ORDER BY CAST(Logdate AS DATETIME) -- DESC

PRINT ''

print '-----------------------------------------------------------------------------------------------------'

PRINT ' ALL SQL ERROR LOG ERRORS LAST 3 DAYS ON ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

SELECT Logdate, ProcessInfo, MsgText

FROM #Errors

WHERE MsgText NOT LIKE '%Log backed up%'

AND MsgText NOT LIKE '%.TRN%'

AND MsgText NOT LIKE '%Database backed up%'

AND MsgText NOT LIKE '%.BAK%'

AND MsgText NOT LIKE '%Run the RECONFIGURE%'

AND MsgText NOT LIKE '%Copyright (c)%'

AND MsgText NOT LIKE 'Login succeeded for user%'

AND MsgText NOT LIKE 'CHECKDB for database % finished without errors%'

AND MsgText NOT LIKE 'DBCC CHECKDB % WITH all_errormsgs, no_infomsgs, data_purity executed by % found 0 errors and repaired 0 errors.%'

AND MsgText NOT LIKE '%This is an informational message only%'

AND MsgText NOT LIKE '% No user action is required.'

--AND MsgText NOT LIKE 'SQL Server has encountered % occurrence(s) of cachestore flush%'

AND CAST(Logdate as DATETIME) > GETDATE() -3

ORDER BY CAST(Logdate AS DATETIME) -- DESC

PRINT ''

print '-----------------------------------------------------------------------------------------------------'

PRINT ' ALL SQL Agent ERRORS LAST 7 DAYS ON ' + @@SERVERNAME

+ ' ServerName: (' + CAST(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS VARCHAR(64)) + ')'

print '-----------------------------------------------------------------------------------------------------'

-- Source: http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005

DELETE FROM #Errors

INSERT #Errors

EXEC xp_ReadErrorLog 0, 2

INSERT #Errors

EXEC xp_ReadErrorLog 1, 2

INSERT #Errors

EXEC xp_ReadErrorLog 2, 2

SELECT Logdate, ProcessInfo, MsgText

FROM #Errors

WHERE (MsgText LIKE '%error%'

OR MsgText LIKE '%stop%'

OR MsgText LIKE '%deactivate%'

OR MsgText LIKE '%failed%'

OR MsgText LIKE '%disabled%'

OR MsgText LIKE '%The process could not be created%' )

AND CAST(Logdate as DATETIME) > GETDATE() - 7

ORDER BY CAST(Logdate AS DATETIME) -- DESC

PRINT ''

-----------------------------------------------

DROP TABLE #Errors

Another SQL Server Build List

2011-08-07T14:10:00.000-05:00

The SQL Server Versions list I used to like, over on SQLSecurity.com now seems to be broken.

I have found another good one here, though.

Enjoy

Great Online SQL Formatter

2011-07-06T14:41:00.003-05:00

I have been using Instant SQL Formatter a lot lately. I highly recommend it.

The settings I like to use are:

Identifier case: Uppercase
Keywords case: Unchanged Function case: Unchanged
List and Parameters Style: Stacked
Max length per line in compact mode: 102