locked
how can increase log file size? RRS feed

  • Question

  • HI All,

    How i can increase my log file size ? I try to delete 7 millions record and there is an error message not allow me to delete successfully.

    Transaction log file is full.

    Thanks.


    MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST
    Thursday, June 17, 2010 6:42 AM

Answers

  • Divide  your deletion with small batches something like that

    WHILE 1 = 1
    BEGIN

       DELETE TOP (500000)
       FROM tbl;

       IF @@ROWCOUNT < 500000 BREAK;

    END


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Naomi N Thursday, June 24, 2010 4:09 PM
    • Marked as answer by Kalman Toth Tuesday, June 29, 2010 10:35 AM
    Thursday, June 17, 2010 7:13 AM
    Answerer
  • BACKUP LOG file of the database and the run DBCC SHRINKFILE to reduce the physical size and then see my previous post to run DELETE in small batches


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by yanyee Wednesday, June 30, 2010 1:10 AM
    Thursday, June 17, 2010 7:43 AM
    Answerer

All replies

  • Divide  your deletion with small batches something like that

    WHILE 1 = 1
    BEGIN

       DELETE TOP (500000)
       FROM tbl;

       IF @@ROWCOUNT < 500000 BREAK;

    END


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Naomi N Thursday, June 24, 2010 4:09 PM
    • Marked as answer by Kalman Toth Tuesday, June 29, 2010 10:35 AM
    Thursday, June 17, 2010 7:13 AM
    Answerer
  • Set the autogrowth property of your log filet o unrestricted if not, check the incremental ratio.

    if you still get the issue check out the free space on the disk... if there is no enough space available move the log file to additional disk.

    (*** be very cautious with Production environment)

    Thursday, June 17, 2010 7:15 AM
  • --The following is the T-SQL to create a new database called “MyDB”
    --This database is stored in a folder called “Databases” on the local “D:” drive
    --The database file is “MyDB.mdf”, the initial size of the file is 3 MB (3072 KB)
    --The transaction log file is “MyDB_log.ldf”, the initial size of the file is 1 MB (1024 KB)
    --NOTE: I’ve changed the FILEGROWTH of the log to a set size; this setting’s default is 10%

    CREATE DATABASE [MyDB] ON PRIMARY(
     NAME = N'MyDB',FILENAME = N'D:\Databases\MyDB.mdf', SIZE = 3072KB , FILEGROWTH = 1024KB )
    LOG ON(
     NAME = N'MyDB_log',FILENAME = N'D:\Databases\MyDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB);
    GO

    --The following is T-SQL to grow the transaction log file from 1 MB to 20 MB
    --In practice you would set this to the optimal size based on your expected transactional usages
    --Use the following Query to increase your log size as per your expectation

    ALTER DATABASE MyDB 
     MODIFY FILE (NAME = MyDB_log,SIZE = 20MB);
    GO

    you can find out how much space is used in my log by using the following:
    DBCC SQLPERF (LOGSPACE)

    Refer this also help  :-   http://support.microsoft.com/kb/873235/en-us

    Thursday, June 17, 2010 7:22 AM
  • BACKUP LOG file of the database and the run DBCC SHRINKFILE to reduce the physical size and then see my previous post to run DELETE in small batches


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 17, 2010 7:43 AM
    Answerer
  • BACKUP LOG file of the database and the run DBCC SHRINKFILE to reduce the physical size and then see my previous post to run DELETE in small batches


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by yanyee Wednesday, June 30, 2010 1:10 AM
    Thursday, June 17, 2010 7:43 AM
    Answerer
  • Hi Uri,

     

    Let me try this out by tomorrow. Thanks.

     

    Sorry for late reply as too busy with other project. Yes it work now if delete in small batches. thanks for all reply. :)


    MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST
    • Edited by yanyee Wednesday, June 30, 2010 1:11 AM update forum reply
    Thursday, June 24, 2010 3:26 PM