Re: Very large LDF

Answer to your questions:-

1. If a database is backed up using the 'Complete Backup' option via
Enterprise Manager (All Tasks - Backup database) - will this backup the
transaction logs
as well?

No. You will have to backup the transaction log seperately.

Answer:-

2. Also when creating a DB Maintenance plan there is an option on one Tab to
do a complete Backup (of the database) and on another Tab to back up the
transaction logs. Thinking about it, if a full database backup is done then
why do the transaction logs need backing up as well as - does a full backup
commit all remaining transactions?

Ans:-

Thats an option in Maintenance plan. You can set the schedule once a day for
full database backup and 30 minutes once for transaction log backup.
So say you can schedule at midnight 12:00 full database backup and from
12:30 AM to 11:30 PM you could schedule transaction log backup.

Eg:-
Incase if there is a database crash at 10:15 AM morning; then you can do
below to recover your database:-

a. Restore the Full database backup taken at 12:AM With NORecovery option
b. Restore the Transaction log backup till 09:30 one by one with NoRecovery
c. Restore the transaction log backup taken at 10:00 AM with RECOVERY

So the trasnaction log will make sure that your database is recovered to
closest time.


3.And lastly - is the '> 1. Perform a Transaction log backup' 'BACKUP LOG
mydb TO mydblogbackup' the same as going into Enterprise Manager
(All Tasks - Backup) and backing up the Transaction log only.

Ans:-

Yes, both are same. You could use Maintenance plan and schedule the backup.
Keep an eye once a day to make sure the log backup is happening and if you
have enough room
in harddisk to store the log backups.


Thanks
Hari

"Atlas" <Atlas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5401ED35-C164-49FC-AE1C-64301FF5A830@xxxxxxxxxxxxxxxx
Thanks for that information, very helpful.

A couple of further questions just to clarify my understanding of the
backup
process.

If a database is backed up using the 'Complete Backup' option via
Enterprise
Manager (All Tasks - Backup database) - will this backup the transaction
logs
as well?
Also when creating a DB Maintenance plan there is an option on one Tab to
do
a complete Backup (of the database) and on another Tab to back up the
transaction logs. Thinking about it, if a full database backup is done
then
why do the transaction logs need backing up as well as - does a full
backup
commit all remaining transactions?

And lastly - is the '> 1. Perform a Transaction log backup'
'BACKUP LOG mydb TO mydblogbackup' the same as going into Enterprise
Manager
(All Tasks - Backup) and backing up the Transaction log only.

Thanks in advance


"Hari Prasad" wrote:

Hi,

This is because you are not performing the transaction log backup. The
LDF
file will be cleared after the log backup.

How to shrink the existing LDF file:

1. Perform a Transaction log backup (BACKUP LOG - refer books online)
2. Use DBCC SHRINKFILE on trasnaction log file

Have a look into the below link;

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318

Note:

Check the Recovery model you are using for that database. If it is FULL
and
your data is not critical then change the
Recovery model to SIMPLE. Simple recovery model will clear the
Transaction
log after commiting.

If you need the recovery model as FULL then,

Schedule a Transaction log backup based on your data growth, This can be
used when point in time recovery.
This will ensure that ur log file wont grow much.


Thanks
Hari


"Atlas" <Atlas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:52FC44F4-7F78-4A97-AB79-1A157207C0A6@xxxxxxxxxxxxxxxx
Hello,

I have a database that has an extremely large LDF file. The DB is
approx 1
GB and the LDF about 20 GB. Presumably the LDF file should not be this
large.
The database gets backed up using Veritas Backup Exec. Can someone tell
me
what I should do to the database in order to reduce the LDF file as it
is
taking up unnecessary space.
Is it just a mater of shrinking the database from Enterprise Manager or
is
there allot more involved.
The DB is set to Full recovery model and gets backed up every night
(using
Veritas Backup Exec)

Thanks






.

Relevant Pages

  • What is the best practice to create Maintenance Plans
    ... There is not a definitive answer to the question of which backup is better. ... the database can be recovered to the point ... Full Recovery or Bulk-Logged Recovery model. ... Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • Re: Large Transaction Log Backup after Database Backup
    ... script and see for yourself that the database backup doesn't truncate the ... ALTER DATABASE steve SET RECOVERY FULL ... But my understanding was that the transaction log was> truncated after a full database backup. ...
    (microsoft.public.sqlserver.server)
  • ALTER DATABASE (optimization job)
    ... Recovery model FULL ... the transaction log on my ... >1) Before the optimization job start backup the database ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Log Size
    ... Backup database does not empty the transaction log files. ... How to Shrink the SQL Server 7.0 Transaction Log ...
    (microsoft.public.sqlserver.server)
  • Disaster Averted?
    ... Some process, unknown at this point, flooded a transaction log to the point ... the .mdf file was 0 bytes according to EM. ... Following the backup, I ... the database was now Suspect! ...
    (microsoft.public.sqlserver.server)