Transaction log increased in size

[Nstuff]

Hi,

We are using microsoft SQL 7 and there was a little problem. Because of an application the transaction log increased to 5.1gb. We solved the error what increased the log file, but now it keeps 5.1gb reserved for the transaction log.

When i try to shrink the log file from 5gb to 100mb it gives an error that u only can increase the file size.
When we look at allocated space we see that there is only 50 mb used and rest is free space

Can anybody help me with this problem. I want to shrink the transaction log to the needed space and it will go larger when it needs space (it has unrestricted file growth)

Thanks already
(I am not the guy who knows much about it ... but I am just the one who needs to solve the problems my colleges make , but i try to be as detailed as possible)
Nstuff

 

[Bernadette]

HI There

If you right click on your database and select properties and click the options tab is "Truncate Log on Checkpoint" checked. If so then the Transaction Log should truncate itself when a transaction is commited to it.
If it is not checked then there are various ways to shrink it.

1. Take a backup of the Transaction Log.
(When SQL Server finishes backing up the
transaction log, it truncates the inactive portion of
the transaction log. SQL Server then reuses this
truncated, unused space in the transaction log rather
than the transaction log continuing to grow and use
more space. )

2. If that doesnt work ..
In Query Analyser type
DBCC Backup Log <dbname> with TRUNCATE_ONLY

This will force the log to truncate without taking a
backup of it.

Hope This Helps NStuff
Good Luck
Bernadette

 

[tlbroadbent]

Caution, setting &quot;Truncate Log on Checkpoint&quot; On is very bad policy for a production database. It renders transaction log backups useless. It should not be done unless you don't care about recovery of transactions.

Check out the FAQ posted by FOXDEV - faq183-345 - to find more information about shrinking the log and the log file. You can also read the following articles.

Shrinking Active Log Files—Revisited

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9011

Quickly Shrinking the Transaction Log

http://www.swynk.com/friends/krishnan/tranlogshrink.asp

Shrinking the Transaction Log

http://msdn.microsoft.com/library/psdk/sql/8_ar_da2_1uzr.htm

DBCC SHRINKFILE

http://msdn.microsoft.com/library/psdk/sql/ts_dbcc_8b51.htm

Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[SeanJ]

1. Truncate log and backup database
2. Use sp_detach_db <database>, true
3. Rename the large database transaction file in explorer
4. Use sp_attach naming the mdf file location and the new ldf location eg:sp_attach_db @dbname = N'Northwind',
@filename1 = N'c:\mssql7\data\northwnd.mdf',
@filename2 = N'c:\mssql7\data\northwnd_log.ldf'
5. The database is then reattached creating a new ldf file in the location you specified above with a 1MB transaction log, once you know its working OK delete the old ldf file
Regards
Sean