April 12, 2011 at 10:19 am
I have a database in production. I do full back every night and do the maintainance every weekend to rebuild indexes in all tables (user db) and shrink database's size to leave to 10%.
However, the database free space cannot be free.
If I run db shrink, the size get back, but next day, it will grow back again. It it in old version: 2000.
What should do I ?
Thank you
yan
April 12, 2011 at 10:32 am
1) Stop shrinking the database ... it's causes fragmentation & poor performance including when the database grows back to the size it needs to be.
2) Take frequent log backups, eg every 20 minutes.
April 12, 2011 at 10:40 am
Thank you. The thing is that it is not the problem with transaction log but db file. We use simple recovery
Yan
April 12, 2011 at 10:44 am
I assumed an active production database would be full recovery, so I added #2 as additional suggestion.
With simple recovery, you don't mind the possibility of losing all changes made since the last backup ?
April 12, 2011 at 10:44 am
The db will use the space it needs to store the data you ask it to store. Nothing you can do about that.
April 12, 2011 at 11:02 am
I agree here that you should stop shrinking. Also, it appears that you have some big processes that run nightly that are causing the data file to regrow (possibly a mass import??).
Find what is causing the growth, stop shrinking the db and then plan for additional disk space.
Shrinking the database daily is possibly causing more harm than good.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2011 at 11:08 am
possibly definitly.
April 12, 2011 at 2:58 pm
Just to be clear - rebuilding indexes is causing the database to grow, then you shrink the database which is causing the indexes to become fragmented.
Then, you rebuild the indexes causing the database to grow - shrink the database causes the indexes to become fragmented...
Now, add on top of that - everytime you shrink the database and it grows again, you are causing file level fragmentation which is going to cause additional IO issues.
So, how do you fix this...don't shrink the database. Let it grow after the index rebuilds - then manually grow it an additional 10-20% (or 6 months of growth, whichever is greater). Then monitor the database growth and manually extend it to keep available space in the data file for index rebuilds and future growth.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply