Shrink or not to shrink that is the question?

Question

Shrink or not to shrink that is the question?

2Tall

SSChampion

Points: 14644
More actions
November 14, 2020 at 3:49 pm

#3807865

Hi. Upon a vendors request I have run maintenance against a single table and deleted 158 million rows. Their next suggestion is to shrink the database. If I do this it will cause index fragmentation (which they didn't seem to bothered about) and most of what i have rad advises against this. If I rebuild indexes it will increase the file size.
Post delete disk usage report:
Total Space Reserved: 843.33GB
Data Files Space Reserved: 221,877.44MB
Transaction Log Space Reserved: 646,805.94MB

Any advise appreciated.
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Do you have any concerns about the space being used for that database? If not - then I see no reason to perform the shrink. Is the vendor recommending this to 'fix' a performance related issue - believing that shrinking will somehow resolve that issue?

If the database has more than 50% free space - and you do not anticipate ever adding enough data to fill that up over time, then you could perform a one-time shrink leaving about 25% available in the data file (for future growth and current operations) - then rebuild the indexes. That would not grow the file again as you would have enough space available to perform the rebuilds.

Also - since you deleted that many rows from the table, you probably want to rebuild that tables indexes. Make sure you identify a correct fill factor for each index - otherwise you may in fact cause more performance issues.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

2Tall SSChampion Points: 14644 More actions · Answer 2

Hi Jeffrey. I have no concerns over the DB size however the vendor felt it might be impacting performance. Thank you for taking time to respond.

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Sergiy SSC Guru Points: 110208 More actions · Answer 3

From how you describe it your vendor has very limited understanding of how databases work. (It should be "have no clue" really, but let's go with "limited understanding" for now)

saying that the database performance might suffer from bigger size of DB files is pretty much the same as to say that Windows performance might suffer from increased size of disks.

i have a strong suspicion that if you order an independent review of the database state in the system you'll learn a lot of interesting facts about your vendor.

the very fact that you needed to delete 150mil+ records at once suggests that things are done not exactly the right way.

_____________
Code for TallyGenerator

2Tall SSChampion Points: 14644 More actions · Answer 4

You could well be right 🙂

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

depending on your system and on what space it takes on the log file to rebuild your indexes I would reduce the size of the log file - assuming I did read your values right and it size is currently 646 GB.

if you do this do ensure that the growth is set correctly (normally between 64MB and 5GB depending on system) and not as a percentage

2Tall SSChampion Points: 14644 More actions · Answer 6

Hi. Yes the transaction log is that large.

Thanks,

Phil

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

2Tall wrote:

Hi. Yes the transaction log is that large.
Thanks,
Phil

What is the auto growth setting for the transaction log? What is the recovery model for the database - I assume it is full, so then the question is how often are you performing transaction log backups?

Can you run DBCC LOGINFO in that database? This will show you the number of virtual log files...you can then read through the articles here: https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Basically, you want to shrink the log to as small as you can - then grow it back out in appropriate increments for your system.

As for performance, the size of the database file has no impact on performance. The size of the table(s) only have an impact if the queries are performing index scans - and if that is the case then you don't have the right indexes in place to support the workload.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

2Tall SSChampion Points: 14644 More actions · Answer 8

Hi. I do not manage SQL I’m the tech lead on the application.

The database is in a cluster hosted in Azure. I have been advised recovery model must be set to Full. The autograph is set at 10%.

Some good advice. The table in question is queried to perform recovery tasks and maintenance had not been able to keep up deleting entries.

I can run the suggested query and work with the SQL admins.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Sergiy SSC Guru Points: 110208 More actions · Answer 9

What is the schedule of BACKUP LOG on this database?

_____________
Code for TallyGenerator

2Tall SSChampion Points: 14644 More actions · Answer 10

Hi. I will need to check. I am surprised it is 600+GB.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

frederico_fonseca SSCoach Points: 16154 More actions · Answer 11

I'm not surprised after those 158 million rows deleted - most likely done all in one go.

2Tall SSChampion Points: 14644 More actions · Answer 12

Hi. I guess it doesn’t make much difference but the deletes were performed in batches over a 24 hour period.

Thanks,

Phil

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Sergiy SSC Guru Points: 110208 More actions · Answer 13

2Tall wrote:

Hi. I will need to check. I am surprised it is 600+GB.
Phil.

that’s disturbing.

always make sure that you have BACKUP LOG plans on every database in Full mode.

and every database in Production (except for certain system databases) better be in Full Recovery mode.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 14

2Tall wrote:

Hi. I guess it doesn’t make much difference but the deletes were performed in batches over a 24 hour period.
Thanks,
Phil

If you don’t perform BACKUP LOG every 15 minutes then yes, you’re right, it does not make any difference.

_____________
Code for TallyGenerator