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?

asked Jun 21 '12 at 11:04 AM in Default

sonal.mcs
21 ● 2

(comments are locked)

3 answers:

Your databases are running in FULL recovery mode, however you are not performing transaction log backups

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, but can be altered per database.

answered Jun 21 '12 at 11:10 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

im dont have knowledge in sql administration and configuration,settigns etc.... if there is any impact if it is altered, because it is allocationg 19 gb only for log files

Jun 21 '12 at 11:24 AM sonal.mcs

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.

Jun 21 '12 at 11:30 AM Kev Riley ♦♦

Thanks for your help...

Jun 21 '12 at 11:48 AM sonal.mcs

(comments are locked)

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.

answered Jun 21 '12 at 11:55 AM