none
The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space. RRS feed

  • Question

  •  

    Hi all

     

    Iam working in Prodcution ENV,Please help how make space

     

     

    The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.

     

     

    Monday, February 4, 2008 2:11 PM

Answers

  • You can refer this thread it will answer your questions,

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2728787&SiteID=1

    When your log file has grown enormously you have 2 options to curtail the growth,

    1. Take a transaction log backup which will truncate the log file and then perform shrinking of log file using DBCC SHRINKFILE statement it will be successful.
    2. You can execute the command Backup log your database name with truncate_only (or backup log your database name with no_log) and shrink the log file. But running those commands (backup log with truncate_only or no_log) will truncate the log file but will break the log chain and hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.

    - Deepak

    Monday, February 4, 2008 2:20 PM
    Moderator
  • Use the below command,

    Backup log dbname to disk='Path\Filename.trn'

    Use yourdb
    go
    DBCC shrinkfile('logfilename',500)

    go

     

    - Deepak
    Monday, February 4, 2008 2:52 PM
    Moderator
  • Hi Rajakrishnan, Welcome to MSDN Forums Smile

    After running the above command, the log file size will become 500 MB. The syntax of the command is as follows,
    DBCC shrinkfile(filename, target size) where target size is in MB. Refer this link for more info,
    http://msdn2.microsoft.com/en-us/library/ms189493.aspx

    - Deepak

    Friday, February 29, 2008 5:11 PM
    Moderator

All replies

  • Hi

     

    Is this statement works

     

    BACKUP LOG <' Database name '> WITH TRUNCATE_ONLY

     

    DBCC SHRINKFILE('<logfilename>')

     

    Monday, February 4, 2008 2:17 PM
  • You can refer this thread it will answer your questions,

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2728787&SiteID=1

    When your log file has grown enormously you have 2 options to curtail the growth,

    1. Take a transaction log backup which will truncate the log file and then perform shrinking of log file using DBCC SHRINKFILE statement it will be successful.
    2. You can execute the command Backup log your database name with truncate_only (or backup log your database name with no_log) and shrink the log file. But running those commands (backup log with truncate_only or no_log) will truncate the log file but will break the log chain and hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.

    - Deepak

    Monday, February 4, 2008 2:20 PM
    Moderator
  • Hi Deepak

     

     

    shall i use like this

     

    BACKUP LOG <' Database name '>

     

    DBCC SHRINKFILE('<logfilename>')

     

    Monday, February 4, 2008 2:36 PM
  • Use the below command,

    Backup log dbname to disk='Path\Filename.trn'

    Use yourdb
    go
    DBCC shrinkfile('logfilename',500)

    go

     

    - Deepak
    Monday, February 4, 2008 2:52 PM
    Moderator
  • Thanks Deepak.

     

    Monday, February 4, 2008 3:44 PM
  • Deepak,

     

    After took log backup. if i try the following command what will happen?

     DBCC shrinkfile('logfilename',500)

     

    It will create 500 MB free space or except 500 MB it will clear the remaining space in the log?

     

    please update me.

    Friday, February 29, 2008 3:20 PM
  • Hi Rajakrishnan, Welcome to MSDN Forums Smile

    After running the above command, the log file size will become 500 MB. The syntax of the command is as follows,
    DBCC shrinkfile(filename, target size) where target size is in MB. Refer this link for more info,
    http://msdn2.microsoft.com/en-us/library/ms189493.aspx

    - Deepak

    Friday, February 29, 2008 5:11 PM
    Moderator