Re: newbie backup question

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 06/30/04 

Date: Wed, 30 Jun 2004 19:58:22 +0530

Hi,

Your data will be moved to MDF file after the checkpoint, SO no worries-
data will be in MDF file. Since the database was in FULL recovery model
those transaction will be there in LDF file and not got truncated.
You can run the below command to truncate the transaction log, there is not
need to backup the log. Even if you backup the transaction log, backup will
not be useful since you turned the recovery to SIMPLE.

How to truncate the transaction log.

1. Login to Query Analyzer
2. Execute the below command to truncate the transaction log

   backup log <dbname> with truncate_only

3. After this execute the below comand to see the usage

   DBCC SQLPERF(LOGSPACE)

4. If the usage is reduced now youi can shrink the transactionlog file

   DBCC SHRINKFILE('logical_ldf_file',truncateonly)

5. After this execute the below comand to see the usage

   DBCC SQLPERF(LOGSPACE)

Note:
Since you have changed the Recovery to SIMPLE, now onwards log file will be
truncated after the checkpoint automatically.

Thanks
Hari
MCDBA

"Josephine" <anonymous@discussions.microsoft.com> wrote in message
news:2e4701c45eae$38ad52e0$7d02280a@phx.gbl...
> Please help clear up my confusion...
> I have a database that was set in FULL recovery mode and
> the transaction log was not being backed up. To stop the
> log from growing, I switched it to SIMPLE, but after last
> night's backup I still have a 12 GB log file.
> How do I get the transactions to commit to the .mdf file?
> My concern is the data is just there in the log and if I
> back it up it isn't commited to the .mdf file.
> Backup the log file and then continue with the simple
> recovery backups?
> Is there a way to do this in EM?
> If I do this do I want no_truncate, truncate on chk pt?
> Thanks in advance for help,
> Josie.
>

Relevant Pages

  • Re: Log Shipping: question about secondary dB mode?
    ... Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. ... And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. ... all the backup and restore jobs are proceeding without errors. ...
    (microsoft.public.sqlserver.tools)
  • Re: How can I back up a log-shipped database?
    ... This means that a later log backup from the production database will not just be ... able to add the log records to the log-shipped database, because the transaction log has been ... It's clear I don't understand the whole RECOVERY business. ...
    (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: SQL 2005 Shrink issue
    ... When you perform a Transaction Log Backup, passive virtual logs in your transaction log file are deleted. ... you can change your database's recovery model to SIMPLE when you want to get rid of your passive virtual logs in your transaction log file. ...
    (microsoft.public.sqlserver.tools)
  • Re: sql server table sizes
    ... If I were you, I would not back the log up, but rather set the recovery mode ... Regularly schedule a transaction log backup using the DB Maintenance Plan ... > dbcc shrinkfile ...
    (microsoft.public.sqlserver.clients)