Ben Curthoys's questions

I have a Webserver and a SQL Server inside an Amazon VPC.

Previously, a Webserver outside the VPC could connect to the SQL Server fine.

Now, sometime, when running SQL Scripts (the deployment script reruns all the stored procedures on install), some of the create procedure scripts fail with an error

"An exception occurred while executing a Transact-SQL statement or batch. A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 -The semaphore timeout period has expired)"

Once it starts failing, it will continue to fail until a reboot. It is always the same scripts that fail. Rebooting both servers clears the problem, but that's not a long term solution. Windows Firewalls off on both machines. Windows Server 2012 on both machines. It might be correlated with a pending Windows Update on the Web Server; every time it's stopped there have been updates pending. Server Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

If I open up the Security Group for the SQL Server, I can connect to it directly and run all the scripts from my development machine; they still fail when I run them from the Web Server, so I'm pretty sure it's the Web Server that's the problem. I'm just not sure how or why.

The scripts are automatically generated from the database; they're basically identical, and don't contain syntax errors, and work when run from elsewhere. But some of them die when being run from the Web Server.

What could the problem be?

In my nightly maintenance plan, I was doing everything - reorganising and rebuilding indexes, updating statistics, etc. There's an hourly maintenance plan that does a transaction log backup.

I noticed that the transaction log backup immediately after the nightly rebuild was huge - and a little research told me that was because rebuilding indexes etc were logged operations.

I don't want to burden my backup system by backing up logs of index rebuilds. Therefore, I plan to change by Nightly plan to:

1. Rebuild Indexes, Update stats, etc
2. DUMP TRANSACTION WITH NO_LOG (oh, which is apparently called BACKUP LOG test WITH TRUNCATE_ONLY these days)
3. Shrink log files
4. Do full database backup

Questions:

1. Is there something wrong with this plan?
2. What's the best way to include the log truncation in a maintenance plan?
3. I just read that including both Reorganise and Rebuild indexes is redundant. How should I decide which is the right one to use in a nightly plan?