How to shrink transaction log

I have an application which is using SQL 2000 as
database. However, the transaction log is getting larger
and larger. I wouldn't reduce the size. The database
size is 300MB w/ used space 278MB and the transaction log
size is 6803MB w/ used space 290MB.
Is there any idea how to reduce the size of transaction
log?Quoted from Vishal :
You have various options to keep transaction log size manageable:
1.Keep taking transaction log backups at particular intervals this will help
you keep optimal transaction log size. When SQL Server finishes backing up
the transaction log, it automatically truncates the inactive portion of the
transaction log.
2.Keep transaction size as small as possible.
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH, Jens Süßmeyer.
"Ling" <Ling.Ngai@.hk.standardchartered.com> schrieb im Newsbeitrag
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?|||I refer you to a useful post by Andrew kelly
Either change to Simple Mode or issue regular log backups. If your are
issuing timely log backups and it still continues to grow you most likely
have an open transaction stopping the truncation. Use DBCC OPENTRAN to see.
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ling" <Ling.Ngai@.hk.standardchartered.com> wrote in message
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?|||Try using this code.
You will need to change the <logname> and <databasename> with your
transaction log name and the database name. <Desired_final_size_in_MB> is
optional parameter. This script works great and I use it regularly.
DBCC SHRINKFILE (<logname> , <DESIRED_FINAL_SIZE_IN_MB>,TRUNCATEONLY )
go
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
go
drop table mytable
go
CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO
SET NOCOUNT ON
DECLARE @.Index INT
SELECT @.Index = 0
WHILE (@.Index < 20000)
BEGIN
UPDATE MyTable
SET MyField = MyField
WHERE PK = 1 /* Some criteria to restrict to one row. */
SELECT @.Index = @.Index + 1
END
SET NOCOUNT OFF
go
DBCC SHRINKFILE ( <logname> ,<DESIRED_FINAL_SIZE_IN_MB>, truncateonly )
go
BACKUP LOG < databasename > WITH TRUNCATE_ONLY
go
"Ling" <Ling.Ngai@.hk.standardchartered.com> wrote in message
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?