Shrinking log files in SQL 2000

Question

Post reply

Shrinking log files in SQL 2000

Sanish

SSC Rookie

Points: 27
More actions
February 14, 2008 at 1:16 am

#120080

I have a database in SQL 2000 and the ldf file size is very huge(aroung 20 gb). please advice as to how i can reduce the size of the ldf file. also will it affect the db working and performance if i do so.

Viewing 6 posts - 1 through 5 (of 5 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

DBCC SHRINKFILE

( { file_name | file_id }

{ [ , target_size ]

| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]

}

)

e.g.

USE UserDB

GO

DBCC SHRINKFILE (DataFil1, 7)

GO

****************

userdb is databasename

datafil1 is your logfilename which grows to 20 gb

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 2

If a production system, please don't forget to take a full backup prior to shrinking your log file. I would hate for you to need to do a point in time recovery after you the file is shrunk

Marvin Dillard
Senior Consultant
Claraview Inc

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Use the following

backup log with truncate only and then

dbcc shrinkfile(logname,targetsize)

but be careful doing this in PROD environment.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

And after that, take a full database backup.

Backup log with truncate breaks the log recovery chain, meaning you'll no longer be able to restore to a pouint in time after the log truncation, unless you take another full/diff database backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Preet_S SSCrazy Points: 2184 More actions · Answer 5

It begs the question why it has been allowed to grow so large? You should run regular backups of your log file (BACKUP LOG...) otherwise it will keep growing. This will truncate the log file but not shrink it. Use the SHRINK commands posted already to reclaim the disk space, but try not to shrink db files regularly as this will increase fragmentation.

You need to assess what database & log file size is appropriate for your applications and shrink it to that size. If you are performing massive updates regularly, the log file will just grow large again and incur the overhead of having to grow the file again.

With regular log backups SQL Server should be able to reuse the space already assigned to the log file.

If you never intend to use the logs to perform a Point-in-Time restore then consider changing the Recovery model of the db to "Simple".

Hope this helps.