I discovered a small issue in production today. Our new DR server which is currently going through the rigors of testing had all of the Scheduled Jobs left enabled. I was confused why I was getting two alerts for some data validation processing we perform overnight with two different sets of data.
After a few minutes thought, I realised it could only be the DR system and I wanted a quick way to disable all of the jobs rather than shut down the Agent service which would trigger an alert in our monitoring software.
Here is my simple query to disable all jobs as well as one to re-enable, although our DR process is a little more sophisticated to only online jobs which are enabled in production.
/* Disable All Jobs */
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ”
SELECT @SQL = @SQL + N’EXEC msdb.dbo.sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(36)) + ”’, @enabled = 0;’
FROM msdb.dbo.sysjobs
EXEC (@SQL)
/* Enable All Jobs */
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ”
SELECT @SQL = @SQL + N’EXEC msdb.dbo.sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(36)) + ”’, @enabled = 1;’
FROM msdb.dbo.sysjobs
EXEC (@SQL)
A useful query to find the size of each data file in your database instance
Read more…
I stumbled across a really cool website today. I’d never come across it in Google searches, so I figured I’d mention it on here so any readers can bookmark it straight away!
Read more…
As databases evolve, you may find that some indexes are no longer used. This is something I have seen a lot in my current job.
This script will allow you to see which indexes are used and whether they are used for seeks, scans or key lookups.
SELECT
SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]) AS [Object Name],
I.[name] AS [Index Name],
S.[user_seeks] AS [User Seeks],
S.[user_scans] AS [User Scans],
S.[user_lookups] AS [User Lookups]
FROM sys.dm_db_index_usage_stats S INNER JOIN sys.indexes I
ON I.[object_id] = S.[object_id] AND I.[index_id] = S.[index_id]
INNER JOIN sys.tables T
ON T.[object_id] = I.[object_id]
INNER JOIN sys.schemas SC
ON T.[schema_id] = SC.[schema_id]
WHERE
OBJECTPROPERTY(S.[object_id],’IsUserTable’) = 1 AND
OBJECTPROPERTY(S.[object_id], ‘IsMSShipped’) = 0 AND
S.[database_id] = DB_ID(DB_NAME())
ORDER BY
SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]), I.[index_id]
Just a quick script to list all schema’s, tables and column information from within a database.
SELECT
(S.[name] + ‘.’ + T.[name]) AS [Table],
C.[name] AS [Column Name],
D.[name] AS [Date Type],
C.max_length AS [Length],
C.precision AS [Precision],
C.scale AS [Scale],
C.is_nullable AS [Nullable],
C.is_identity AS [Identity Column],
C.[collation_name] AS [Collation]
FROM sys.tables T INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
INNER JOIN sys.columns C
ON T.object_id = C.object_id
INNER JOIN sys.types D
ON C.system_type_id = D.system_type_id
WHERE T.is_ms_shipped = 0
ORDER BY T.[name], C.column_id