Comments
Sort by recent activity
One way to do this is to add views to the msdb databases on both nodes/servers that act as a non-system-object "proxy" You can then use SQL DATA Compare with the option "include views" and then set comparison keys in SQL DATA COMPARE tables & views to use the names instead of uuids and then exclude identifier/modified/version columns from comparison e.g
USE msdb;
GO
CREATE OR ALTER VIEW sysjobs_proxy
AS
SELECT *
FROM msdb.dbo.sysjobs AS j;
GO
CREATE OR ALTER VIEW sysjobsteps_proxy
AS
SELECT st.*,
j.job_id AS j_job_id,
j.name AS job_name
FROM dbo.sysjobsteps AS st
INNER JOIN dbo.sysjobs AS j
ON j.job_id = st.job_id;
GO
CREATE OR ALTER VIEW sysjobschedules_proxy
AS
SELECT jsc.*,
j.job_id AS j_job_id,
j.name AS job_name,
sc.name AS shedule_name,
sc.schedule_id AS sc_schedule_id
FROM msdb.dbo.sysjobschedules AS jsc
INNER JOIN dbo.sysjobs AS j
ON j.job_id = jsc.job_id
INNER JOIN dbo.sysschedules AS sc
ON sc.schedule_id = jsc.schedule_id;
GO
CREATE OR ALTER VIEW sysschedules_proxy
AS
SELECT *
FROM msdb.dbo.sysschedules AS s; However - this does not yield any usable scripts to switch job-step order etc.. It's easier to script out the job with DROP and CREATE and alter top portion
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name='JOBNAME'
BEGIN
DECLARE @jobId BINARY(16)
SELECT @jobId=job_id FROM msdb.dbo.sysjobs WHERE name='JOBNAME'
EXEC msdb.dbo.sp_delete_job @job_id=@jobId, @delete_unused_schedule=1
END
/ comments
One way to do this is to add views to the msdb databases on both nodes/servers that act as a non-system-object "proxy"You can then use SQL DATA Compare with the option "include views"and then set c...
See https://redgate.uservoice.com/forums/14 ... al-objects / comments
See https://redgate.uservoice.com/forums/14 ... al-objects
The changes are not flagged by SQL Compare because they are not there yet.
I'm trying to use the SQL compare to script out a selected range of objects in one script and then implement the changes on that script.
Think big refactors eg:
*changing a column name in a range of tables and/or views that are not always "interdependent"
*replacing a UDF with inline code
*changing filters and business rules in a range of views
*applying style-formatting to a range of objects
so i'd have a script like :
ALTER VIEW vw1
ALTER VIEW vw2
... / comments
The changes are not flagged by SQL Compare because they are not there yet.
I'm trying to use the SQL compare to script out a selected range of objects in one script and then implement the changes o...
Eddie
Thanks for the feedback.
We use the dedicated model. I was mostly just wondering why tempdb is used and why these suggested indices would not be pre-included.
I often use the missing_indices DMV (performance dashboard) to spot performance problems and I stumbled upon these suggested indices many of times so I thought I'd ask around on the forum to satisfy my curiosity
Btw I was wrong to blame the DTA (in this instance) , it was the missing indices dmv/report
Lx / comments
Eddie
Thanks for the feedback.
We use the dedicated model. I was mostly just wondering why tempdb is used and why these suggested indices would not be pre-included.
I often use the missing_indices ...
ALTER PROC MS_PerfDashboard.usp_Main_GetRequestWaits
as
begin
SELECT
r.session_id,
MS_PerfDashboard.fn_WaitTypeCategory(r.wait_type) AS wait_category,
r.wait_type,
r.wait_time
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id
WHERE r.wait_type IS NOT NULL
AND s.is_user_process = 0x1 AND r.wait_type<>'WAITFOR' / comments
ALTER PROC MS_PerfDashboard.usp_Main_GetRequestWaits
as
begin
SELECT
r.session_id,
MS_PerfDashboard.fn_WaitTypeCategory(r.wait_type) AS wait_category,
r.wait_type,
r.wait_time
FROM sys.dm_exec_requ...
LTER PROC MS_PerfDashboard.usp_RequestWaits
as
begin
select r.session_id,
r.request_id,
master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(r.plan_handle) as plan_handle,
case when LEN(qt.query_text) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N'...' end as query_text,
r.statement_start_offset,
r.statement_end_offset,
r.wait_time,
r.wait_type,
r.wait_resource,
msdb.MS_PerfDashboard.fn_WaitTypeCategory(wait_type) as wait_category
from sys.dm_exec_requests r
join sys.dm_exec_sessions s on r.session_id = s.session_id
outer apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(r.sql_handle, r.statement_start_offset, r.statement_end_offset) as qt
where r.wait_type is not null and s.is_user_process = 0x1 AND r.wait_type<>'WAITFOR' / comments
LTER PROC MS_PerfDashboard.usp_RequestWaits
as
begin
select r.session_id,
r.request_id,
master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(r.plan_handle) as plan...
Chris,
Do you get what i mean?
Is use the ignore options to only detect some differences (my software is also uesd by another company so and some options are different like SQLUsers indexes etc... )
But it seems that when a table rebuild occurs, the table is not fully rebuilt when some ignore options are on / comments
Chris,
Do you get what i mean?
Is use the ignore options to only detect some differences (my software is also uesd by another company so and some options are different like SQLUsers indexes etc... ...