asked
log file allocation
If the transaction logs for a database is increasing uncontrolled and filling up the log disk. Where should the configuration be set for this to be handled correctly. My guess is that this is a general setting assigned to the SQL-server instance related to the size of the log disks. I would like to get som confirmation on this question: Is this a configuration that is set in the SQL-instance as a general setting, or is it a database specific configuration?
Your databases are running in [FULL recovery mode][1], however you are not performing [transaction log backups][2] You need to decide what the right recovery model is for your database(s), and alter the backup schedule accordingly. The default recovery model is determined by the recovery mode of the [model database][3], but can be altered per database. [1]:
http://msdn.microsoft.com/en-us/library/ms189275.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms179478.aspx [3]:
http://msdn.microsoft.com/en-us/library/ms186388.aspx
Yes the impact is you will change the recoverability of your database. Full recovery allows for point-in-time recovery, whereas simple recovery will only allow for recovery to the last backup. It does sound however that you are running in Full without knowing fully what that means (a very understandable and typical situation) - I would urge you to seek out whoever setup and configured the server, or failing that employ the services of an experienced DBA. You will find lots of information here and other places on the internet about setting up the right backup jobs etc, but the true question is one about what your organisation wants, guided by the advice of a DBA.
Please read this blog post:
http://www.scarydba.com/2011/01/18/dba-101-why-is-my-log-file-full/ . This is a classic problem. I lay out the problem and what to do about it in some detail.
+1 Thanks Grant I was looking for a good resource to link to - the author of this one is good isn't he :)
Thanks very much Grant
10 People are following this question.
