Actually...what you need to do is something along these lines...


use dbname

BACKUP LOG dbname WITH NO_LOG
GO
DBCC SHRINKFILE(logical_log_name found in systables,TRUNCATEONLY)
GO


Hope this helps...

Luis



"george eapen "
<mssqldba-ezmlmsh
ield-x44181776.x1 To
[Email Address Removed] "LazyDBA Discussion"
.com> <[Email address protected]
cc
24/11/2004 17:47
Subject
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