Yesterday, I was deploying some scripts to production. One of the script was to create a database and set its intial size to 50GB for data and log. It created the database alright on the local server but when it attempts to create it on remote servers, it failed with the following error message:
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)
A search on the web suggest that it could be a hard disk failure or some shared memory and/or namepipe issue. I called BS on it and refused to go down that route. I thought it might have something to do with the linked server but it wasn’t, based on how the dev wrote the db creation process. A UDL test from serverA to serverB was good, port 1433 was open, ping was responding, and able to access serverB using its virtual name.
After some time, I got annoyed and told the dev that we are going to script out the db and create it manually on each servers and that his script will have to go into the db and add the necessary objects. On all 15 servers, when attempting to create the db manually, it failed with the same error. Amazing. Now I have a feeling it might be the db initialization size that’s causing the problem. So I changed the size from 50GB to 1GB and everything went smoothly.
The 16 servers are running SQL 2005 w/ sp2 and sp2 hotfix.