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

[SimonMac]

Trying to do some housekeeping on a database, not huge by anyones standard (700GB or so) but has never had any house keeping, and has a lot of data from over 15 months ago that could easily be removed (job history etc.), which I am hoping improved tasks such as reindexing, backups and when batch jobs run.

Fag packet estimate and we can save 20% of the database just removing this data, after the data has been removed what is the consensus on shrinking the database to recover the diskspace. All reports seem to say that shrinking the database should be avoided as the database will grow to use this space again, but is that still the case when you do such a drastic purge of data?

[DimPrawn]

Shrinking the database will save disk space (if you need it for something else), but new data will cause the database to grow, which is a slow process, so pretty pointless unless your disk is nearly full or it's a read-only database.

[SimonMac]

Shrinking the database will save disk space (if you need it for something else), but new data will cause the database to grow, which is a slow process, so pretty pointless unless your disk is nearly full or it's a read-only database.

The db growth is a hell of a lot less than what I am removing, 6 months + at the current rate, is reclaiming diskspace the only benefit? Will indexing/backups run as fast with the whitespace still in place?

[LondonManc]

The db growth is a hell of a lot less than what I am removing, 6 months + at the current rate, is reclaiming diskspace the only benefit? Will indexing/backups run as fast with the whitespace still in place?

If you're talking about SQL Server, then there are some good articles out there about database maintenance tasks (don't use the wizard). I typically run the big stuff on a Saturday or Sunday when there's convenient downtime.

[WTFH]

*Not an SQL expert (also you haven't specified the database type or the system although I'm going to stab at SAP)


IF you want to improve performance, then search for the biggest tables and deal with them.


IDOCS and job history are a good shout.
Many clients restrict job history to 1 month, some as short as 2 weeks.
As for IDOCS, if the process has completed successfully then the client needs to give me a seriously good reason why the intermediary tables cannot be purged.

[SimonMac]

*Not an SQL expert (also you haven't specified the database type or the system although I'm going to stab at SAP)


IF you want to improve performance, then search for the biggest tables and deal with them.


IDOCS and job history are a good shout.
Many clients restrict job history to 1 month, some as short as 2 weeks.
As for IDOCS, if the process has completed successfully then the client needs to give me a seriously good reason why the intermediary tables cannot be purged.

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

[DimPrawn]

https://www.brentozar.com/archive/20...seriously-now/

[SimonMac]

https://www.brentozar.com/archive/20...seriously-now/

I saw that article, which is why I mentioned about the database growing over time to undo the work of the shrink, but if the regrowth is the only issue then shrinking the database after doing something so drastic is acceptable given how long it would take for the database to grow again at current rates.

Would I still notice improvements in the database if I remove data, but don't shrink if my main objective is to increase performance, not reclaim disk space

[DimPrawn]

I saw that article, which is why I mentioned about the database growing over time to undo the work of the shrink, but if the regrowth is the only issue then shrinking the database after doing something so drastic is acceptable given how long it would take for the database to grow again at current rates.

Would I still notice improvements in the database if I remove data, but don't shrink if my main objective is to increase performance, not reclaim disk space

Shrinking destroys the performance because it fragments indexes and causes disk fragmentation. You then have to rebuild, which grows the database.

[SimonMac]

Shrinking destroys the performance because it fragments indexes and causes disk fragmentation. You then have to rebuild, which grows the database.

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