Shrink Database

Question

Shrink Database

david.holley

Hall of Fame

Points: 3627
More actions
December 23, 2015 at 6:12 pm

#303403

I have a project that uses a SQLExpress database. The size of the database is close to 50% of the max for a SQLExpress db. A big honking chunk of that is data is logging from ELMHA (ASP.NET) which I don't need. Once I blow away those records, the actual size of the database will be significantly less 141,399 KB from 586,950 KB. Is there any harm in shrinking the database?
I had read somewhere that Shrink should only be used sparingly.
AutoShrink is set to False
AutoGrowth is enabled (10 MB increments)
Max File Size Unlimited

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Yes, shrinking will be DEVASTATINGLY BAD due to the internal fragmentation it creates in all of your tables/indexes. Why can't you just leave the database the size it is? Is there any harm in doing that?

If you DO shrink, shrink to a reasonable size (leaving some free space in other words) and then you MUST REBUILD EVERY INDEX. I would take some checkdbs and backups along the way too.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

david.holley (12/23/2015)
I have a project that uses a SQLExpress database. The size of the database is close to 50% of the max for a SQLExpress db. A big honking chunk of that is data is logging from ELMHA (ASP.NET) which I don't need. Once I blow away those records, the actual size of the database will be significantly less 141,399 KB from 586,950 KB. Is there any harm in shrinking the database?
I had read somewhere that Shrink should only be used sparingly.
AutoShrink is set to False
AutoGrowth is enabled (10 MB increments)
Max File Size Unlimited

You do realize that 586,950 KB is only a little over a half gig and that SQLExpress can handle database up to 10 gig, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

david.holley Hall of Fame Points: 3627 More actions · Answer 3

I'm incredibly bad at math and doing conversions, but I make a killer Pineapple/Jalapeno caserole.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

david.holley (12/23/2015)
I'm incredibly bad at math and doing conversions, but I make a killer Pineapple/Jalapeno caserole.

Heh... sounds awesome! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

david.holley Hall of Fame Points: 3627 More actions · Answer 5

Appropriately that pineapple-jalapeño casserole is yellow, the same color as the Yellow Screen of Death which ELMAH conveniently captures. Deleting the past 2 years of errors (some legitimate ASP.Net errors, most being code-raised to caoture Dara about users and assorted information that I wanted to monitor), cut the size of the database by 2/3 reassuring me that a SQLExpress DB is more than adequate for at least the next 10 years.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

You don't need to shrink the file. Once you've deleted the logging info, SQL will reuse the space in the data file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

andrewcamary (12/29/2015)
As suggested above, It is not recommended to shrink database anymore. Instead, you can shrink data file if it required in your environment.

Err.... no. All the comments above about why not to shrink were relevant for the data file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

simon-hulse SSC Enthusiast Points: 194 More actions · Answer 8

🙂

A DBA's first reaction is always "Noooo" - don't shrink ! Actually, you need to consider what your DB typical growth is going to be - and can you afford the disk space if un-shrunk...

1. Are you going to manage the ASP.NET logging ? i.e. are you going to only keep a days worth so the DB will never grow at that rate again ?

2. If disk space is not an issue - yeah, SQL will re-use the free-space in the file - WHEN it needs to. If it never needs to - you have a file using disk space that is not required for your normal operation.

Shrinking is not that bad. it does have side effects of fragmenting your indexes. but again, depending on the size of your indexes this can be nominal (under 1% fragmentation).

The answer is - whats your normal operation - and what do YOU want to do... (shrinking every night is not a good idea for example as the DB has grown under normal operation - so shrinking it is pointless and detrimental). But if this is a one-time growth due to development or bad management - yeah - shrink it!

david.holley Hall of Fame Points: 3627 More actions · Answer 9

Thanks for the responses. In Googling around, it's interesting to see the responses to the issue. I'll probably keep the true .net errors for 90 days and the advisory messages for 30.

I do find it interesting that the gut reaction is no giving the potential impact to Indexes, but a part of me can't help but wonder if normal monitoring shouldn't include checking the indexes to begin with.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

Not so much 'normal monitoring', more 'normal maintenance'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

david.holley (12/29/2015)
I do find it interesting that the gut reaction is no giving the potential impact to Indexes, but a part of me can't help but wonder if normal monitoring shouldn't include checking the indexes to begin with.

Not sure what you're talking about... it should be understood that maintaining indexes should be automated and done on a regular basis and we did warn that shrinking a data file will fragment the hell out of it. We even stated that in makes no difference if you're shrinking the data file or the database, the result will be the same hell for the data file.

If you mean that other articles on the subject of shrinking data files don't mention the massive fragmentation that it causes, it's because they either forgot to include that very important fact or they don't actually know what they're talking about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ed Wagner SSC Guru Points: 287015 More actions · Answer 12

simon-hulse (12/29/2015)
Shrinking is not that bad. it does have side effects of fragmenting your indexes. but again, depending on the size of your indexes this can be nominal (under 1% fragmentation).

I've never seen 1% fragmentation as the result of shrinking the data file. I've seen that 90% and more and routine, but never 1%. I guess the exception would be if you have no indexes at all in your database, but I don't think that's a realistic scenario.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

david.holley (12/29/2015)
Thanks for the responses. In Googling around, it's interesting to see the responses to the issue. I'll probably keep the true .net errors for 90 days and the advisory messages for 30.
I do find it interesting that the gut reaction is no giving the potential impact to Indexes, but a part of me can't help but wonder if normal monitoring shouldn't include checking the indexes to begin with.

See Ola.Hallengren.com for THE SQL Server Maintenance Solution. Free, documented, supported, and Ola is a great guy to boot! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

simon-hulse SSC Enthusiast Points: 194 More actions · Answer 14

hmm..

I have just run a shrink file and a shrink file with reorganise on a 30Gb database. The DB has lots of indexes. Just checking a couple... (both Clustered and Non-Clustered) - Fragmentation on all indexes didn't get over 5% on any index. typically it was around 1%.

It will depend on how organised the data is in your DB before the shrink...