February 17, 2009 at 10:16 am
Hello,
I'm having trouble setting the autogrowth property of a transaction log for a particular database. I have posted my problem on another forum. However, I haven't received a remedy yet there so I thought I would try posting here.
I am our company's SQL Admin. I created the database in question. I populate it using an ASP script that connects to it. I have a particular stored procedure that inserts 25,000 records into one of the tables. When I run this stored procedure I receive errors concerning the transaction log's size.
(The database size is currently 2.6 GB. The log file ranges from 7.5 MB to 20 MB in size. There is plenty of room on the hard disk; 250 GB are free. )
I have repeatedly tried to manually set the filegrowth of the log file to no avail. For some reason, MS SQL 2005 will not save my settings.
When I right-click on the database name, then click 'properties', choose 'files', I see two files. They correspond with the MDF and LDF files for the database.
I look for the Autogrowth heading and look at what it says for the log file. "[Autogrowth...] by 100 MB, restricted growth to 2,097,152."
I click the Autogrowth button for the log file. Under Maximum File Size I attempt to check Unrestricted file growth. I click OK. On the Database Properties window the text corresponding with the log file temporarily says: "By 100 MB, unrestricted growth." I click OK, thinking that I'm good to go.
I return to the same place. (Right-click the database name, choose 'properties', then 'files'.) Guess what. When I return to the Database Properties window, it says "By 100 MB, restricted growth to 2,097,152."
So, I can't set the autogrowth property here. I attempt to do so in Query Analyzer.
I've tried these statements, and they have all successfully executed. ( ):
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)
Now, after running each one of those commands I have attempted to run the stored procedure that inserts 25,000 records. Each time I do this the stored procedure terminates. When I check the SQL log file I get variations of this error message:
"Autogrow of file [dbname_log] in database [dbname] was cancelled by user or timed out after 407 miliseconds. Use Alter Database to set a smaller filegrowth value for this file or explicity set a new size."
or:
"Autogrow of file [dbname_log] in database [dbname] was cancelled by user or timed out after 1968 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."
Unfortunately, I don't know what else to try. Does anyone have any advice?
thanks.
February 17, 2009 at 12:38 pm
Scott,
In the properties tab you say it is displaying 100MB growth restricted to 2,097,152. I think that is the maximum space available on the drive.
Unlimited filegrowth is only in the sense your data/log files can grow to 100% of your disk space available( as it says from the properties) and not INFINITE size for the files.
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
February 17, 2009 at 1:02 pm
I believe you can only grow the transaction log once per transaction, so if you are tryin gto insert all of those records in one shot, the log may grow, and then need to grow again and since it can't, it will roll everything back. try manually increasing the size of the mdf file and then importing your data to see if that helps things.
-Luke.
February 17, 2009 at 1:03 pm
Hi,
I unattached the database and renamed the log file to something else. Then I reattached the database. This created a new log file.
With the new log file I can specify:
Enable Autogrowth (checked);
File Growth in megabytes: 20 mb;
Maximum file size: Unrestricted file growth.
I'm glad that I killed off that other log file. However, I'm still getting an error when I attempt to run the stored procedure (with its large insert statement). The error is:
Autogrow of file 'SMART_log' in database 'SMART' was cancelled by user or timed out after 375 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
When I look at how much disk space is available (where the log and the database files both are), it says 300 GB.
I'm still playing with these settings, trying to avoid the error. thanks.
February 17, 2009 at 1:07 pm
cafescott (2/17/2009)
I look for the Autogrowth heading and look at what it says for the log file. "[Autogrowth...] by 100 MB, restricted growth to 2,097,152."I click the Autogrowth button for the log file. Under Maximum File Size I attempt to check Unrestricted file growth. I click OK. On the Database Properties window the text corresponding with the log file temporarily says: "By 100 MB, unrestricted growth." I click OK, thinking that I'm good to go.
I return to the same place. (Right-click the database name, choose 'properties', then 'files'.) Guess what. When I return to the Database Properties window, it says "By 100 MB, restricted growth to 2,097,152."
The max permissible size for a log file is 2 terabytes, which is exactly what the max size is changing to. If you feel that your log will need to grow beyond that, you'll have to add another log file.
Data files are limited to 16 terabytes, logs to 2 terabytes.
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
February 17, 2009 at 1:09 pm
cafescott (2/17/2009)
I'm still playing with these settings, trying to avoid the error. thanks.
The error's saying that it's taking too long to grow the file by the required 20 MB. Either reduce that number or figure out why expanding a file by 20 MB is taking over 5 minutes. I would guess IO bottlenecks.
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
February 17, 2009 at 1:10 pm
Decrease the Autogrow from100MB to say 10% or a smaller size in MB. Also allocate a larger file size to begin with that way the transaction will not have to wait on your database to grow.
A transaction will be only rolled back if there is not enough space for the log file to grow on disk. That would be failing with an error lik ' Disk Space Not Enough Error 112'. In your case it is the setting where in you are asking it to grow by too much. I would suggest increase the allocated size by like 500MB or 1Gb and this should work fine when you run the SP
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
February 17, 2009 at 1:31 pm
I'm very pleased to report that your suggestion is working. 😀
Thanks Gail, Luke and The_SQL_DBA.
I find that before I run the stored procedure, if I do these things:
1. shrink the log file;
2. do an alter database command and specify 50 MB as the starting size for the log file:
ALTER DATABASE [dbname]
MODIFY FILE (NAME = '[db_log_name]', Size=50MB, FILEGROWTH=10%, MAXSIZE=UNLIMITED)
GO
...this allows my stored procedure to run. I'm really glad to finally see those records in that table!!
Since the stored procedure is actually just a small part of what my routine has to do, i am sure I am going to have to keep experimenting with these values.
However, I am so happy that I finally made the breakthrough.
thanks again for the help. 🙂
February 17, 2009 at 1:37 pm
It's not the best idea to keep shrinking and expanding your log unless you really need to. If your log needs to be that large, then just keep it that size. It will save you performance issues down the road if the db keeps needing to autogrow. best to be able to plan when you want to do it, not just have it happen.
-Luke.
February 17, 2009 at 1:43 pm
Luke,
thanks for the tip. I'm going to try to follow your advice. Once this database is in production, I won't be able to shrink and resize the log anyways.
thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply