Calling all SQL experts - To shrink or not to shrink?

[LondonManc]

Not SAP (Dynamics AX), that is exactly what I did, 4 of the biggest tables are the job history from when the system was launched in 2014 and not done with anything since

Be careful with job history, it takes a fair while to clear down.

If it's SQL Server (which Dynamics AX tends to sit on), then do you mean the SQL Server job history table? There's a retention parameter which sets the number of days to keep. Reducing this will mean that the clear up process will suddenly dump the extra days - make sure you do this at a quiet time (create some SQL to run after your weekly backup).

[DimPrawn]

Indexes are rebuilt weekly, but if there is less data in the tables, the rebuilt indexes etc will be completed quicker?

You seem to be confusing removing data vs shrinking database. By all means remove old data, just shrinking the DB will move all the pages around, causing table and index fragmentation and OS disk fragmentation.

[SimonMac]

Be careful with job history, it takes a fair while to clear down.

If it's SQL Server (which Dynamics AX tends to sit on), then do you mean the SQL Server job history table? There's a retention parameter which sets the number of days to keep. Reducing this will mean that the clear up process will suddenly dump the extra days - make sure you do this at a quiet time (create some SQL to run after your weekly backup).

No, this is Dynamics Batch history, yeah it is taking a while, only doing a month at a time but making a big improvement on size so far at least.

[SimonMac]

You seem to be confusing removing data vs shrinking database. By all means remove old data, just shrinking the DB will move all the pages around, causing table and index fragmentation and OS disk fragmentation.

Its clear in my mind at least

I am removing data, that bit I am happy with, I want to know if this alone will improve performance of backup/indexing etc or will I need to shrink the database as well

[DimPrawn]

Its clear in my mind at least

I am removing data, that bit I am happy with, I want to know if this alone will improve performance of backup/indexing etc or will I need to shrink the database as well

Like I said, shrinking a database will ruin the performance, so it's up to you.

[SimonMac]

Like I said, shrinking a database will ruin the performance, so it's up to you.

But is it only a temporary performance drop? You mention the indexes, but they are scheduled to be rebuilt anyway, so does this negate the loss of performance

[LondonManc]

No, this is Dynamics Batch history, yeah it is taking a while, only doing a month at a time but making a big improvement on size so far at least.

Sounds similar to the SSIS batch job history. Had fun with that last year.
One thing for you to look for - there may be a retention parameter or you might need to set up a job to periodically purge the batch history.

[DimPrawn]

But is it only a temporary performance drop? You mention the indexes, but they are scheduled to be rebuilt anyway, so does this negate the loss of performance

Rebuilding the indexes after the shrink will move all the pages around again, the DB will then grow much larger during this process. OS Disk fragmentation will get even worse. But if it makes you feel like you're making things better and can invoice why the hell not?

[mudskipper]

I can explain it with smarties

[SimonMac]

Simple enough for me to understand though?