As for size of transaction log, as always, it depends...
Depends on how much data you care to potentially lose (1 week, 1 day, 1 hour, 5 minutes). Call this your "comfort interval".
So, start with 10 MB, and after the comfort interval, see if it's grown. (There's a DBCC command that tells you how much of the log is actually used). If your comfort interval is 5 minutes, and after 5 minutes you have a 25 MB log, you should probably size it to 50 MB. If your comfort interval is 1 day, and you have a 100 MB log, size it to 150 MB. (Oversize so you don't suffer performance degradation while the log file is expanded).
Re: recovery: No, you can get away with not backing up the master, that will not affect the recovery. We back everything up nightly (including master and msdb).
P
-----Original Message-----
From: george eapen
[mailto:mssqldba-ezmlmshield-x44181776.[Email address protected]
Sent: Wednesday, November 24, 2004 12:47 PM
To: LazyDBA Discussion
Subject: SUSPECT: Transaction log backup
Hello..
I have a database 120MB size with a transactional log size of 300MB (it had
never been backed up before). I have seen and tried the script to reduce the
size of the transaction logs, which works great.
This database uses the full recovery model and I read in a couple of place
that if a backup of the logs is performed, it truncates the transaction logs
and only contains the active transactions. So I basically want to keep my
log size as small as possible. I tried the following script:
USE master
Backup database databasename to test_backup_data
backup log databasename to test_backup_log
After executing it, it creates the backup file with a size of 250Kb and the
original log still remains at 300Mb.
I am just confused as to whether this is the way it is supposed to work or
not. I was expecting that the backup log would have a size of 300MB and the
original log would come down to a few megabytes. Do I just need to run the
script that reduces the transactional log size to achieve this, since it's a
virtual log file? And does the backed up log file contain all the
transactional log entries I need?
How would I best determine what is the ideal size for the transactional log?
- Start with 10Mb and see how much it grows in a week and set it at that?
I have another question as well.. when backing up databases, is it possible
to recover them even if I don't back up the Master database? How often do
you recommend to backup the master database, assuming I backup the other
databases once a day?
Thanks!
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page