August 16, 2016 at 8:29 am
Everything i read goes on and on about how bad it is. One of our sql instances houses a database that uses mainly batch processing and as a result does truncate and load of massive amounts of data daily. It's current mdf is 1.5 tb. There's 503,199 mb free space available.
Am i crazy to want to shrink this database and free up several hundred gigs? At what point do i shrink it? when it has 50% or 60% free space?
August 16, 2016 at 8:37 am
I hardly ever shrink data files. I Rather leave the space there for future growth.
August 16, 2016 at 8:42 am
Snargables (8/16/2016)
Everything i read goes on and on about how bad it is. One of our sql instances houses a database that uses mainly batch processing and as a result does truncate and load of massive amounts of data daily. It's current mdf is 1.5 tb. There's 503,199 mb free space available.Am i crazy to want to shrink this database and free up several hundred gigs? At what point do i shrink it? when it has 50% or 60% free space?
Quick question, how many files and file groups do you have or is all the data in a single mdf / primary?
😎
August 16, 2016 at 9:01 am
Unless the log growth was the result of a one-off process and the disk space is shared by other databases, I don't see the point in shrinking. Maybe focus on transaction log backups or refactoring ETL to minimizing logging.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 16, 2016 at 10:20 am
Snargables (8/16/2016)
Everything i read goes on and on about how bad it is. One of our sql instances houses a database that uses mainly batch processing and as a result does truncate and load of massive amounts of data daily. It's current mdf is 1.5 tb. There's 503,199 mb free space available.Am i crazy to want to shrink this database and free up several hundred gigs? At what point do i shrink it? when it has 50% or 60% free space?
If that space will never be used, or won't be used until after years of growth, shrink it. If it's going to regrow every night or every week, you're saving nothing. If the space is needed by the load process, then shrinking, in addition to fragmenting the file and all that implies, is just slowing down the load process because you're requiring it to regrow again after the shrink. We're not there and don't know how that load process works or what it does. If it needs the space, leave it alone. If it truly isn't using that space, then it's OK to do a one-off shrink. As soon as you find yourself scheduling shrink events, you're doing it wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 16, 2016 at 2:57 pm
1 file, primary
August 16, 2016 at 2:58 pm
Simple mode. Transaction log isn't the issue. It's the mdf
August 16, 2016 at 3:01 pm
This is a one off and will probably need to be done every 2 to 3 years. It's 80 percent one table. I'm going to be breaking the table out by month then using a partitioned view. Hence doubling the table size. I'll shrink it after that
August 16, 2016 at 3:08 pm
Snargables (8/16/2016)
This is a one off and will probably need to be done every 2 to 3 years. It's 80 percent one table. I'm going to be breaking the table out by month then using a partitioned view. Hence doubling the table size. I'll shrink it after that
Sounds like a reasonable case for a shrink to me.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply