I worked as IT manager for local cell phone operator in Czech Republic, that was acquired by Vodafone a few years later.
It was second week on my new job when the proprietary selfcare system became unresponsive. The web part was php, there was a lot of integration with sms centre and IVR but the reason for trouble were sudden connection timeouts from Microsof SQL Server 2000. Support and maintenance people told me that it happens from time to time and they think the reason is Microsoft SQL Server driver for IVR that is causing trouble. They recommended to migrate database layer to Oracle, known as as much more reliable rdbms. I asked how they were solving timeout problems before and the answer was: "Nothing, whatever we do (restarting different components, servers or doing nothing) after 10 minutes or so, the system starts working as if nothing happened."
The answer made me asking for sa password, I was feeling a smell of an old enemy. The application programmer, who played role of DBA, switched on auto-shrink of self-care database.
So, why is auto-shrink bad? Actually, any shrink is bad. When you shrink database, you release the space database needed at one point in time. If it needed once, it will be probably needed again, since databases grow over the time. Even if they do not grow (in case there is archiving procedure in place that keeps the size of database with just "current" data constant), there are tasks requiring extra (free) space, like rebuilding indexes, large data imports, monthly billing compilations, ETL jobs etc. When you shrink database you reduce the database size, next time that space is needed, database has to expand, and it usually does at the time of heavy activity, causing timeouts we experienced in Vodafone.
I prepared with our "DBA" the following plan:
1. We expanded the size of self-care database, so it can handle maintenance tasks, re-indexing and big data imports without expansion.
2. We scheduled frequent enough transaction log backup in addition to regular database backups. When recovery log is full, database server can reuse transaction log space, where committed transactions are stored, only after they are backed up and marked free.
3. We set up alert that informs DBA when used space is close to the database size.
4. When the free space becomes low, dba will schedule short downtime in off-peak hours in order to enlarge the database.
It was second week on my new job when the proprietary selfcare system became unresponsive. The web part was php, there was a lot of integration with sms centre and IVR but the reason for trouble were sudden connection timeouts from Microsof SQL Server 2000. Support and maintenance people told me that it happens from time to time and they think the reason is Microsoft SQL Server driver for IVR that is causing trouble. They recommended to migrate database layer to Oracle, known as as much more reliable rdbms. I asked how they were solving timeout problems before and the answer was: "Nothing, whatever we do (restarting different components, servers or doing nothing) after 10 minutes or so, the system starts working as if nothing happened."
The answer made me asking for sa password, I was feeling a smell of an old enemy. The application programmer, who played role of DBA, switched on auto-shrink of self-care database.
So, why is auto-shrink bad? Actually, any shrink is bad. When you shrink database, you release the space database needed at one point in time. If it needed once, it will be probably needed again, since databases grow over the time. Even if they do not grow (in case there is archiving procedure in place that keeps the size of database with just "current" data constant), there are tasks requiring extra (free) space, like rebuilding indexes, large data imports, monthly billing compilations, ETL jobs etc. When you shrink database you reduce the database size, next time that space is needed, database has to expand, and it usually does at the time of heavy activity, causing timeouts we experienced in Vodafone.
I prepared with our "DBA" the following plan:
1. We expanded the size of self-care database, so it can handle maintenance tasks, re-indexing and big data imports without expansion.
2. We scheduled frequent enough transaction log backup in addition to regular database backups. When recovery log is full, database server can reuse transaction log space, where committed transactions are stored, only after they are backed up and marked free.
3. We set up alert that informs DBA when used space is close to the database size.
4. When the free space becomes low, dba will schedule short downtime in off-peak hours in order to enlarge the database.
