June 29, 2009 at 10:21 am
What is error mean?Autogrow of file 'ActiveInteractionData_log' in database 'ActiveInteractionData' took 105734 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file. Form my db here is what I have:
EnableAutoGrowth set to true
FileGrowth set to true by 10%
RestrictedFileGrowth set to 2,097,152 MB
June 29, 2009 at 10:25 am
It means that you aren't properly managing the size of your database files more or less. Your database files are set to autogrow to prevent the database from running out of space, but as a matter of practice, autogrow should never actually happen on your databases. You should be monitoring the free space in your database files and growing them manually as needed. If you rely on autogrowth only to maintain your database, then you need to use ALTER DATABASE and change the files from a Percentage Growth to a fixed size that is small enough to prevent timeouts from happening. Also check that you have the Instant File Initialization right set for the SQL Service Account, which will allow the datafile to grow without zeroing out the space.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
June 29, 2009 at 10:30 am
One note about your 10% growth setting. This gets to be more expensive the larger the database gets to be. If you have a 500GB database, that would be a 50GB growth of the datafiles. Even if you have the space, that kind of allocation is going to take a long time.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
June 29, 2009 at 10:54 am
It means that you aren't properly managing the size of your database files more or less. Your database files are set to autogrow to prevent the database from running out of space, but as a matter of practice, autogrow should never actually happen on your databases. You should be monitoring the free space in your database files and growing them manually as needed. If you rely on autogrowth only to maintain your database, then you need to use ALTER DATABASE and change the files from a Percentage Growth to a fixed size that is small enough to prevent timeouts from happening. Also check that you have the Instant File Initialization right set for the SQL Service Account, which will allow the datafile to grow without zeroing out the space.
1.How to monitor the free space on the database files?
2.How to check Instant File Initialization?
3.This for Logs:
EnableAutoGrowth set to true
FileGrowth set to true by 10%
RestrictedFileGrowth set to 2,097,152 MB
4.This for data:
EnableAutoGrowth set to true
FileGrowth
In Megabytes 1
In Maximum
Unrestricted File Growth
Please help me to change to right size. Thank you
June 29, 2009 at 10:59 am
Krasavita (6/29/2009)
It means that you aren't properly managing the size of your database files more or less. Your database files are set to autogrow to prevent the database from running out of space, but as a matter of practice, autogrow should never actually happen on your databases. You should be monitoring the free space in your database files and growing them manually as needed. If you rely on autogrowth only to maintain your database, then you need to use ALTER DATABASE and change the files from a Percentage Growth to a fixed size that is small enough to prevent timeouts from happening. Also check that you have the Instant File Initialization right set for the SQL Service Account, which will allow the datafile to grow without zeroing out the space.1.How to monitor the free space on the database files?
2.How to check Instant File Initialization?
3.This for Logs:
EnableAutoGrowth set to true
FileGrowth set to true by 10%
RestrictedFileGrowth set to 2,097,152 MB
4.This for data:
EnableAutoGrowth set to true
FileGrowth
In Megabytes 1
In Maximum
Unrestricted File Growth
Please help me to change to right size. Thank you
I would set a growth size greater than 1 MB. You will end up with a very fragmented mdf file if you have multiple growths with that small size before manually growing the file at a more reasonable size.
June 29, 2009 at 12:32 pm
Krasavita (6/29/2009)
1.How to monitor the free space on the database files?2.How to check Instant File Initialization?
3.This for Logs:
EnableAutoGrowth set to true
FileGrowth set to true by 10%
RestrictedFileGrowth set to 2,097,152 MB
4.This for data:
EnableAutoGrowth set to true
FileGrowth
In Megabytes 1
In Maximum
Unrestricted File Growth
Please help me to change to right size. Thank you
1) You can run the standard report for Disk Usage. This will show you how much space is actually being used for that database. What you really should be doing is capturing this usage on a daily basis and trending the growth so you know how much disk space you are going to need over the next 6 months to a year. This way, you'll know when you need to go to the boss and ask for more disk space.
You want to have enough free space available in the data file for ongoing operations (e.g. daily growth) and also enough available to rebuild your largest index. How much space that is all depends upon your system. As a starting point, you can use 20% available space and monitor from there.
2) If you google for Instant File Initialization you will find plenty of documents describing how this is used and what is required.
3) How large is the transaction log file now? How large is each transaction log backup? You are going to want an autogrowth size that is large enough to handle at least one backup, but not too large that it takes a long time to create.
4) Same as question 3 - except here we want to make sure we have an autogrowth that makes sense for the business. It doesn't make sense to autogrow at 1MB on a 500GB database that is generating 1GB worth of transactions every fifteen minutes. On the other hand, it doesn't make sense to autogrow at 1000MB on a database that is only 500MB and generates less than 10MB of transactions a day.
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
July 2, 2009 at 8:53 am
Thank you for the recomendations. I changed from full to simple and ran DBCC shrink file.
I want to change settings on this db and can you tell me if this correct please.
Here what I will do:
For the data:
EnableAutoGrowth set to true
FileGrowth
In Megabytes 100
In Maximum
Unrestricted File Growth
For the log:
EnableAutoGrowth set to true
FileGrowth
In Megabytes 100
In Maximum
Unrestricted File Growth
Here is what I have now:
For the data:
EnableAutoGrowth set to true
FileGrowth
In Megabytes 1 MB
In Maximum
Unrestricted File Growth
For the log:
EnableAutoGrowth set to true
FileGrowth
In percent 10
In Maximum
restricted File Growth :2,097,152 MB
This is about db:
DB size4307.25 MBUnalocated size 451.01 MB
Reserved 3947704 KBData 2241992 KBindex_size 1687512 KBunused 18200 KB
Can you also explain how do I know what to put under file growth for data and logs.
Thank you
July 2, 2009 at 9:20 am
If you can't help, don't reply:-)
July 2, 2009 at 9:29 am
Krasavita (7/2/2009)
If you can't help, don't reply:-)
Excuse me?? I have been helping, as have several others. We are trying to get you to think critically about the things your are doing. We are trying to get you to research the things you are asking questions about so that you have a better understanding about SQL Server.
Or would you rather everyone just hand everything to you on a silver platter and not worry about understanding what we are telling you?
If the latter, then fine, I'll walk away. If not, and you really want to improve your knowledge and abilities then great.
July 2, 2009 at 9:31 am
I am asking questions that I don't understand, I read material,but I get really confuse. Sorry,that I am not smart like you
July 2, 2009 at 9:45 am
Krasavita (7/2/2009)
I am asking questions that I don't understand, I read material,but I get really confuse. Sorry,that I am not smart like you
But you keep asking the same questions. We can only answer it the same way each time. Try focusing on more specific areas instead of trying to understand the entire picture at once.
You build a house, or a car, or what ever in small steps, not all at once.
July 2, 2009 at 9:47 am
Ok,#1 I don't understand what to put in file growth under MB or percent for data and logs
July 2, 2009 at 9:57 am
Krasavita (7/2/2009)
Ok,#1 I don't understand what to put in file growth under MB or percent for data and logs
For that, we have given you a guideline. We suggested about 100 MB, but the value you actually use needs to be determined by you. You can start with our suggestion if you want. What really needs to be there you have to determine by monitoring your databases growth. You should have enough free space in the database for 3 to 6 months growth without adding space to the database. The growth factor you put on the database is there in case volume increases and causes the database to have to expand unexpectedly. You want it to be large enough so that it does not have to happen frequently.
You don't want to use a percentage, because each time it has to grow automatically the space added increases.
July 2, 2009 at 10:03 am
Krasavita (7/2/2009)
If you can't help, don't reply:-)
Heh... you must like pork chops. The link Lynn gave you was to a thread where Gail Shaw and others "in the know" made some damned good recommendations to you. You should actually take Gails advice on this. 😉
For this thread and based on the size of your database and log, I'd set the growth to 100 MB on both the MDF and the LDF file... just like Gail said in the other thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply