Re: transaction log file

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 05/25/04 

Date: Tue, 25 May 2004 11:44:04 -0400

Do you need full recovery model? If not, you can reduce growth in the long
run by using simple or bulk logged. In the meantime, you can apply a
band-aid by:

BACKUP LOG db_name WITH TRUNCATE_ONLY
GO

Then executing DBCC SHRINKFILE against the log file. See sp_helpfile to get
the logical file name for the log, and see books online for usage of DBCC
SHRINKFILE. If you still can't shrink the log, there is likely an open
transaction; try DBCC OPENTRAN and it will tell you who the user is, and you
can track them through sp_who2 etc. and find out if you can just kill them
or if it is something you need to wait on.

See http://www.aspfaq.com/2471 and http://www.aspfaq.com/2446 (the latter is
*not* solely for tempdb).

But, if you don't change anything else, it will just get that big again in
the future... 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"mike" <anonymous@discussions.microsoft.com> wrote in message
news:109D1D3A-C36E-40C7-99C0-E965ACD468AA@microsoft.com...
> Hello,
> Here's my problem: I have a sql server 2000, the transaction log for the
database is about 9GB and it's getting bigger, running out of space on the
HDD ang got the message: " the log file for database 'dbname' is full. back
up the transaction log for the database to free up some log space." Recovery
mode for the database is set to Full. How can I empty the transaction log,
or shrink it like a lot!!!
> Thanks!

Relevant Pages

  • Re: transaction log
    ... It seems your Recovery model set to that database is "FULL". ... So we need to scdule a transaction log backup regularly (atleast hourly ...
    (microsoft.public.sqlserver.server)
  • Re: Regarding Transaction Log
    ... All modifications are logged in the transaction log. ... If you are running the database in full recovery model, ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: transaction log file
    ... Replication uses the log to make its changes. ... Pro SQL Server 2000 Database Design ... > Then executing DBCC SHRINKFILE against the log file. ... I have a sql server 2000, the transaction log for the ...
    (microsoft.public.sqlserver.programming)
  • ALTER DATABASE (optimization job)
    ... Recovery model FULL ... the transaction log on my ... >1) Before the optimization job start backup the database ...
    (microsoft.public.sqlserver.programming)
  • Re: Large transaction log causing write performance hit
    ... model or consider moving the transaction log to a separate disk where there ... A good recovery model depends on whether you want up to the minute restores ... The frequent transaction log backups or truncations will keep the size down ... then consider setting your database ...
    (microsoft.public.sqlserver.server)
Loading