Post reply

How to shrink Log file to a minimum?

  • April 13, 2004 at 9:58 pm

    #85169

    Hi all,

    Can you help me shrink a log file which has grown too large. The data file size was only 300MB but the log file has grown to 2,3GB!!

    The log file size grown after I run a stored procedure which basically perfrom insert select ( Bulk insert ) from a table in a database and insert it to table in another database.

    I noticed after running the sp several times, the log file for database from which I select the records grow.

    How do I:

    1.) Shrink the log file to a minimum, say 10MB after I perform full backup on the database? Or can I create a new log file for the database and drop the old log file?

    2.) Avoid the log file to grow so huge after perform insert select .. statement?

    Thanks,

    Danny

  • April 14, 2004 at 1:50 pm

    #502807

    1) You might want to shrink the log right after the full backup (or LOG backup). Refer to DBCC SHRINKFILE.

    2) You might want to change the recovery model to BULK_LOGGED instead. Check on ALTER DATABASE .... SET RECOVERY ....

  • April 15, 2004 at 1:22 am

    #502852

    Try the following:

    USE MyDB

    DBCC SHRINKDATABASE (MyDB, 10)

    BACKUP LOG MyDB WITH TRUNCATE_ONLY

    DBCC SHRINKDATABASE (MyDB, 10)

    See also MS KB http://support.microsoft.com/default.aspx?scid=kb;en-us;272318

    Patrick SIMONS, MCP

  • April 15, 2004 at 1:42 am

    #502857

    Thanks for the reply guys,

    I've tried using the DBCC Shrink + Backup Log .., sometimes it works and the log shrinks but sometimes it's not working and the log file stay the same size.

    I'm thinking if I've full backup the database then I no longer need the information in the log file, right? Can I drop/delete the old log file and create/assign a new log file for the database?

    Also I need to run the stored procedure everyday .. so this problem is recurring, I wonder is there any way to perform insert .. select .. without causing the log file to explode. Can anyone help?

    Thanks again ..

     

  • April 15, 2004 at 3:33 am

    #502865

    I run a batch file which removes our big log file (after a backup of course!) and creates a tiny new one:

    isql.exe /U xxx /P yyy /i detachDB.sql

    DEL "c:\Program Files\Microsoft SQL Server\MSSQL\Data\whatever_log.ldf"

    isql.exe /U xxx /P yyy /i attachDB.sql

    detachDB.sql:

    ----------------------------------------

    USE master

    GO

    EXEC sp_detach_db @dbname = 'whatever'

    use master

    GO

    ----------------------------------------

    attachDB.sql:

    ----------------------------------------

    USE master

    GO

    EXEC sp_attach_db @dbname = 'whatever',

    @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\whatever_data.mdf'

    use master

    GO

    ----------------------------------------

    Just watch out for currently logged in users, running jobs etc.. I was able to get round this by stopping/restarting the SQL services which was OK for our systems.

    Regards,

    Jon A

  • April 15, 2004 at 3:44 am

    #502867

    So if I detach the DB, delete the log file and re-attach the DB, the log file will be created at minimum size?

    Thanks,

    Danny

  • April 15, 2004 at 4:01 am

    #502870

    Yes.  It works for me.

  • June 4, 2004 at 7:57 am

    #509010

    Yeah, so don't do this... not if you have more than one log file anyway. Took me a couple of hours or so to find how to rebuild a logfile from scratch after trying this "solution," as the db would not attach without the log files present.

    For others who find themselves up a creek as a result of trying this, see cchitanu's fix here --

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=18193&p=2

Viewing 8 posts - 1 through 7 (of 7 total)

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