Optimum values.

[Parasu]

Dear all,

* If my database's initial size is 50 MB, what is the optimal size for the transaction log.
* The other question is, how do I remove the logs automatically overwritten? This would reduce the harddisk space utilisation because of the transaction log.

Thanx
Parasuraman Parasuraman

 

[tlbroadbent]

The answer depends on many factors.

Which version of SQL Server are you using?
How much update activity will the database have?
How frequently will you do backups?
Which type of backups will you do?

If you use SQL 7 or later, you can set the log to autogrow so you don't need to worry about size as much as with previous versions.

When you backup a database or log, the transactions are removed from the log and the space will be reused. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Parasu]

Hi Terry,
I'd like to answer the questions u said.

* Which version of SQL Server are you using?
Im using sql server 7.0

* How much update activity will the database have?
The update activity will be pretty heavy.
* How frequently will you do backups?
Every night
* Which type of backups will you do?
Full backup

And, if i say the autogrow option, won't it keep increasing and keep using more spaces?

Thanx again Parasuraman

 

[tlbroadbent]

If you set autogrow, the log will grow until you backup the database. As I mentioned, backup will empty the log of all committed transactions.

If you have a heavy activity database, I suggest that you also do transaction and possibly differential backups throughout the day. With your present plan, if your server crashes and you lose the database and the transaction log you would only be able to recover to the last full backup. You could lose several hours of transactions. Read some information on backup planning to help you determine the best plan for your site.

http://gethelp.devx.com/techtips/thesqlpro/10min/10min1100/10min1100.asp

http://www.swynk.com/friends/sjones/BackupStrategy.asp

http://www.swynk.com/friends/boyle/dbbackup.asp

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9629

Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin