June 24, 2014 at 3:32 am
Hi
I am having a database whose size is around 10 GB.
1) If i shrink database using DBCC shrinkdatabase it comes around 9.5GB only
2) If i re-index the DB size grows to some 13 GB and if shrink database using DBCC shrinkdatabase it comes around 6.5GB
why it is so?
what is happening inside between reindex and shrink steps
I am not shrinking database regularly , doing it once in 10 months time frame.
Thanks!
June 24, 2014 at 5:20 am
Rebuilding the indexes removes wasted space by moving rows that are distributed across a lot of empty space and compacting them into individual pages. This allows for more compression. Regular shrinking of the database like this is not good. It leads to fragmentation on the physical disk that your index defragmentation does nothing to alleviate.
"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
June 24, 2014 at 6:22 am
which option to go with
a)Reindex DB after huge delete or insert happened
b)Reindex DB and shrink some one month gape
c)Shrink and Reindex DB some 10 month gape
or better way of keeping healthy database.
Database mode Simple.
DB status:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics
NOTE: Please don't ask why it is simple and what will you do if the database is lost or something else, this requirement was to have as simple as possible DB is a gateway data loss is accepted here.
June 24, 2014 at 6:27 am
If the database is going to grow to 10gb once every 10 months, why not just leave it at 10gb. The shrinking, or not shrinking, has nothing to do with index rebuilds. Those should be done on a regular basis regardless. You're doing two things when you shrink the database over and over. As I already said, you're fragmenting it on the drive. But, you're also forcing additional load on the system. As you say, it's going to grow over 10 months and, at each point where it has to grow, it's slowing down processing while it allocates more disk space, and this has to happen over and over. Set it to the size it needs to be, leave it there, you don't have to worry about growing it or shrinking it.
"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
June 24, 2014 at 6:38 am
yuvipoy (6/24/2014)
which option to go witha)Reindex DB after huge delete or insert happened
b)Reindex DB and shrink some one month gape
c)Shrink and Reindex DB some 10 month gape
d) Rebuild indexes on a regular basis. Don't shrink.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply