Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

Question

Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

Viewing 13 posts - 16 through 27 (of 27 total)

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 1

Ok, Thank you I put for both 100 MB. I have for data 4,307 MB and for log 2MB. Do I keep it like this for now? Also I checked data and logs for Enable Autogrowth to true and Unrestricted file Growth and when I save for Logs it goes back to resticted File Growth 2,097,152. Why is it going back to restricted?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Krasavita (7/2/2009)
Ok, Thank you I put for both 100 MB. I have for data 4,307 MB and for log 2MB. Do I keep it like this for now? Also I checked data and logs for Enable Autogrowth to true and Unrestricted file Growth and when I save for Logs it goes back to resticted File Growth 2,097,152. Why is it going back to restricted?

Think of it as unrestricted. the max size of the transaction log is 2 TB, and that is what that value is. I would increase the size of your t-log, perhaps change it from 2 MB to 100 MB as well.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Krasavita (7/2/2009)
Ok, Thank you I put for both 100 MB. I have for data 4,307 MB and for log 2MB. Do I keep it like this for now? Also I checked data and logs for Enable Autogrowth to true and Unrestricted file Growth and when I save for Logs it goes back to resticted File Growth 2,097,152. Why is it going back to restricted?

Exactly what Lynn said.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Krasavita (7/2/2009)
Ok, Thank you I put for both 100 MB. I have for data 4,307 MB and for log 2MB. Do I keep it like this for now? Also I checked data and logs for Enable Autogrowth to true and Unrestricted file Growth and when I save for Logs it goes back to resticted File Growth 2,097,152. Why is it going back to restricted?

Your second question, yes. Now you monitor your system and after a while, you make adjustments based on data growth (or non growth).

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

... and no more "shrinking".... it's bad for the system all the way around.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff Moden (7/2/2009)
... and no more "shrinking".... it's bad for the system all the way around.

Agreed!

Krasavita SSC-Insane Points: 23138 More actions · Answer 7

Ok, Under File Section under Logs I changed from 2 to 100 MB,then under autogrowth under FileGrowth, also put 100 MB, then under Maximum put Unrestricted and click save, after I reopen and everything saved except: Goes Back to Restricted 2,097,152 MB under Maximum section, why is that? Thank you

WayneS SSC Guru Points: 95460 More actions · Answer 8

While reading through this thread, it dawned on me that this is something that I failed to check on with my new job. 🙁

Is there a way to tell when / how often a file has grown in the past? There are no logs kept that will tell us that.

Geez, just looked... 6gb file set to autogrow by 1mb... - it's now at 100, but I need to start tracking it's available space history....

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Krasavita (7/2/2009)
Ok, Under File Section under Logs I changed from 2 to 100 MB,then under autogrowth under FileGrowth, also put 100 MB, then under Maximum put Unrestricted and click save, after I reopen and everything saved except: Goes Back to Restricted 2,097,152 MB under Maximum section, why is that? Thank you

Reread my reply to that above (Goes Back to Restricted 2,097,152 MB under Maximum section). It's OKAY. Just to reaffirm, the transaction log file is limited to 2 TB in size, 2,097,152 MB == 2 TB. Therefore being restricted to this size is the same as unrestricted growth.

Krasavita SSC-Insane Points: 23138 More actions · Answer 10

Krasavita

SSC-Insane

Points: 23138

July 2, 2009 at 1:35 pm

#1019434

Thank you

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

WayneS (7/2/2009)
Is there a way to tell when / how often a file has grown in the past?

Default trace, for as far back as it goes.

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

WayneS SSC Guru Points: 95460 More actions · Answer 12

GilaMonster (7/2/2009)
WayneS (7/2/2009)
Is there a way to tell when / how often a file has grown in the past?
Default trace, for as far back as it goes.

Thanks Gail!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

WayneS (7/2/2009)
GilaMonster (7/2/2009)
WayneS (7/2/2009)
Is there a way to tell when / how often a file has grown in the past?
Default trace, for as far back as it goes.
Thanks Gail!

Right-click on the database, navigate to Reports, Standard Reports and select the report for Disk Usage. Not only will this show you how much space is available in the data files - it includes all of the previous autogrowth events for both the log and data files.

Well, at least as far back as the default trace contains - because that is where it gets the data.

If you run Profiler when you run the report you can capture the SQL that the report uses to get the data.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs