Transaction log spans 2 files

• Juanita

  SSCertifiable

  Points: 5300

  More actions

  September 22, 2006 at 9:20 am

  #367445

  HI. I have to backup a transaction log that I believe has never been backed up before. It is currently at 8 G. The transaciton log is now made up of 2 files(it autogrew to where it had to create another file)  Can I set up a normal maintenance plan and include the step where the transaction log is backed up? I have never dealt with a transaction that is made up of more than 1 file.

  I was not sure how this worked.

  Question #2, once I backup the transaction log, how would I go about shrinking this transaction log being that it is 2 files?

  thanks so much,

  Juanita 

• September 25, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  September 25, 2006 at 7:44 pm

  #662397

  The number of transaction log files does not matter to a transaction log backup.  Just run your job like you would if there were only one file.

   

  -Eddie

   

  Eddie Wuerch
  MCM: SQL

• Juanita

  SSCertifiable

  Points: 5300

  More actions

  September 26, 2006 at 7:39 am

  #662456

  Thank you. Now it appears that about 6 g of this transaction log is not used. I would to shrink the transaction log. when I go to ALL TASKS. and choose 'shrink database' and then click on FILES, which log do I shrink since there are now 2?

  I would actually like to remove the 2nd log because I don't think it is necessary.

  Juanita

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  September 26, 2006 at 8:16 pm

  #662615

  Do not use Enterprise Manager for important tasks.

  The command to shrink files is DBCC SHRINKFILE(fileid, newsize).

  Take a look in BOL for more info.  You can get the fileid from the sysfiles system table.

  While shrinking transaction logs, you usually have to take a transaction log backup and try a few more times in order to move all of the log entries out of the way.

  DBCC SHRINKFILE(3, 1000)

  GO

  BACKUP LOG MyDatabase TO DISK='backupFilename1.trn'

  GO

  DBCC SHRINKFILE(3, 1000)

  GO

  BACKUP LOG MyDatabase TO DISK='backupFilename2.trn'

  GO

  In order to drop a transaction log file completely, you must use DBCC SHRINKFILE with the EMPTYFILE option.  (See BOL)

  Note that transaction logs get large for a reason.  Be sure you are not shrinking the transaction log file to the point that it will grow again.  Give it the room it needs to work.

  -Eddie

   

  Eddie Wuerch
  MCM: SQL