January 9, 2007 at 4:57 am
I had killled a long running process that is now rolling back for more than 12 hours and not completed yet. Meanwhile, errors started appearing in SQL error log "Autogrow of file 'dbname_Log.ldf' in database 'dbname' cancelled or timed out after 1875 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size." These errors are showing up every 3-4 minutes and the timeout period is different in each message. The log file is now 75 GB in size and there is enough space available on HDD for it to grow. The database where the rollback is happening is the same. There is no size limit on the data and log files and the increment for size is set as 10%. The autogrow process and rollback of transaction might be slowing each other down. It is SQL 2000 Standard with SP4. I would appreciate any solutions.
January 9, 2007 at 6:04 am
Beware of killing processes without knowing what they are.
If you have backups and/or don't mind the risk, rebooting your server may help.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 9, 2007 at 6:34 am
Won't the server take its time to rollback the transaction after the restart before it makes the db available for use?
January 9, 2007 at 7:06 am
I had the same issue. Maybe this will help
http://support.microsoft.com/kb/822641
January 9, 2007 at 3:47 pm
not always, you'd be surprised - but this is a desperate measure, after all.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 10, 2007 at 1:22 am
This is INFORMATIONAL message added in Service Pack 4.
There might be some operation going on (which caused file to grow) which was cancelled.
If you have profiler for same time, you will see attention event captured in Event Class.
Well I would suggest that instead of the 10% growth give the growth in MB's. beacuse for huge databases 10% growth sometimes is a quite a big size to expand and if transactions are going on then it will be a problem.
January 10, 2007 at 9:19 am
I reduced the increment of size to 500 MB and since then these messages have not been appearing. Thanks for your help!!
January 10, 2007 at 9:10 pm
If this is a production box then autogrow feature takes a toll and hampers the performance during the production time evrytime the database gows. So I would suggesst you to to estimate as to how much the database can grow in the coming year and then allocate it a fixed size so that it will not have to grow automatically and thus will help in improving the perfoamnce also.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply