9GB Transaction Log: when is OK to start fresh, and how?

  • I have a 9GB transaction log. I have been thinking about creating a new log with detach and attach single file db.

    The reason is I am stuck trying to analyze log with those Log Rescue tools. But, none of them can actually read old transactions. Since we cannot perform analisys and trying to set up things correctly for the future I just want to delete, get some disk space...

    What is the opinion on this? Is it OK from time to time to create a new Log?

  • Take a look at this blog post I wrote on just this problem - it'll tel lyou the options and how to go about them - http://www.sqlskills.com/blogs/paul/2007/09/24/SearchEngineQA1RunningOutOfTransactionLogSpace.aspx

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Your article is fine, but I am running SQL 2000. On the other hand, I checked for long running transaction and there is none. It seems is just a big Log on a DB with FULL RECOVERY.

    It is weird I restored DB in another server and Log Rescue apps did not see any transactions.

    I will change DB recovery mode. But then, I must ask about my question, 9GB log, when is ok to start fresh?

  • ok - so you can either take (and continue to take) log backups and stay in full recovery mode, or you can change modes and run a dbcc shrinkfile on the log file to shrink it down.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Paul Randal (10/9/2007)


    ok - so you can either take (and continue to take) log backups and stay in full recovery mode, or you can change modes and run a dbcc shrinkfile on the log file to shrink it down.

    Last DBA left a job: DBCC shrink and option WITH TRUNCATE ONLY. While db is still in FULL mode it seems TRUNCATE ONLY is causing some trouble. Although a full backup of DB and Log have been done, softwares to peek at transaction logs are not displaying any data.

    I even tried at my server. Deleted some data, full backup, dbcc shrinkfile ** with truncate only...openes SQL Log Rescue and either there is no data or some columns show as 'unknown'.

    It is like truncating wipes fundamental information.

    I guess I'll just detach and reatach 'single file db' to create a new T Log and from then on implement a nice recovery plan. That way I can get rid of those 9GB. Any advise?

  • Why do you keep going on about SQL Log Rescue? There is no log recovery tool on the market that copes with everything in the SS2005 transaction log.

    What are you trying to achieve here - shrinking the log or recovering data?

    You can't truncate a transaction log that's in full recovery mode and hasn't been backed up.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Are you doing log backups, or just full backups?

    A full backup takes a copy of the data (.mdf) file, and that portion of the log that was used while the full bacup was run. This ensures that a restore can back out any in-flight transactions that existed during the full backup, and leave your database in a consistant state.

    A log backup takes a backup of the log (.ldf) file. It allows old portions of the log to be marked for reuse. After taking a log backup you should see the % log used value decrease (there are some valid reasons why this might not happen).

    Taking a full backup allows you to recover to the last full backup. Taking log backups allows you to use the full backup and log backups to recover to any point in time.

    If you do not need to recover to a point in time, and if there are no regulatory reasons why you might need to keep log backups, then set your database to Simple recovery mode. If you keep it on Full recovery mode you must take log backups.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • If you have a 9GB log in full recovery mode and are using truncate_only I would expect you to have a big empty(ish) file, which is why your log tools cannot see anything.

    Run dbcc sqlperf(logspace) to check how much of the log is actually being used.

    You should find the % used is small, so you can then run a file shrink -

    DBCC shrinkfile(logicalfilenameoflog,size)

    Please dont deatach/reattach single file, it could go horribly wrong.

    HTH

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

  • dandrade (10/9/2007)


    Your article is fine, but I am running SQL 2000. On the other hand, I checked for long running transaction and there is none. It seems is just a big Log on a DB with FULL RECOVERY.

    It is weird I restored DB in another server and Log Rescue apps did not see any transactions.

    I will change DB recovery mode. But then, I must ask about my question, 9GB log, when is ok to start fresh?

    Dandrade,

    You need to expect to get answers relating to SQL 2005 because you posted in a SQL 2005 forum. If you are running SQL 2000 you should have posted in the SQL 2000 Backups forum. That being said George has probably given you the answer as to why the file is staying large and your log reading tools aren't seeing anything. Truncate_Only removes the data from the log, but does not reduce the space allocated for the log file.

  • Doing a full backup will NOT allow log file to shrink. You must in some way backup the log to allow space reuse. This is a VERY common misunderstanding for SQL Server newbies.

    Easiest way to get rid of the 9gb log (assuming the database has only one file) is to take the database offline drop it and then reattach it using sp_attach_single_file_db. This will create a new log file automatically. If you do this you should manually size the new log to an appropriate size.

    Shrinking the log could take a LONG time - again prolly best to stop all db activity.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (10/11/2007)


    Doing a full backup will NOT allow log file to shrink. You must in some way backup the log to allow space reuse. This is a VERY common misunderstanding for SQL Server newbies.

    Easiest way to get rid of the 9gb log (assuming the database has only one file) is to take the database offline drop it and then reattach it using sp_attach_single_file_db. This will create a new log file automatically. If you do this you should manually size the new log to an appropriate size.

    Shrinking the log could take a LONG time - again prolly best to stop all db activity.

    I don't agree with this suggestion. It requires application downtime and could easily go horribly wrong if the newbie does something like shutdown SQL Server without letting the database cleanly shutdown, and then deletes the log file. It's way better to use the tools that SQL Server provides to get out of this situation one time and then manage the log size correctly.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • ...never mind the loss of ability to use point in time restore should newbie destroy sometihng in DB prior to next full backup....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • No one has asked yet, but what is the size of the DB and what is it's growth. Perhaps there are enough transactions to warrant 9GB Log size to prevent it from growing too often? I'm not saying I think that is the case, but it is possible.

    A Backup Log statement and then a DBCC ShrinkFile should take care of it.

    You should not see anything using a log reader after Truncating the log. The truncate clears the log, leaving unused space. From BOL:

    Used in only BACKUP LOG statements, performs a checkpoint to manually force the transaction log to be truncated. NO_LOG and TRUNCATE_ONLY are synonyms. Specifying a backup device is unnecessary because the log is not backed up.

    Under the simple recovery model, performing a checkpoint removes the inactive part of the log without making a backup copy. This truncates the log by discarding all but the active log. This option frees space, but risks possible data loss. After the log is truncated by using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the truncated portion of the log are not recoverable until the next database backup. Therefore, for recovery purposes, after using either of these options, immediately execute BACKUP DATABASE to take a full or differential database backup.

  • TheSQLGuru (10/11/2007)


    Easiest way to get rid of the 9gb log (assuming the database has only one file) is to take the database offline drop it and then reattach it using sp_attach_single_file_db. This will create a new log file automatically. If you do this you should manually size the new log to an appropriate size.

    Shrinking the log could take a LONG time - again prolly best to stop all db activity.

    That is my opinion too.

    Here is the deal, check Post Title, rephrase..."a large log, no way to recover to any point in time, when is a good time to create a new Log File? and How"

    Opinions are divided some favor attach single file db, some do not.

    I have seen many posts here and other places where people have large logs. Mostly from people hosting apps...like DotNetNuke or Rainbow.

    Every now and then someone complains, "my log is too big, my hoster is asking me to pay more, but my data files are not that big, just the log".

    So, if there is no need for recovery, when do you create a new log? Granted, a Simple Recovery Model allows Log to shrink, however most hosters create DB in FULL mode.

    This is not neccesarily related to a particular DB version (2K or 2005). What I intended was to get some opinions, or criterias as to when should a DBA consider it is good time to dump a log file and start fresh.

    BTW, my data file is 2GB and T Log is really 1.5GB (yet shows 9GB), got it from one Log recovery tool, confirmed with dbcc sqlperf.

  • As you mention, SIMPLE recovery mode would eliminate the issue with tlog growth (unless you get a runnaway DML spid). A sql job that regularly (once per week?) runs a job that does "backup log mydb with truncate_only" will enable FULL recovery mode without letting tlog grow unchecked. This can allow point in time recovery upon lost mdf or corruption if you 1) have a full backup and 2) can immediately backup the tlog.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 25 total)

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