Alter table times out in SMS 2005

[jsteph]

In Server Management Studio 2005, I'm trying to remove a primary key constraint from a table. It keeps timing out. When I did the Connect in sms, I specifically put a timeout of 3600, which should be an hour. But the statment stops with an error saying it timed out after a few minutes.
Is there some other connection that these statements are using?

Also, is there a way...and I know this is a crutch...to do the changes to the table in the 'design view', and then view the actual ddl sql in a script somewhere? Thanks,
--Jim

 

[mrdenny]

The timeout that you set would be the connection time out, the amount of time to wait while attempting to initiate the connection.

The timeout setting when working in the design view is hard coded at a minute or two.

After making your changes in the design mode in the button rows at the top, over to the left will be a button will look like the save button with a little script icon above the "save" disk and to the right. That will give you the script that SSMS is going to run. Click the button and it will bring up the script. Copy the script and click no or cancle, close the design window without commiting your changes and run the script.

When working with large tables it's often better to run a script to do the changes so that you don't have to deal with these anoying timeouts.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jsteph]

Mr Denny,
I tried that and while it did work for removing the PK, I still get a timeout running a dbcc shrinkfile command. This is running right in a query window. It gives the following error:

Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I'm doing this on a test machine, with no other db's running (no other user db's even exist), and no other connections to that db, in fact only 3 connections at all, one for tempdb, master, and my testdb--which is the ssms query window connection.

The test machine has an 80 gig drive, about a 55 gig .mdf file, and after all the OS and all, about 12 gig free space. Could space be the issue? I didn't think sql-server 'shrunk' db's in the same way as, for instance, MS Access--where they simply make a new copy of the file, schlep all the data over, then delete the original. I have shrunk other files larger than this with less free space, so I don't look at that as a possible issue, but I'm not sure.
--Jim

 

[mrdenny]

No SQL Doesn't shrink a file in that method.

Can you post the code that you are using that's giving the error.

I've never seen a DBCC SHRINKFILE command give a timeout error. Not even on a very busy database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jsteph]

Here's the code (names changed to protect the innocent):

use Initech_testdb
dbcc shrinkfile   (Initech_data,40000)

The 40 gig is a guestimate of what it's taking (based on the empty space listing in the ssms alltasks-->shrink dialog), but it's been my experience that if I throw it too small a size, it just shrinks it down to the smallest it can, but won't bomb or error out.

It's been running for over 2 hours now, it hasn't stopped but Activity Monitor shows status 'suspended'. As I hit refresh, the cpu and io are increasing steadily, the wait-type goes back and forth from Writelog to PAGEIOLATCH_EX, the status always stays as suspended though.

The first two times I tried this I just let it run overnight and in the morning the timeout error message was there.

One last detail I just thought of that may inroduce some sort of size/time restriction is that this testdb is using the sql-server 2005 developers edition. I know that some products have size/space limits on developer editions. But there's basically one main table here with about 250 Mil. records, with a smattering of smaller ones, but I'm not adding to that, I'm just shrinking the db after removing a pk and index from the main table.
--Jim

 

[mrdenny]

Developer edition doesn't have a database size limit.

That's what you should see if the cpu and io increasing and the wait type switching.

Try running a dbcc checkdb on the database. Sounds like there is something causing a delay moving a data page from one place of the file to another.

Are there any error messages in the errorlog file or the application log?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jsteph]

Mrdenny,
Wouldn't you know it--I brought up the screen to check the logs--and it just finished successfully!

I don't know why it was failing before. I'm logged into the machine via remote destkop, and I the first two times I would disconnect (but not log off), but this time I left the remote-desktop window open but minimized, so I'm wondering if that had any effect.

I guess 3rd time's a charm.
--Jim

 

[mrdenny]

It could, depending on how RDP is setup to handle disconnected sessions.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com