SQL Server Performance

Strange SQL Server time out

Discussion in 'SQL Server 2005 General DBA Questions' started by hernan93, Apr 8, 2008.

  1. hernan93 Member

    Hello everybody:
    I have a SQL Server intance with SP2. I want to modify the colum's order of one of my tables, which has 600.000 records. the time out property is set to 0 (unlimited).
    I open the desing view, (in multiuser or single user mode, the get the same result with both modes).
    With the mouse, for example, I move the fifth column to the second o seventh column position.
    SQL Server shows a warning telling me that this kind of operation with large amount of data can take very long time. I click ok. (continue)
    After a few seconds SQL Server shows a Time Out Error!!! (It seem like is SQL doesn't do anything)
    No event viewer o SQL Server error or warning is reported.
    It seems like if SQL Server ignores the time out=0 (or whatever configured).
    If I try the same procedure with less records, for example 250.000 the operations is successful [:S]. I thought it was RAM problem and upgraded my server from 2GB to 4GB
    I need to solve this issue but I would like to knwon which other method can I use to change the column order
    Plase help, any idea?? or documentation about it?
    Thank you in advance.
    Hernán Rojas.
  2. preethi Member

    Not exactly sure of why timeout is not working. Did you set the connection timeout or execution timeout? The former applies only to connecting to the server instance.However, I always do this type of work in small chunks. I loop through and complete the whole data. Yes, it will take more time to complete, but you have better control over what’s happening. It is easy to see the progress too.
    Best of luck
  3. hernan93 Member

    Thank you for your answer.
    I set the execution time out.
  4. jagblue New Member

    Hi
    Why you wnats to move physican order of the column in table
    any how data will store physically in order of cluster index in your table
    and you can set order you wants in select statment it want affect execution time of select
    Thanks
  5. L0st_Pr0phet Member

    Not sure why this is, but there are various ways around it, it does seem to me to be a resource issue though. What do the perf counters look like when you try this.
    I personally would just use a view to mask the tables structure, if you really wanted to do this another way you could create a new table and reload it!
  6. hernan93 Member

    Hello everybody I found the problem. The time out is originated from SSMS not from SQL Server engine. To correct the problem:
    Tools-->Options-->Designers-->Table and Database designers. The defoult is 30 seconds.

Share This Page