The normal approach is to join to a calendar table for the datetime between the given times.

Okay, no problem with forwarded records.

At least it seems the tables in your query are heaps which might jog someone else's memory.

This could be a problem with heaps and too many forwarded records. See what numbers are produced by:

DECLARE @YourDB sysname = 'YourDB';

SELECT OBJECT_NAME(O.object_id) AS TableName, COALESCE(S.forwarded_record_count, 0) AS forwarded_record_count

FROM sys.dm_db_index_physical_stats(DB_ID(@YourDB),...

Try the following on the production DB out of hours:

USE [VoruhusGagna];

GO

DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS;

GO

That question was a coincidence. I had to refer to Paul Randal’s blog article for one of our development boxes yesterday.

https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

I can never quite remember the details.

I also suspect running the lot remotely will be more efficient but there might be two tables to copy as [ETL].[dbo].[ETLCorrelation] and ETLCorrelation could be different.

You should get something going...

Assuming the PK of [dbo].[POP10110] is (PONUMBER, ITEMNMBR), try playing with the following:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[X_POPREQUIREDDATECHANGE]

ON [dbo].[POP10110]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT, XACT_ABORT ON;

INSERT INTO [dbo].[X_POPDateChange]

(PONUMBER, ITEMNUMBER, ITEMDESC, QTY, UOFM, REQUESTBY,...

I think some third party backup solutions, like Redgate, allow you to restore specific objects (tables etc) from a backup.

If you can afford the license cost, it might be worth...

You could look at creating asynchronous triggers with service broker. This would stop the summary table from interfering with production DB.

The downside is the plumbing and having to monitor service...

marc.corbeel (10/6/2016)

---

Yes but here you include the complete select command in the condition.

Problem is here that my real query is very complex (about 40 lines), and putting it 2 times...

One way is to expand the dates with a number/tally table and then roll them up again:

-- *** Consumable Test Data ***

-- Please supply in future with dates in ISO...

SELECT ADD_MONTHS(TRUNC(sysdate, 'MONTH'), 1)

FROM DUAL;

When using Windows utilities, Performance Monitor, perfmon, will be better at showing server bottlenecks.

It should be relatively cheap to upgrade the amount of memory in the servers to 32GBytes.

(I think that is the maximum for the standard version of WS2008R2.)

It is also worth...

As mentioned, this is an horrible design.

If you have to live with an app which uses it, I would alter it so the parameters are logged and a check made...