Sql 2k Database Log file Full ... How to empty that

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Abdul Salam
    #1

    Sql 2k Database Log file Full ... How to empty that

    hi,

    my sql database log file has been fulled recently ..... becuase
    there are 55 millions records in main 3 tables .... so how i can empty
    log file ...
    i don't want to attach new log file or save any pervious log info
    .....

    thanks for helping me ... and my company ..

    Abdul Salam
    Sr. DBA + Programmer
    Xebec Groups of Business.
    Tags: None
    • Hugo Kornelis
      #2
      Re: Sql 2k Database Log file Full ... How to empty that

      On 19 May 2004 22:11:30 -0700, Abdul Salam wrote:
      [color=blue]
      >hi,
      >
      > my sql database log file has been fulled recently ..... becuase
      >there are 55 millions records in main 3 tables .... so how i can empty
      >log file ...
      > i don't want to attach new log file or save any pervious log info
      >....
      >
      >thanks for helping me ... and my company ..
      >
      >Abdul Salam
      >Sr. DBA + Programmer
      >Xebec Groups of Business.[/color]

      Hi Abdul,

      If you're talking MS SQL Server, there are various options:

      1. Make a backup of the databse (either full or incremental). After that,
      SQL Server can reuse old log space.
      2.Set the recovery model to "simple". You probably have it set to "full"
      now; this will cause the log file to grow until you take a new backup of
      the database.
      3. Execute BACKUP LOG databasename WITH TRUNCATE_ONLY. This will release
      old log space.

      I recommend that you read up on recovery model in Books Online to fully
      understand the difference between the simple and full recovery models and
      the implications of the TRUNCATE_ONLY option.

      Best, Hugo
      --

      (Remove _NO_ and _SPAM_ to get my e-mail address)

        Comment

        • Dan Guzman
          #3
          Re: Sql 2k Database Log file Full ... How to empty that

          To add to Hugo's response, you should choose a database recovery model based
          on your recovery requirements. If your recovery plan is to simply restore
          from your last full backup, use the SIMPLE recovery model. You can use DBCC
          SHRINKFILE to release unused log space back to the OS after you've set the
          recovery model and removed committed transactions from the log (truncate).

          Your transaction log still needs to be sized to accommodate the largest
          single transaction.

          --
          Hope this helps.

          Dan Guzman
          SQL Server MVP

          "Abdul Salam" <whylanguages@y ahoo.com> wrote in message
          news:18a7dc26.0 405192111.5b98b cef@posting.goo gle.com...[color=blue]
          > hi,
          >
          > my sql database log file has been fulled recently ..... becuase
          > there are 55 millions records in main 3 tables .... so how i can empty
          > log file ...
          > i don't want to attach new log file or save any pervious log info
          > ....
          >
          > thanks for helping me ... and my company ..
          >
          > Abdul Salam
          > Sr. DBA + Programmer
          > Xebec Groups of Business.[/color]


            Comment

            Previous template Next
            Working...