Post reply

Shrinking the ldf file

  • April 23, 2008 at 8:54 am

    #185647

    Hi,

    In our production server recently we had created a new database 'mydb' in full recovery model. Sizes of Data and Log files are:

    DataFile (.mdf) InitialSize: 250mb Autogrow

    LogFile (.ldf) InitialSize: 1 gb Autogrow

    We were taking only full and diff backups. But recently to decrease the data loss in case of disaster we had started taking the transaction log backups also. But the size of the transaction log backup is almost 1 gb. Upon further investigation we found that the Available free space in the logfile is 90%. So I would like to shrink the size of the logfile. Will shrinking the size of the logfile will decrease the size of the transanctionlog backup. And if so what are the precautions and commands to achieve this without any data loss. This database is continuously in use by our callcenter application as we are into insurance related business. If required we can take a downtime of 15mins. But our mission is to decrease the size of the logfile without any data loss. Please guide me

    Thanks

    Chandra Mohan

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • April 23, 2008 at 9:04 am

    #806123

    After you have backed up the transaction log, you can you the GIU to shrink the file, or you can use dbcc shrinkfle, either will work

  • April 23, 2008 at 11:17 am

    #806194

    Yes, As Tim said, after taking Tlog backup, you can use DBCC SHRINKFILE command as below

    DBCC SHRINKFILE(logfilename, targetsize of the log).

    "If there is any open transactions on the database, you can not shrink"

    Also you can try the following too to truncate the Transaction log file... But before doing this, take a Tlog backup (to avoid data loss if any failure occurs).

    1. BACKUP LOG [database name] WITH TRUNCATE_ONLY

    2. DBCC SHRINKFILE (LogfileName, TRUNCATEONLY)

    3. (Important)Take a FULL BACKUP of your DATABASE, this will avoid the LSN (log Sequence Number) mismatch for your further Tlog backups.

    Hope this make sense to you.

    Have a great day...

    Warmest

    Jay.

    Thanks
    Jay
    http://www.sqldbops.com

  • April 23, 2008 at 1:21 pm

    #806276

    Avoid unneccessary shrinks of the database files. Size the files according to what is appropriate for your business/applicaton (I always error on being bigger than what is needed). Search this site for a TON of related links.

    -- You can't be late until you show up.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply