locked
my server .mdf is 3.33 GB and .ldf is 77GB. I took the full backup. I want to reduce the size of the RRS feed

  • Question

  • Hi all,

    In my server .mdf  is 3.33 GB and .ldf is  77GB. I took the full backup. I want to reduce the size of the .ldf file. Can anyone suggest me how to reduce the log file size.




    Wednesday, October 26, 2011 11:03 AM

Answers

  • 1) BACKUP LOG dbname TO DISK ='C:\log.bak'

    2) DBCC SHRINKFILE (logfile_name,100)

    If it is not reduced issue step 1,and 2 again

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by hemcold Thursday, September 27, 2012 10:00 AM
    Wednesday, October 26, 2011 12:38 PM
    Answerer
  • Steps (Considering your database is in  FULL | BULK_LOGGED without replication)

     1 - Backup your t-log

     

    		BACKUP LOG
    
    			<MyDatabaseName>
    
    		TO
    
    			DISK = '<MyPathFileBackup>'
    

     

     2 - Run this

     

    		USE master;
    
    		-- #1: Check your current recovery before: SELECT DATABASEPROPERTYEX('<MyDatabaseName>','Recovery')
    
    		ALTER <MyDatabaseName> SET RECOVERY SIMPLE;
    
    		DBCC SHRINKFILE(2,<NewSizeMegaBytes>) -- In the '2' put the ID of your log file: select file_id,name from sys.master_files where database_id = DB_ID('<MyDatabaseName>') and type_desc = 'LOG'
    ALTER <MyDatabase> SET RECOVERY FULL /* Or BULK_LOGGED, equals result of #1*/;

     

    Case your database is in Transactional Replication, check status of Log Reader Agent, because if it not reading the commited transactions by some cause, the SQL Server don't truncate the inactive portion of log, for guarantee this records will be replicated before was overwritten.

     

    See here how troubleshooting Replication Agents: http://msdn.microsoft.com/en-us/library/ms151872.aspx

     

     


    []s Rodrigo Ribeiro Gomes MCTS - SQL Server 2008, Implementation and Maintenance
    • Edited by RodrigoRRG Wednesday, October 26, 2011 7:19 PM Add content explained
    • Marked as answer by hemcold Thursday, November 3, 2011 5:07 AM
    Wednesday, October 26, 2011 7:13 PM

All replies

  • If you have the disk space and you want to be able to perform point-in-time restores during the period that the log has built up, you should back up the transaction log.

     

    If you don't care about being able to do this, then change the recovery mode to simple and then back to what it is now (I'm assuming you're in full or bulk-logged).

    Then do another full backup as you will have broken the log backup chain.

    finally schedule regular transaction log backups.

    Ben

    Wednesday, October 26, 2011 11:24 AM
  • Take tran log backup. pls read the following link 

    http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp

     

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Wednesday, October 26, 2011 11:35 AM
  • In the current situation that you are in, you will have to go with the option of taking transaction log back up and trucate it to reduce the size. Further, have proper transaction log back up strategy in place so that your transaction log growth is in controllable state.

    How transaction log back up addresses this issue:

    When you have transaction log backup strategy in place, then it gets kicked off based on configuration. It cleans up all committed transactions and cleans up that space for new transactions to be stored in that. Once a transaction is committed in database, you dont need that to exist in the transaction log. Log backup with take care of this. However, as mentioned above by Ben, having transaction log in Simple mode will reduce the amount of logging. But take a call based on your business need.

    Let me know if you have any further questions on this.


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Wednesday, October 26, 2011 11:41 AM
  • You probably set up your db to FULL recovery model, so please BACKUP LOG and then run DBCC SHRINKFILE (see details in BOL)

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 26, 2011 11:43 AM
    Answerer
  • Dear Uri Dimant

    please tell me step by step

    In my server .mdf  is 3.33 GB and .ldf is  77GB. I took the full backup. I want to reduce the size of the .ldf file. Can anyone suggest me how to reduce the log file size.

    Wednesday, October 26, 2011 12:36 PM
  • 1) BACKUP LOG dbname TO DISK ='C:\log.bak'

    2) DBCC SHRINKFILE (logfile_name,100)

    If it is not reduced issue step 1,and 2 again

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by hemcold Thursday, September 27, 2012 10:00 AM
    Wednesday, October 26, 2011 12:38 PM
    Answerer
  • In fact if you perform step 1 multiple times until you see "Processed pages" count reduce (preferably to 1 page) before attempting the shrink, it will probably prevent you having to go back and try again.

    If you run into any problems shrinking back then you should check for old open transactions in the database.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Wednesday, October 26, 2011 4:30 PM
  • Steps (Considering your database is in  FULL | BULK_LOGGED without replication)

     1 - Backup your t-log

     

    		BACKUP LOG
    
    			<MyDatabaseName>
    
    		TO
    
    			DISK = '<MyPathFileBackup>'
    

     

     2 - Run this

     

    		USE master;
    
    		-- #1: Check your current recovery before: SELECT DATABASEPROPERTYEX('<MyDatabaseName>','Recovery')
    
    		ALTER <MyDatabaseName> SET RECOVERY SIMPLE;
    
    		DBCC SHRINKFILE(2,<NewSizeMegaBytes>) -- In the '2' put the ID of your log file: select file_id,name from sys.master_files where database_id = DB_ID('<MyDatabaseName>') and type_desc = 'LOG'
    ALTER <MyDatabase> SET RECOVERY FULL /* Or BULK_LOGGED, equals result of #1*/;

     

    Case your database is in Transactional Replication, check status of Log Reader Agent, because if it not reading the commited transactions by some cause, the SQL Server don't truncate the inactive portion of log, for guarantee this records will be replicated before was overwritten.

     

    See here how troubleshooting Replication Agents: http://msdn.microsoft.com/en-us/library/ms151872.aspx

     

     


    []s Rodrigo Ribeiro Gomes MCTS - SQL Server 2008, Implementation and Maintenance
    • Edited by RodrigoRRG Wednesday, October 26, 2011 7:19 PM Add content explained
    • Marked as answer by hemcold Thursday, November 3, 2011 5:07 AM
    Wednesday, October 26, 2011 7:13 PM