Shrinking the ldf file

Question

Shrinking the ldf file

Chand00

SSCertifiable

Points: 6466
More actions
April 23, 2008 at 8:54 am

#185647

Hi,
In our production server recently we had created a new database 'mydb' in full recovery model. Sizes of Data and Log files are:
DataFile (.mdf) InitialSize: 250mb Autogrow
LogFile (.ldf) InitialSize: 1 gb Autogrow
We were taking only full and diff backups. But recently to decrease the data loss in case of disaster we had started taking the transaction log backups also. But the size of the transaction log backup is almost 1 gb. Upon further investigation we found that the Available free space in the logfile is 90%. So I would like to shrink the size of the logfile. Will shrinking the size of the logfile will decrease the size of the transanctionlog backup. And if so what are the precautions and commands to achieve this without any data loss. This database is continuously in use by our callcenter application as we are into insurance related business. If required we can take a downtime of 15mins. But our mission is to decrease the size of the logfile without any data loss. Please guide me
Thanks
Chandra Mohan
[font="Verdana"]Thanks
Chandra Mohan[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

timscronin SSCertifiable Points: 6851 More actions · Answer 1

After you have backed up the transaction log, you can you the GIU to shrink the file, or you can use dbcc shrinkfle, either will work

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 2

Yes, As Tim said, after taking Tlog backup, you can use DBCC SHRINKFILE command as below

DBCC SHRINKFILE(logfilename, targetsize of the log).

"If there is any open transactions on the database, you can not shrink"

Also you can try the following too to truncate the Transaction log file... But before doing this, take a Tlog backup (to avoid data loss if any failure occurs).

1. BACKUP LOG [database name] WITH TRUNCATE_ONLY

2. DBCC SHRINKFILE (LogfileName, TRUNCATEONLY)

3. (Important)Take a FULL BACKUP of your DATABASE, this will avoid the LSN (log Sequence Number) mismatch for your further Tlog backups.

Hope this make sense to you.

Have a great day...

Warmest

Jay.

Thanks
Jay
http://www.sqldbops.com

tosscrosby SSCoach Points: 18045 More actions · Answer 3

Avoid unneccessary shrinks of the database files. Size the files according to what is appropriate for your business/applicaton (I always error on being bigger than what is needed). Search this site for a TON of related links.

-- You can't be late until you show up.