RE: Transaction Log Size

From: John Bandettini (JohnBandettini_at_discussions.microsoft.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 03:49:10 -0800

Steven

here are some articles on how to shrink your transaction log.

INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650

INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318

http://www.mssqlserver.com/faq/logs-shrinklog.asp

I would guess that you have your database/databases in Full recovery mode
and you have not been performing Transaction log backups.

You need to either set them to simple recovery mode or schedule regular
transaction log backups.

To check the recovery mode of your databases, right click on the database in
Enterprise Manager and choose options. You can chnage the mode from the
window that will open.

To perform transaction log backups (and database ones) you can use a wizard
to set them up or you can create jobs manually and use the backup command.
(See BOL for format)

Hope this helps

John

"Steven Hutchinson" wrote:

> Is it possible to reduce the size of the transaction log for a specific
> database. At the moment one of our databases has a database of size of 16Gb
> and a transaction log of 43Gb!!
>
> As you might guess I am new to SQL Server but this doesn't seem right?
>
> Any help would be greatly appreciated..
>
>
>

Relevant Pages

  • Re: Transaction log problems
    ... IF you have to shrink a database or file it ... sure that you get that database backup on tape or to another machine. ... > We are using SQL Server 2000 on Windows Server 2003 Standard Edition, ... > One of the databases has recently begun showing large growth of the> transaction log and it is causing us some problems. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log keeps growing
    ... Introduced in SQL Server 7.0 was the ability automatically grow and to ... shrink the physical size of database data and transaction log files. ...
    (comp.databases.ms-sqlserver)
  • Re: Shrinking MDF and LDF Files
    ... written a stored procedure that will delete all the data in all tables and ... With the database empty I'd like to SHRINK the database so the files are ... State what version of SQL Server you are using and specify the content ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Transaction Log Size
    ... listed in my article has detailed info on shrinking of the transaction log. ... Tibor Karaszi, SQL Server MVP ... > (database + transaction log) with fake appended to the name. ... >> CommVault to backup the backup files. ...
    (microsoft.public.sqlserver.server)
  • Transaction log problems
    ... We are using SQL Server 2000 on Windows Server 2003 Standard Edition, ... transaction log and it is causing us some problems. ... Because the database is used by the third party software, ... transaction log file and I would not have to monitor it so closely. ...
    (microsoft.public.sqlserver.server)
Quantcast