Log Space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » Log Space

13 posts, Page 1 of 2

1 2 »»

Log Space

Rate Topic

Display Mode

Topic Options

Author

Message

Krasavita

Posted Monday, September 22, 2008 11:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

How do I know if I have enough log space for my databases and system databases? In what mode my databases should be?

Post #573718

Luke L

Posted Monday, September 22, 2008 11:32 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:42 AM
Points: 2,891, Visits: 5,858

Speaking of which recovery mode, much of that depends on what your responsibilities/ SLAs are for each of the databases you support. If you don't require the ability to do point in time restores, perhaps you can get by with simple. Otherwise you'll need to be in Full or Bulk Logged mode. You can read in BOL about each and what the differences are. You need to figure out how much data loss is acceptable for your environment. Can you lose 5 minutes of data, 5 hours, 5 days?

As for disk space that's another It depends type of question... How big is your DB? How much free space are on your disks? How fast does your database grow? etc.

-Luke.

To help us help you read this

For better help with performance problems please read this

Post #573735

Jack Corbett

Posted Monday, September 22, 2008 11:33 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Basic rule of thumb for logs is to make them 20% of the database size, then monitor growth and re-size accordingly.

As far as recovery models, the answer is, It Depends. It depends on how active the databases are, how much data you (the business) can afford to lose, what the primary purpose of the database is, and possibly other factors. If you will never need to do a point in time recovery and daily full backups are enough then you should use Simple. If you need to be able to restore to a point in time then you need to be in Full and remember to take regular log backups.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #573736

Krasavita

Posted Monday, September 22, 2008 11:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

I have Data 99MB and Logs 77MB, so you are saying that Logs should be 120MB? Can I change that anytime? Thank you

Post #573741

Jack Corbett

Posted Monday, September 22, 2008 12:20 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

It sounds like your database is in Full recovery and you are not doing TX Log backups. Normally when first creating a database you estimate the space needed for the DB say 100 GB and then allocate 20% of that (20 GB) for log space. If you see your log growing often then you either need to decrease the time between log backups or increase the size of your log.

Post #573758

Krasavita

Posted Monday, September 22, 2008 12:47 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

Yes the db in full mode,but we have transaction logs set up through maintance plan. I am confused should I change the log size? Thank you

Post #573780

Lynn Pettis

Posted Monday, September 22, 2008 12:58 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 21,615, Visits: 27,445

How often are you running the Transaction Log backups in your maintenance plan? Based on the size of the database and t-log, it doesn't look like your are running it very frequently.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #573791

Steve Jones - SSC Editor

Posted Monday, September 22, 2008 1:04 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:26 PM
Points: 31,425, Visits: 13,738

Run the log backups on your interval and note the sizes. This will help you figure out how big to size the log. The more often you backup the log files, the smaller your log can be.

For the data, be sure you have enough space to handle growth in your files for a few months. You don't want them to autogrow and you don't want to manage this on a daily basis.

Follow me on Twitter: @way0utwest