SQL Transaction Log

Can't clear it down...

[giggle]

I'm in what appears to be a surreal situation (to me at least).

Got a new hosting company and started populating our database with the required static data. Everything was going ok until I got an error message that stated something like "Your transaction log is full", since then I cannot do anything with the database.

I contacted our new hosting company asking for help clearing down the transaction log and got the reply:

We will not clear down your transaction log for you as you will need to purchase additional SQL space.

They say that we've been allocated 10mb of space and that our transaction log takes up 10mb!

Seems like a shaky start to our relationship, with other hosting companies I've never encountered this problem.

Anyone got any ideas how to close the transaction log or do we (before our site even goes live) have to purchase the additional space?

Cheers

Mick

[chicagohh]

If your sure you don't need your log try running the following query - Actually, read up on it first and then make your decision...

DUMP TRANSACTION yourdatabase WITH NO_LOG

[giggle]

Thanks chicagohh, I wasn't sure how to run that command. I tried to set it up as a Stored Procedure (?) but when I tried to save it I got the "Transaction Log Full" error message again.

I'm running Enterprise Manager, is there another way of running the command?

Cheers

Mick

[plumsauce]

if you don't care about up to the minute recovery, set the property "truncate log on checkpoint" for your database.

10mb seems rather small for a space allotment whatever the price. i start my customer db's at 50+50.

reading up on backup procedures in SQL BOL will explain why the transaction log is getting full.

[giggle]

Thanks plumsauce, I agree 10mb sounds too small.

I looked in the properties but cannot see the Truncate Log On Checkpoint setting.

In the same email from them that said "We won't clear down your transaction log" they suggested purchasing a dedicated server.

Customer service? I don't think so...

[topr8]

move hosts, 10mb is tiny!

we have a shared hosting account and have 350mb of sql server space

...

i would add that i had a host once where the transaction log just got bigger and bigger and used up all my space, but after a quick phone call, they realised there was a wrong setting, changed it and all was resolved - don't know what was changed i'm afraid.

[giggle]

I agree top8, starting to think that the 10mb is too small and that the level of support that we're receiving is ridiculous.

This is the complete response from them to my support request asking for help:

"We will not clear down your transaction log for you as you will need to purchase additional SQL space.

Your database is using 10mb and your transaction log is 10mb aswell.

We offer dedicated servers which has MSDE installed as standard,which start
from £65.00 per month,would you like our Sales Team to contact you?"

Obviously if the transaction log is taking up all our space then there is a problem that shouldn't be corrected by purchasing additional disk space for the transaction log to use!

Cr*p.

[giggle]

Ok, time to look for a different hosting company, this was there response to my latest pleading email:

We have already told you that you will need to purchase additional SQL space,it is not possible for us to clear down your transaction log.
We have also told you that your SQL database is 10mb,so clearing your transaction log will not make any differance as you have only requested and paid for 10mb of SQL space.

Start looking for a new hosting company in the uk again and give Netcetera a miss...they're cr*ap...

[Easy_Coder]

giggle - tell your host 'good luck with their sales prevention techniques' on your way out the door.

[giggle]

You're right Easy_Coder, I went through their Cancel Account process and it crapped out with an SQL error, there's poetic justice for you.

I emailed them and notified them of the error and they came back with "Try Again", I told them to shove it, too busy looking for a good UK hosting company!

If anyone knows of a reliable hosting company that provides more that 10mb I would really appreciate it if you could sticky me their URL.

All the best

Mick

[Easy_Coder]

--> I went through their Cancel Account process and it crapped out with an SQL error

Naturally...

[woop01]

You should be able to find about a million web hosts that offer more than 10mb of SQL space for a cheap price. 10mb is nothing.

[giggle]

Thanks guys.

I found a hosting company that offers lots of SQL data space and signed up with them.

Now it turns out that you cannot administer your database remotely. i.e. you cannot use MS SQL Enterprise Manager and all my VB syncing software won't work either! That wasn't in the small print...

Now that I've learned what questions I should have asked before signing on the dotted line and handing over the cash I think that maybe we should start a thread that lists questions you should ask your prospective hosting company so that noone else falls into the trap I fell into (maybe you're all more switched on than me!).