Issue Upgrading SQL Server Database to v10.x
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=29733
Printed Date: 31 March 2026 at 1:18pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Issue Upgrading SQL Server Database to v10.x
Posted By: txwaterfowler
Subject: Issue Upgrading SQL Server Database to v10.x
Date Posted: 05 September 2011 at 8:19am
|
I'm consistently getting the following error or some variation when trying to update SQL2000 database from v9.71 to either v10.02 or v10.03. I confirmed with the host that I have dbo rights on the database. Is there a way to increase the timeout?? --------------------------- Your Database is being updated. Please be patient as this may take a few minutes to complete. Stage 1: Creating New Database Tables and Fields..... Error Altering the Table tblThread Timeout expired.Complete Stage 2: Entering default values for new fields..... Complete Stage 3: Migrating forum configuration settings to new format..... Complete Stage 4: Migrate Post Count to new Point System (this may take some time to complete)..... Complete Timeout expired SQL Server database is updated, but with Error! |
Replies:
Posted By: txwaterfowler
Date Posted: 05 September 2011 at 8:33am
|
Another variation after a database restore ... -------------------------------- Your Database is being updated. Please be patient as this may take a few minutes to complete. Stage 1: Creating New Database Tables and Fields..... Error Altering the Table tblTopic Timeout expired. Error Altering the Table tblThread Timeout expired.Complete Stage 2: Entering default values for new fields..... Complete Stage 3: Migrating forum configuration settings to new format..... Complete Stage 4: Migrate Post Count to new Point System (this may take some time to complete)..... Complete Timeout expired SQL Server database is updated, but with Error! |
Posted By: WebWiz-Bruce
Date Posted: 05 September 2011 at 8:38am
|
It would appear to be a time out with the database connection. This would be set on the database server and you would need to get your SQL Server DB Admin to increase the timeout for database queries. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: txwaterfowler
Date Posted: 05 September 2011 at 10:45pm
|
Thanks for the quick response. My host is telling me they will not change the timeout on the shared database server. Any other option to keep retrying the table updates?
|
Posted By: WebWiz-Bruce
Date Posted: 06 September 2011 at 8:37am
|
I presume that either your database has allot of topics and posts or the timeout is set very low on the database. There are new fields added to the tables tblTopic and tblThread which means that the database table would need to be rebuilt when the fields are added. If the timeout on the database server is set to low to add these new fields then you would get these errors. If your host can not change the timeout the only options are to do the upgrade on another database server. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: txwaterfowler
Date Posted: 08 September 2011 at 3:02am
|
The host has tried a few things but still unsuccessful thus far ... I've had this forum on SQL2K since 2004 ... successfully upgraded a several times until now. Topics - approx 22,000 Posts - approx 265,000
|
Posted By: WebWiz-Bruce
Date Posted: 08 September 2011 at 10:59am
|
In previous upgrades your database was probably much smaller. Unfortunately the only solution would be to either install everything on your local PC or find another host that can give you more resources. We are a web host so you could host with us if you wished to and we could then also do the upgrade for you. Also as we use SQL Server 2008 R2 it means that you would be able to use the server side paging which is not available in SQL Server 2000. A forum of your size really needs to the server side paging. We can host your whole website and forum under our http://www.webwiz.net/web-hosting/" rel="nofollow - Web Site Hosting , or just your forum under our http://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting . ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: hummelmose
Date Posted: 15 April 2012 at 4:44pm
|
Hi. I'm an administrator on a WebWiz 9.7 forum, who wishes to upgrade. We're running SQL server 2005, and have administrative rights on our database. Allthough script timeout is set to 0 (no timeout) we get the following error: I've triede 10 times, and gets the same error every time. http://dl.drop.com/u/65950/15-04-2012%2017-37-12.png - http://dl.drop.com/u/65950/15-04-2012%2017-37-12.png
|
Posted By: WebWiz-Bruce
Date Posted: 15 April 2012 at 4:56pm
|
The link to the image did not work, but I am guessing the time out is a database timeout rather than a script timeout. You would need to increase the database query timeout on the SQL Server so that the new fields can be added to the tables. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: hummelmose
Date Posted: 15 April 2012 at 5:03pm
|
It's the same error described above. http://dl.dropbox.com/u/65950/bruce.png As written I've set timeout to 0 (no timeout)
|
Posted By: WebWiz-Bruce
Date Posted: 15 April 2012 at 5:09pm
|
Then my previous reply to you was correct, this is a database timeout and you need to have the database admin increase the query timeout on the database server. When new fields are added to a table SQL Server will have to rebuild the table with the new fields. This can take some time and if the database is quite large it can take longer than queries are allowed to take giving the database timeout that you see. This is database server side rather than on the web server side, so increasing the script timeout would not fix the issue. Failing that you would need to attempt to create the new fields in the tblThread table manually. The fields that you would need to create the tblThread are:- 1. Answer - datatype = bit - default value = 0 2. Thanks - data type = integer - default value = 0 ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: hummelmose
Date Posted: 15 April 2012 at 5:12pm
|
I'm trying to attach images. But you wont let me. Look at my SQL Server settings: http://dl.dropbox.com/u/65950/bruce2.png http://dl.dropbox.com/u/65950/bruce3.png
So I still can't understand why I get a timeout. If you mean something else, please explain what else to set different on the sql server
|
Posted By: WebWiz-Bruce
Date Posted: 15 April 2012 at 5:17pm
|
You will need to create the new fields in tblThread manually using the info I gave in the previous post as there is nothing that can be changed in the upgrade script to try and fix this issue, your database is simply to large for web server side upgrade scripts to work. The other tables will be upgraded fine, it will just be tblThread that you need to manually update. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: hummelmose
Date Posted: 15 April 2012 at 5:37pm
'tblThread' table - Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Sorry. No cigar. It was done directly on the SQL server via Admin interface. |
WebWiz-Bruce wrote:Posted By: rune
Date Posted: 15 April 2012 at 6:22pm
|
Sounds like some other program has a lock on the table. The brute-force way to fix that is to restart the sql server. Bruce: the database does not normally time out just because the table is big. Only if it is overloaded _and_ has timeout specially configured by the admin. On an unloaded server, you can modify a table with hundreds of thousands of rows in a few seconds. |
Posted By: hummelmose
Date Posted: 15 April 2012 at 8:12pm
It's a fresh restore, so no one has lock on the database.
|
Posted By: hummelmose
Date Posted: 15 April 2012 at 8:17pm
Even on a local 64 bit 16 GB SSD based machine I get the timeouts. Yes, we have a big forum, but should that prevent us from upgrading ? 1062378 rows in thread 101481 rows in topics |
Posted By: rune
Date Posted: 15 April 2012 at 8:25pm
|
It might be worth checking the database consistency with the DBCC CHECKDB command. |
Posted By: hummelmose
Date Posted: 15 April 2012 at 8:32pm
CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxx_forum10'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. I will now try to reindex
|
Posted By: rune
Date Posted: 15 April 2012 at 8:39pm
|
If the zipped db backup can fit in your Dropbox, PM me a link and I'll take a look. Obviously, only if your forum data is not secret. |
Posted By: hummelmose
Date Posted: 15 April 2012 at 8:44pm
Thanks, but no thanks :-) We're quite a big forum. I tried to reindex the whole database with EXEC [sp_MSforeachtable] @command1 = "RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')" The reindexing went well, but still after reindexing it all. Still timeout. 'tblTopic' table saved successfully 'tblAuthor' table saved successfully 'tblThread' table - Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. |
Posted By: rune
Date Posted: 15 April 2012 at 8:55pm
|
It sounds like you use the user interface to add the fields. What happens if you use a command instead: alter tblThread add Answer bit not null default 0 and alter tblThread add Thanks int not null default 0 (These commands are based on Bruce's description of the fields. I haven't checked them myself). |
Posted By: hummelmose
Date Posted: 15 April 2012 at 9:00pm
See  That did the trick. Mange tak for hjælpen. Now let's see if the rest of the upgrade will continue
|
Posted By: hummelmose
Date Posted: 15 April 2012 at 9:05pm
|
Your Database is being updated. Please be patient as this may take a few minutes to complete. |