How to Shrink Large Logfile - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to Shrink Large Logfile

Author

Topic

kennetu
Starting Member

33 Posts

Posted - 2009-10-08 : 06:44:25

can someone help me with the procedures on how to shrink large logfiles?

Thanks.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-10-08 : 07:50:26

Why do you want to shrink the log file?

Full recovery model? Are you doing log backups?

--
Gail Shaw
SQL Server MVP

kennetu
Starting Member

33 Posts

Posted - 2009-10-08 : 08:01:29

Yes, I am doing log backup.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-10-08 : 10:58:04

Then why do you want to shrink the log files? Unless they are far bigger than they need to be for regular operation, they'll just grow again. That'll slow down the DB while the growth occurs.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-08 : 13:19:48

Their protestations are because you shouldn't constantly shrink the log if it's an appropriate size...growing and shrink the files will cause overhead and slow down your app

With that knowledge



BACKUP DATABASE [<db_Name>] 
			 TO  DISK = N'<File_Path>\<Backup_Name>_Dyyyymmdd_THHMMSS.BAK' 
		   WITH NOFORMAT, NOINIT,  NAME = N'<Backup Name>', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP LOG <dbname> WITH TRUNCATE_ONLY
GO

USE [dba]
GO
DBCC SHRINKDATABASE(N'<db_Name>' )
GO

USE [dba]
GO
DBCC SHRINKFILE (N'<db_Name>_Data' , 0, TRUNCATEONLY)
GO

USE [dba]
GO
DBCC SHRINKFILE (N'<db_Name>_Log' , 0, TRUNCATEONLY)
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

kennetu
Starting Member

33 Posts

Posted - 2009-10-09 : 02:51:39

Yes, the log file was growing too large for the db; so I want to shrink it.
Thanks.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-10-09 : 10:47:57

Then you need to take a log backup not shrink the file, also check on your autogrowth setting for you log file and make sure its not restricted to something stupidly small

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources