January 16, 2017 at 5:01 am
Hi All,
I have to remove few databases from my production machine which are not getting used. I want to know if there are any jobs which have dependency on those Databases so that the jobs don't start getting failed. Is there a way to find the jobs having dependency on those Databases ? Or I have to check all jobs one by one ?
Also there are few jobs as integration packages. How can we view these packages to verify ?
January 16, 2017 at 7:49 am
if you run the query below in a given database, it will check the index statistics and tell you the last time a table was read/updated since the last time the sql service was stopped and started.
I would use use this as a starting point to see if the database in question is really unused or not..
as far as packages and processes that might touch it, you can search the packages xml for strings containing your your database name.
I would probably do wonething simple, like rename the database or set it to single user and see if it breaks soemthing, but my processes are not as mission critical as some peoples.
--based on the ideas from
--http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx
;WITH ServerStarted AS
(
SELECT
MIN(last_user_seek) AS first_seek,
MIN(last_user_scan) AS first_scan,
MIN(last_user_lookup) AS first_lookup
FROM sys.dm_db_index_usage_stats
),
ServerFirst AS
(
SELECT
CASE
WHEN first_seek < first_scan AND first_seek < first_lookup
THEN first_seek
WHEN first_scan < first_seek AND first_scan < first_lookup
THEN first_scan
ELSE first_lookup
END AS usage_start_date
FROM ServerStarted
),
AllObjects
AS
(SELECT
DB_NAME() AS TheDatabase,
QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) As TheTableName
FROM sys.objects
WHERE type_desc='USER_TABLE'
),
myCTE AS
(
SELECT
DB_NAME(database_id) AS TheDatabase,
QUOTENAME(OBJECT_SCHEMA_NAME(object_id,database_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id,database_id)) As TheTableName,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
)
SELECT
MIN(ServerFirst.usage_start_date) AS usage_start_date,
x.TheDatabase,
x.TheTableName,
MAX(x.last_read) AS last_read,
MAX(x.last_write) AS last_write
FROM
(
SELECT TheDatabase,TheTableName,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,TheTableName,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,TheTableName,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,TheTableName,NULL, last_user_update FROM myCTE
UNION ALL
SELECT TheDatabase,TheTableName,NULL AS last_read, NULL AS last_write FROM AllObjects
) AS x
CROSS JOIN ServerFirst
WHERE 1= CASE WHEN DB_NAME() = 'master' THEN 1 WHEN TheDatabase = DB_NAME() THEN 1 ELSE 0 END
GROUP BY TheDatabase,TheTableName
ORDER BY TheDatabase,TheTableName
Lowell
January 16, 2017 at 9:35 am
Here's a query I use to search SQL Agent jobs for a database name reference, I search the command and the database setting since it could be either way:
SELECT j.job_id, j.name, js.step_id, js.step_name,
p.name as proxy_name, js.command, js.database_name, js.output_file_name,
CASE js.on_success_action WHEN 1 THEN 'Quit with success' WHEN 2 THEN 'Quit with failure' WHEN 3 THEN 'Go to next step' WHEN 4 THEN 'Go to step ' + CAST(js.on_success_step_id AS varchar(3)) END AS on_success_action,
CASE js.on_fail_action WHEN 1 THEN 'Quit with success' WHEN 2 THEN 'Quit with failure' WHEN 3 THEN 'Go to next step' WHEN 4 THEN 'Go to step ' + CAST(js.on_fail_step_id AS varchar(3)) END AS on_fail_action,
js.retry_attempts, js.retry_interval
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
LEFT OUTER JOIN msdb.dbo.sysproxies p ON js.proxy_id = p.proxy_id
WHERE j.enabled = 1
AND (js.command LIKE '%SOAR_DEV%' OR js.database_name = 'SOAR_DEV')
ORDER BY j.name, js.step_id
January 24, 2017 at 2:39 pm
Also, we usually place the DB off-line for a few weeks (Alter Database XXX set offline with rollback immediate) so that it can be quickly placed back on-line if needed. Just remember to place the DB on-line before you delete it so that the actual DB files are deleted (if you delete an off-line DB the files will remain on disk).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply