SQL Server Performance

Shrink Log File

Discussion in 'SQL Server 2008 General DBA Questions' started by michellesbs, Dec 28, 2010.

  1. michellesbs New Member

    Hi,
    I found my Log File growing to large file size, i try to add the Shrink Database Task (set to shrink when grows beyond 50mb) on my daily maintenance backup plan but the Log file is still not shrink at all. Why the shrinkfile task is not work at all when apply on the maintenance plan?
    Recently I found a script to shrink the Log File as below :
    DBCC SHRINKfile([MyData_Log], 1)
    BACKUP LOG MyData WITH TRUNCATE_ONLY
    DBCC SHRINKfile([MyData_Log], 1)
    dump transaction MyData with no_log
    DBCC SHRINKfile([MyData_Log], 1)
    After run the above script the Log file size decrease to 1024kb only.
    1) is the above will cause data loss / corrupt?
    2) is it safe to run on a production server ?
    3) any other way / script can reduce the Log File size with no risk ?
    best regard,
    Michelle

  2. satya Moderator

    I see this as a FAQ in the forums, and before I insist on the solution clarify whether your server is having any disk free space issues on the drive where the transaction log is located.
    The reason the log is not SHRUNK because the maintenance jobs and other processes are contributing heavily to the transaction log which cannot be turned-off as it is compulsory. Moreover it also depends on how frequently your transaction log jobs are running on thsi server.
    Coming to you set of questions:
    1) is the above will cause data loss / corrupt?
    Not the data loss but for sure you are causing a contention on disk by running SHRINK operation repeatedly. There may be chances it may lead to corruption too.
    2) is it safe to run on a production server ?
    Not completely a safe process, as you are reinventing the wheel again and again. I suggest to take note of activity on this SQL instance including any scheduled job that are running on daily/weekly/monthly schedule.
    3) any other way / script can reduce the Log File size with no risk ?
    SHRINK DATABASE or LOG FILE must be the last operation to take on if all other fails and you are at risk on disk free space. As a best practice I wouldn't recommend that at all.
  3. Luis Martin Moderator

    Few things more (Satya was crystalline).
    For sure you have full recovery model, so you don't ever shrink nothing. In any case by more disk.
  4. MikaS New Member

    Hello,

    I would be very careful before running BACKUP LOG WITH TRUNCATE_ONLY in production server. Depending on your backup strategy you're likely to break something, like the LSN chain for log backups. I would personally never advice anyone to do TRUNCATE_ONLY and almost never transaction log file shrinking. This is because it will most likely occur again in the future and if you're using autogrowth in your tlog file, you can't control when it will happen.

    Best approach is to find a suitable drive with enough free space to house your transaction log files, but if you absolutely must use TRUNCATE_ONLY take backup of your database immediately afterwards, just to be safe.

Share This Page