July 20, 2006 at 12:05 am
Dear all,
I need to know is this good to shrink the database after every backup.
If not then what will be the effect if we continue shrinking the database after every Database Backup.
Hope u undestand my question.
From
Killer
July 20, 2006 at 12:10 am
It's not considered good practice to shrink a production database.
If the database has been shrunk and new data is added then it has to grow again, probably at an inconvenient time. Grows and shrinks are expensive IO operations
Also shrinking a database reorders data and index pages to fit into a smaller volume, hence you are fragmenting your indexes every time you 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
July 20, 2006 at 3:35 am
Dear ,
Thanks for the reply.But still i need to know what will be the worst part if a DBA schedule a job to shrink database after every database backup in the production enviornment.
from
killer
July 20, 2006 at 3:39 am
As I said, it will force the database to grow again, when data is added. This grow may occur at a busy time, slowing the database down. It will fragment your indexes, possibly leading to degraded performance.
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
July 20, 2006 at 5:53 am
Dear,
I understand what u are trying to say.But i need some strong points a part from what u described in ur posts.
So that i can explain to my directors and stop the process.
I want to know what else can happen if i continue shrinking the database.
The database is 24*7 mode (few user at night time approx:50 to 60).
The process is as follows.
Backup takes 3 hours to compete.After Backup complete shrink database job exceutes.
Hope u undestand.
from
Killer
July 21, 2006 at 1:44 pm
It's a performance hit. If your asking weather or not a shrink will corrupt your DB, the answer is no. You should go back and suggest running the shrink once a week, unless you HAVE to shrink the DB everyday due to size constriants. After you run a shrink, you should also update stat's and reindex if possible.
July 24, 2006 at 1:07 am
- I hope you're performing SQL backups using the T-sql backup statement or a maintenance job from sqlserver
- If a db is "to big" that my have two causes :
1) the is a whole bunch of insert/update/delete with rowrelocation or not resulting in empty pages.
2) insert into tables with clustered indexes cause many pagesplits
In both cases schedule DBCC DBReindex on your tables having clusered indexes. (Let's hope every table has one !)
This will optimize your data and your dataaccess !!!
We also schedule sp_updatestats after a reindex, because stats may get inaccurate due to frequent small operations that did not trigger the stats.
like Christian Benvenuto mentioned you have to take at least 2 performance-hits !
1) during the shrink operation
2) during the new extend
Your applications - and users - will be better served with a scheduled reindex than with a scheduled shrink, because a shrink does not optimize your data.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 24, 2006 at 9:02 pm
Dear all,
Thanks a lot. I am able to convince the director for stopping the shrink database process.
This is dont becaue of ur help guys.
thanks once again.
From
Killer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply