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?
more ▼

asked Jun 21, 2012 at 11:04 AM in Default

sonal.mcs gravatar image

21 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

[3]: http://msdn.microsoft.com/en-us/library/ms186388.aspx
more ▼

answered Jun 21, 2012 at 11:10 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

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, 2012 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, 2012 at 11:30 AM Kev Riley ♦♦
Thanks for your help...
Jun 21, 2012 at 11:48 AM sonal.mcs
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Jun 21, 2012 at 11:55 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1 Thanks Grant I was looking for a good resource to link to - the author of this one is good isn't he :)
Jun 21, 2012 at 12:02 PM Kev Riley ♦♦
Eh... He gets by. Ha!
Jun 21, 2012 at 12:11 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Thanks very much Grant
more ▼

answered Jun 21, 2012 at 12:56 PM

sonal.mcs gravatar image

21 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 21, 2012 at 11:04 AM

Seen: 743 times

Last Updated: Jun 21, 2012 at 12:56 PM