Trucate Transaction Log in SQL Server 2000/2005

In SQL Server, it is important to keep the size of the transaction log at a decent size. If the transaction log fills up your disk drive. It can hang up the database. T-log is like archived redo log files in oracle database. Instead of storing multiple files, SQL server stores the t-log as a single file with extension .ldf . The t-log file consists of multiple virtual files with the log sequence no. ( out of scope for this article 🙂 )
Procedure to shrink transaction log :

C:\> osql -u sa

password: ****

–1)

BACKUP LOG dbname WITH TRUNCATE_ONLY
GO

–2)

USE dbname;
SELECT * FROM sysfiles;
GO

–3)
Then, using the logfile name that sysfiles revealed in the last
step, shrink log size to the –desired size in mb, in this case 500.

USE dbname;
DBCC SHRINKFILE(logfilename,500)
GO

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s