Re: Very large LDF

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

  • 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)
  • Re: Recover records from ldf file.
    ... when you "slide in" files from a different db, you should only do the ldf file. ... Yes, the db will be suspect, but you can do the log backup using NO_TRUNCATE. ... I'm working with copies of everything so there is no danger I will make things worse with the production database. ... If I can backup this transaction log, then restore it, I can dump out the rows of the table I need with a script to a csv file, then insert them into the production database with a script. ...
    (microsoft.public.sqlserver.server)
  • 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)
  • Re: Transaction Log Size
    ... We currently use a 3rd party backup agent to backup our SQL databases and ... transaction logs (Commvault Galaxy iDataAgent for SQL) and the database is ... Because we are using a 3rd party agent to backup the database, ... > here are some articles on how to shrink your transaction log. ...
    (microsoft.public.sqlserver.server)