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!


MS Sql Server LazyDBA home page