can't set the log file to unrestricted growth

[MDCrab]

hello,

I've got an insert statement that keeps failing when I call it from a SQL 2005 stored procedure. (This insert statement attempts to put 22,000 records in a table.)

After I try to call the stored procedure, I check the SQL logs for information. It tells me: "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."

I've tried several things. I've logged in as 'sa', and run this command:

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

It still produces the error. I've also tried:

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

This gives me the same error.

I've also tried to right-click the database name from the 2005 Server Management Console. I click on "files". I click on the button associated with the log file. I indicate that "Enable Autogrowth" is checked. Also, I check off the Restricted Growth checkbox and check on Unrestricted File Growth. I click "OK". Guess what, when I look again at the log file it still says "restricted growth."

I've tried to truncate my log many times, running these commands:

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

The output that appears beneath these commands indicate that the log file is the same size as its minimum size.

So, does anyone have an idea how I can configure my log file so that I can run my insert statement?

thanks.

 

[JayKusch]

Try setting the file growth value to say 100MB, not a percentage.

Questions would be ... How large are the DB files now and how much disc space is available on the drive that houses the log file.

As an example, if there is only 500MB of free space on the disc and you have a log file that is already say 8GB AND you have the autogrow set to grow by 10% then the file will try to grow 800MB which is more than the available disc space of 500MB.



Thanks

J. Kusch

 

[MDCrab]

Hi Jay,

I'll respond to your questions when i return to the office in a few days. thanks.

 

[MDCrab]

Hi Jay,

Sorry for the delay in responding. I was off for nearly a week.

The database size is 2.6 GB. The log file (i.e., LDF file) is only 20 MB. The total storage space free is 268 GB. So, we've got plenty of room for this database.

I went to the database and right-clicked on the name. I chose 'properties'. Then 'files'.

I find the log file. (There is only a database file and a log file.) I click on the 'autogrowth' button. I attempt to make these changes:

Filegrowth/In Megabytes: 100 MB.
Maximum File Size/Unrestricted File Growth.

I click OK. I then click OK and close out the properties window.

I go back to look at it. For some reason, it never saves the unrestricted file growth option. When I return (properties/files), I see:

Autogrowth by 100 MB, restricted file growth: 2,097,152.

I'm rather baffled as to why it won't save 'unrestricted file growth' because I've checked it every time, and I've saved this value about a dozen times now.

Next, from the Query Analyzer window I attempt this SQL statement:

ALTER DATABASE <database name>
MODIFY FILE (NAME = <database log file name>, FILEGROWTH=100MB)

This works; it tells me that "Command completed successfully."

However, this solution doesn't work for me. When I attempt to run my insert statement, the error log tells me: "Autogrow of file '<db log file>' in database '<db name>' 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."

So, I'm wondering what I have to do to set a value for the log file that actually saves when I click "OK". I'm also wondering what can I try next.

thanks for the help.

 

[JayKusch]

Is this a user DB or a DB used by a 3rd party app or tool?

Thanks

J. Kusch

 

[MDCrab]

Hi Jay,

It is a user DB. It isn't used by a 3rd party app or tool.

I created the structure. I populate it with a series of insert statements. The insert statements are called by an ASP script which connects to the database.

thanks.

 

[MDCrab]

Hi,

I'm happy to report that I've made a breakthrough and my problem appears to be resolved. Here are the steps I took:

1. I unattached the database in question and renamed the log file. Then I reattached the database. This created a new log file.

2. with the new log file I am able to specify "unrestricted growth".

3. For my particular routine to run, I prepare my database by doing two things:

1. truncate the log file;
2. run an Alter database statement like so:

ALTER DATABASE [dbname]
MODIFY FILE (NAME = '[db_log_file]', Size=50MB, FILEGROWTH=10%, MAXSIZE=UNLIMITED)
GO

By specifying a pretty large initial size for the log file (i.e., 50 MB), the stored procedure doesn't seeem to need to call for file growth, which was giving me the errors.

thanks for the help.