Production issue

Question

Production issue

madhu-686862

SSChampion

Points: 10707
More actions
December 2, 2008 at 12:50 pm

#196762

Hi,
We have an issue with the application. users having difficulty trying to issue policies from our Production database.
I found a message in the error log as below which is appeared first time . Is this will be an issue? how to correct this?
Autogrow of file 'ABC_dat' in database 'abc' cancelled or timed out after 30056 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.
thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

Two things.

Autogrow should not be left on a percentage (the default) as that causes massive grows for larger databases, with the resulting time problems and poor performance during the grow.

You should be managing your database's file size manually, with autogrow enabled just in case something slips.

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

madhu-686862 SSChampion Points: 10707 More actions · Answer 2

thanks Gail,

The automatically grow file option is enabled and the file growth by percent 10 is enabled for all production databases.

I never get this timeout problem. So whatwill be the immediate step I can take now?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Set the database file autogrowth in increments like 100 MB, 200 MB based on what is suitable and acceptable as per your server disk subystem.

MJ

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

As I said, firstly, the growth should not be 10%. That's a bad default and it gets out of hand on larger databases. Change it to a fixed size (depending on the size of your DB, how fast it grows and what your disks can handle)

Secondly, you should be monitoring your database's size and growing it manually before the autogrow kicks in.

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

madhu-686862 SSChampion Points: 10707 More actions · Answer 5

thanks,

I have lot of disk space left where the datafile exist, so how this auto grow option effects to cause issues. and how to monitor the database growth. could you plz explain a little more on this

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

It's got nothing to do with space available. Grows are very intensive operations and will cause the entire system to slow down due to the IO impact.

As for monitoring, try sp_spaceused

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

madhu-686862 SSChampion Points: 10707 More actions · Answer 7

I tried with sp_spaceused in the database causing the issue. Below are the results

databasename database_size unallocated space

ABC 4466.00 MB 311.97 MB

reserved data indexsize unused

3946528 KB481016 KB 4104 KB 3461408 KB

So what can I do now?

thanks in advance

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

Monitor that regularly and when the available space gets low, manually grow the file.

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

madhu-686862 SSChampion Points: 10707 More actions · Answer 9

thanks Gail,

so right now the available space(unallocated space) 311.97 MB. So it is decreased further, I need to manually increase the datafile right?

for that Do I need to follow this step:

To change the default values provided in the File name, Location, Space allocated (MB), and Filegroup (not applicable for the transaction log) columns, click the cell to change and enter the new value.

For existing files, only the Space allocated (MB) value can be changed; the new value must be larger than the existing value.

In the screen shot,I need to change in the cloumn 'Spaceallocated' to a larger value?

plz advice me

madhu-686862 SSChampion Points: 10707 More actions · Answer 10

I guess it would be fine if the autogrow set to 5% instaed of 10% as the error log saying that use alter database to set to a smaller filegrowth. plz advice me.

thanks

madhu-686862 SSChampion Points: 10707 More actions · Answer 11

could you plz provide me a script to monitor the datafile size and log file size

Thanks

Madhu

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 12

Autogrowth is recommended be a fixed size instead of a percentage.

A monitoring script for database-size should be in the scripts-sections.