Is there any ongoing issues with SQL when trying to just change the column type via SSMS

Question

When trying to change a column config from nvarchar(10) to nvarchar(200) the SSMS throws an timeout error when saving the change. The table is not a big one, just 40k rows. I have tried to change the Transaction time from default 30 secs to 120 secs, but that doesn't help. Earlier (couple days ago) the change went through just fine within few secs and after that, nothing has changed. I just found out this behavier yesterday.

What might be the reason for this kind of behavior and how to fix this?

Thanks

Answer 1

When you encounter a timeout error while trying to change a column type in SQL Server Management Studio (SSMS), it can be due to several reasons, even if the table is relatively small (40k rows). Here are some potential causes and solutions:

1. Query Performance: The timeout could be related to the performance of the query that SSMS generates to alter the column. If there are any constraints, indexes, or triggers associated with the column, they may be contributing to the delay. You might want to check if there are any locks on the table or if other operations are running concurrently that could be affecting performance.
2. Transaction Settings: Since you mentioned increasing the transaction timeout from 30 seconds to 120 seconds without success, ensure that the increase is being applied correctly and that there are no other settings overriding it.
3. Change Tracking: If change tracking is enabled on the table, it might complicate the operation. You can try disabling change tracking temporarily, make the column change, and then enable it again.
4. SSMS Version: Ensure that you are using the latest version of SSMS, as bugs and performance issues are often addressed in updates.
5. Database Health: Check the overall health of the database. Running DBCC CHECKDB can help identify any underlying issues that might be affecting performance.
6. Alternative Methods: If the issue persists, consider using T-SQL commands to alter the column type instead of using the SSMS GUI. This can sometimes bypass issues encountered in the graphical interface.

If the problem continues after trying these suggestions, you may want to investigate further into the specific execution plan for the operation or consult SQL Server logs for any additional error messages that could provide more insight into the timeout issue.

Answer 2

You can just script the changes and run the script manually.