LDF File size too big - 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

General SQL Server Forums

New to SQL Server Administration

LDF File size too big

Author

Topic

amit9004
Starting Member

1 Post

Posted - 2011-08-18 : 03:34:35

Hi,

I am facing problem in my database server yesterday suddenly our ldf file is increase upto 250Gb and our database size is 4 GB. I have shrink but its not working. I don't have any space in drive bcoz i have shifted my lof file to other drive then its again take a backup of ldf file upto 250Gb.

Problem is that i can't increase of our HDD Space.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-18 : 04:16:06

Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-08-22 : 16:03:01

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

For SQL server 2005:

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

For shrinking log file in sql server 2008, first u have to change the recovery model of database to simple,shrink the log file and then change back to the previous recovery model. following is the code

USE dbname;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE dbname
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1); -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE dbname
SET RECOVERY FULL;
GO

read this to understand the log file growth
http://sqlserverpedia.com/blog/sql-server-management/backup-log-with-truncate_only/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-22 : 16:33:37

Please don't do that to a database. Breaks the log chain, leaves you unable to do point-in-time restores, forces the log to grow (expensive) next time there's a modification and probably fragments the log severely. It's about the worst form of log mismanagement possible.

--
Gail Shaw
SQL Server MVP

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-08-22 : 16:43:13

Hi GilaMonster,

I understand it does't fix the underlying problem, log will grow again. But timebeing we can shrink log and take full backup..right.

but what if there is no free space in disk.. and moreover why is auto shrink/shrink option is there in SQL server. Please correct me if I am wrong. Thank you.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-23 : 05:09:38

Your code is fixing symptoms (and in a very bad way), not addressing root cause. I posted that mainly because you didn't mention that it's a temporary solution, or that there are side effects or that it doesn't fix the root cause.

Shrink is there because it is sometimes necessary. If a file is much larger than necessary (due to archiving or large operations) then a once off shrink to a reasonable size is a good idea. Not a shrink to as small as possible, a shrink to a reasonable size.

Autoshrink is solely there for backward compatibility. It should have been removed a long time ago.

--
Gail Shaw
SQL Server MVP

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-08-24 : 15:44:49

Autoshrink should have never been introduced...

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources