Regarding the type of file growth increment to use, it depends upon your database traffic. Ideally, you should not have to continuously have your database autogrow so if your autogrowth...

Maybe this will help you.

declare @Desired_CustomerID int

select top 6500 @Desired_CustomerID = CustomerID

from dbo.Customer

order by CustomerID

select @Desired_CustomerID

That should return the CustomerID of the 6500th record.

Unfortunately, I haven't solved the problem.  A month after my original post, I left the job and am now not a database administrator (I moved over to development).  Good luck!

Thank you for the suggestion.  I'll try that.  I wanted to also note, as I have been fiddling with this all day, that I ran each query individually and I...

The transaction log prior to the one that fails is always successful.  I don't see a real pattern to when the backup fails.  It happens at all times during the...

Hi Amol,

There is a two column clustered index already existing in billing.dbo.payments and it is on paymentid and scpoolid.

Thanks!

Here is the entire procedure as it was prior to any testing.  The first insert into the recon table is where my headache appears to be located. I am currently...

Thank you for all of your replies.  I actually added indexes to the temporary tables yesterday and now the procedure takes 2 hours and 50 minutes! 

Hi Andy,

Great! That makes some sense. Thanks for clarifying.

Melanie

Hello,

The fill factor is 80%.

Can you please explain how this could be the cause? I guess I'm confused because I thought that with deleting half a million records, the...

Hi Andy,

Thank you so much!! You allowed me to continue to telecommute every Fridays (can't give that up!).

Thanks!!

Melanie

Hi Juanita,

In our environment, SQL Server is only rebooted when the server itself needs to be rebooted. Otherwise, in our 24x7 shop, SQL Server always needs to be up...

Hello,

Well, our network guy decided to defrag all of our systems and so far we haven't experienced any TDS errors again... and hope we never do. We did not...

Hello Antares686,

Thank you very much for your recommendation. I'll try this and let you know how it goes.

Melanie

Hi Steve,

I just thought the VBScript would be more difficult than it actually is. I just tried it and the script worked perfectly.

Thanks!

Melanie