LDF Size - 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)

LDF Size

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-11-30 : 18:40:18

I have setup the LDF file to Restricted growth and autogrowth setup is 10 percent Restricted Growth and Initial Size is 43551 MB.

Now, Currently LDF size is 44 GB.

How can it be possible the to Grow the LDF to 44 GB when it is set to RESTRICTED GROWTH?

Can anyone explain this phenomenon.

Thanks All.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-30 : 22:54:23

1. Restricted to what max size?
2. How are you measuring .ldf size? Windows reports an estimate if you're using windows explorer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-30 : 23:07:14

Show us the output of: sp_helpdb 'yourDbNameGoesHere'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-11-30 : 23:42:38

Thanks All,

db_size 183482.50 MB
owner SQLDIST01\Administrator
dbid 18
created Apr 30 2009
status =ONLINE
Updateability=READ_WRITE,
compatibility_level
Recovery=FULL,
Version=611,
Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52,
IsAutoCreateStatistics,
IsAutoUpdateStatistics 90

MDF: 176962368 KB
LDF: 10923712 KB

Maxsize
MDF: Unlimited
LDF: 2147483648 KB

Growth

MDF:10 %
LDF:10 %

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-30 : 23:43:54

You did not specify a maxsize for the LDF, hence the growth. Both the mdf and the ldf are set to unrestricted growth since the maxsize setting hasn't been set for either.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-12-01 : 05:28:12

Thanks Tara.

1.MDF is set Unrestricted growth and LDF is Restricted with 10 Percent increase.

AUTOGROWTH: LDF

File 10 percent

DB Restricted

The Orginal settings were not changed.

However, LDF has grown to 44 GB, which couldn't understood.

2.I tried to change while database is not transacted by users such as Changing LDF RRESTRICTED to UNRESTRICTED GROWTH but it is not changing at all.

The Autogrowth Window when changed to Unrestricted it comes back with Restricted only.

I have recreated the database with both MDF and LDF to UNRESTRICTED and restored from backup then also it comes back to Restricted for LDF.

Can you suggest how to change the LDF to UNRESTRICTED GROWTH.

3. How to calculate MDF and LDF Sizes for the database size of 200GB?

I thank for your expertise answers.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-12-01 : 05:35:43

Because your ldf is set to a maxsize of 2147483648 KB which equates to 2048 gig, therefore it capable of growing one hell of a lot larger than 44 gig

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-12-01 : 16:45:50

Then Please let me know as to how to setup the Logfile LDF to UNRESTRICTED, as I do in the AUTOGROWTH.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-01 : 16:47:22

Ignore what the GUI says for the LDF file. When it says 2147483648 KB that means unrestricted as that's the largest number.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-12-01 : 17:55:48

Thanks All

But Can you educate me how to create Database with LDF as UNRESTRICTED

OR

How to Change the LDF's Maxfilegrowth to UNRESTRICTED

When I change this under the Change Autogrowth for LDF logFile it allows me to change but when I open next time it comes back to Restricted mode.

I have created afresh Database on prior taking backup and restore on this new DB with MDF and LDF settings for UNRESTRICTED but on restore it came back with Restricted Setting for LDF.

I have used these T-SQL statements to grow them.

1.

ALTER DATABASE [dbname]
MODIFY FILE (NAME = [dbname_log], Filegrowth=10%)

2.

ALTER DATABASE [dbname]
MODIFY FILE (NAME = [dbname_log], MAXSIZE=UNLIMITED)

3.

ALTER DATABASE
MODIFY FILE (NAME = [dbname_log], FILEGROWTH=100MB)

4. I should add that I have repeatedly truncated the log with this statement:

Use [dbname]
GO
DBCC Shrinkfile('[dbname_log]', 1)
BACKUP Log [dbname] with TRUNCATE ONLY
DBCC Shrinkfile('[dbname_log]', 1)

Please help me in this regard

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-01 : 18:26:19

I don't understand why you are truncating the log. That is not a thing you should be doing.

I'll repeat what I said in my last post. Your LDF is already unrestricted since it says 2147483648 KB.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-12-01 : 19:37:45

I have understood the LDF is created now with Unrestricted of size around 2048 GB.

It still says DBname -> Properties -> Files -> Autogrowth-> By 10 percent, restricted Grwoth to 2097152 MB which deviates that it is restricted. ( You said earlier GUI to IGnore)

But,

Can I ask you how I can create a database with LDF as Unrestricted size ?

Perhaps this is more direct I believe.

Thanks for your guidance overall.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-02 : 00:05:20

I think we are going in circles. My answer is still the same. The maximum size is 2147483648 KB, so when it says that in the GUI, it means unrestricted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources