locked
shrinking the log files RRS feed

  • Question

  • Hello,

    we are running on sql server 2005 and in some of our production databases the log files are twice as bigger than the data files. we are planning to reduce the log file size. I saw the option on the management studio to shrink the log file. I just have some questions on this.

    as long as we are doing on the production server can we do that while the database is online or do we need to take the database offline? any help would be appreciated. Thank you!

     

    .

    Friday, April 11, 2008 7:53 PM

Answers

  • Yes you can take a t-log backup and shrink the log file for the principal database involved in mirroring. You don't need to pause or remove mirroring to accomplish this. You can use SSMS or Dbcc shrinkfile command.

    - Deepak
    Wednesday, April 16, 2008 12:15 PM

All replies

  • Yes you can shrink the log file with the database online. No need to take the db offline. But shrinking is a bad practice and I would certainly recommend you to go through these links where similar topic was discussed and take corrective actions like changing recovery model to Simple or if it is in full recovery you need to take regular t-log backups.
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2728787&SiteID=1
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2522242&SiteID=1


    - Deepak
    Saturday, April 12, 2008 11:53 AM
  • Hello Deepak,

     

    I looked at the forums that you sent me. Here is my issue. We have some databases in the production server, that we did not take the trasaction log backups before. So the log files were way bigger that the data files. For example we have the data file size of 1600 MB and the Log file is 30025 MB. But we are taking the Tlog backups now. we are trying to reduce the Tlog file size. Is there any way to reduce with out using the shrink file option? Please let me know.  If I need to shrink the file what are the steps that I need to follow? Thank you.

    Monday, April 14, 2008 3:44 PM
  • Backing up the log will truncate the contents of the file but the physical size of the file will remain the same. If you begin doing Transaction Log Backups, the file won't grow.  You can shrink it once intially, but then you should never shrink it again.  If it grows, you should leave it where it is, or increase the frequency of your log backups to allow it to truncate and reuse the log space it has.

     

    If at all you wish to shrink it you need to take t-log backup and shrink it using DBCC shrinkfile command. Please note that you can take t-log backup only in Full/Bulk_logged recovery model.

     

    Use Yourdb

    Go

    Dbcc shrinkfile('log file name', 500)

     

    The above command will shrink your log file to 500 MB.

     

    - Deepak

    Tuesday, April 15, 2008 3:39 AM
  • Deepak,

     

    I have one more question here, If we take the t-log backup is it going to take the backup of the .ldf file? what happen if I don't specify the size in the Dbcc shrinkfile command? If I have to give the size how do you know how much to give the size. Please let me know. Thank you!

     

    Tuesday, April 15, 2008 1:13 PM
  • If you give with TRUNCATE_ONLY option then answer is no. ie

    Backup Log YourDatbasename with Truncate_Only will not backup the TL.

     

    From BOL : TRUNCATE_ONLY

    Removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.

     

    The following statement will backup the TL

     

    BACKUP LOG AdventureWorks    TO Disk='YourDb.trn'

     

     



    if I don't specify the size in the Dbcc shrinkfile command?

     

    target_size

    Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.

    If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

     

     

     

     

     

    Madhu

     

     

    Tuesday, April 15, 2008 1:37 PM
  • Hello Madhu

     

    I have one more question here can I shrink the log file for the principle database that has setup for mirroring, with out stoping the mirroring or doing any thing? And can I use the command

    BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'

    to backup the log and use the management studio to shrink the log file, I am more comfortble with that tool. Thank you!

    Tuesday, April 15, 2008 5:54 PM
  • Yes you can take a t-log backup and shrink the log file for the principal database involved in mirroring. You don't need to pause or remove mirroring to accomplish this. You can use SSMS or Dbcc shrinkfile command.

    - Deepak
    Wednesday, April 16, 2008 12:15 PM
  •  

    Thank you very much for your help deepak. I am actually DB2 DBA, recently I have additional task that is to take care of the SQL servers also. I am keep on asking so many question, so sorry about that. Any way thank you!
    Wednesday, April 16, 2008 6:52 PM