September 25, 2006 at 2:14 am
Hi all,
I have a large size database of arround 190 GB of size in production environment which is 24X7 operational.
We do Reindexing and Shrinking of database once in a month. Earlier it was not in clustered environment so it was taking arround 3-4 hrs to complete shrinking of database after indexing.
But now since it is in clustered environment it is taking very large time and it is increasing blockings so we have to terminate the process.
I request you all please put some efforts for me and give me some valuable suggestions.
Your suggestions will be highly appriciated.
Regards,
Chandu
September 25, 2006 at 7:03 pm
Why are you shrinking the database every month? If it's just going to grow again, then stop shrinking it. You are just forcing your users to wait for the database to grow when it fills to the point of autogrowth. Allocate enough space and leave it alone.
Because shrinking a database means moving data from pages at the tail of the files to open pages scattered throughout the files, you basically re-frag your busiest tables right after you defrag them. You are left with fragged tables, some huge tran log backups, and a database waiting for a massive performance hit when it grows again.
Killing the useless shrink job will buy you a lot of time. If the reindexing takes too long, then stop using a generic maintenence task once a month. Instead, script separate reindex commands for everything, split the list into four groups, and reindex one group each week. That way, everything still gets reindexed once a month, but the activity gets spread out over many weeks.
The clustered environment has nothing to do with the speed of reindexing. The active node has total control of the service and all of the data volumes. The clustering service, during normal operation, does little more than report back to the passive server that the clustered services are still running, and that it does not need to take over.
-Eddie
Eddie Wuerch
MCM: SQL
September 26, 2006 at 1:54 am
just to add to Eddie Wuerch 's reply :
- also check if you still use the same recovery mode. Maybe even switch recovery mode to bulk-logged during your maintenance window.
- did you alter disk-configurations when moving to your clustered env. ?
(other raid config ? )
- for your db
- for tempdb
- ...
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply