sql transaction logs

Results 1 to 2 of 2

Thread: sql transaction logs

  1. #1
    Join Date
    Dec 1969

    Default sql transaction logs

    I have a 10 mb database with 10 mb log file which is full and the website is giving log full errors.<BR>Is there any uncomplicated way to shrink the transaction logs on sql 2000. I have knwon that sql 2000 shrinks the transaction logs on taking their backup but i did take a backup of transaction log didnt shrink the log by even 1 kb . OTher method was detaching the database deleting the log file and then attachin the database. I tried that one. And some transaction log header error didnt allow me to attach the db. So i dont have the guts to try that option .<BR><BR>Any suggestion <BR>brij <BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: sql transaction logs

    First of all, making a database backup will not shrink your transaction log. What it will do is _truncate_ the transaction log. This means that the physical space of the logfiles will remain the same, but within these log files there will be space available for more transactionlog records.<BR><BR>If you really want to shrink you log, because you need the disk space for something else, you can do that with a DBCC command (see Books-online). but I don&#039;t think that is what you want, you probably just want more space in your log for more transactionlog records. This is done after the bacup is complete, or you can set the Recovery model to &#039;Simple&#039; (SQL 2000) or set &#039;Truncate log on checkpoint&#039; on (SQL 7). This will truncate the log periodically. Do this in the Enterprise Manager - Database properties - Options. Here (but on the transaction logs tab) you can also specify to automaticcaly grow the logs when neccessary. Then you will never get an error "log full&#039;, except when your harddisk is full...<BR><BR>If you cannot shrink the database, maybe there is an unfinished, long-living transaction in the beginning of your logfile. Try closing all connections to the database and then shrink the database or logfile(s).<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts