February 4, 2017 at 5:25 pm
I have a MS SQL 2008 database, remote hosted. Recently the host upgraded the server platform and the new CP has no option to truncate the transaction logs. They have replaced it with a "my little admin" page, which has a field where you can insert code to perform actions to the database.
Is there a standard code that will clear a mssql database transaction log? I tried calling technical support, but they "had never heard of transaction logs before" Really? Unbelieveable. The hosting company is Verio. I've had nothing but problems with them.
Thank you
February 4, 2017 at 8:17 pm
Tatay_2016 - Saturday, February 4, 2017 5:25 PMI have a MS SQL 2008 database, remote hosted. Recently the host upgraded the server platform and the new CP has no option to truncate the transaction logs. They have replaced it with a "my little admin" page, which has a field where you can insert code to perform actions to the database.Is there a standard code that will clear a mssql database transaction log? I tried calling technical support, but they "had never heard of transaction logs before" Really? Unbelieveable. The hosting company is Verio. I've had nothing but problems with them.
Thank you
If you have a hosted database and they're supposed to be doing your administration for you but have never heard of transaction logs, maybe it's time to get a new host for your database. The log size is determined by your load and the frequency of your log backups. Attempts to shrink it won't work if your log entries aren't backed up and you're in the full recovery mode.
There's a lot more to managing your logs than just shrinking it. There's a stairway series on this site that'll help you understand the transaction logs. It's at http://qa.sqlservercentral.com/stairway/73776/
February 4, 2017 at 10:03 pm
Hello Ed,
This provider was good until the take over last year, then it went south FAST, like 2 weeks fast.
Yes we have to move, but that is difficult for a small business with a limited budget to do that immediately.
I know I have a lot to learn. I was just looking for a start, so thank you for that link.
February 6, 2017 at 5:57 am
First up, I'm not familiar with Verio, so take this for what it's worth...
The questions I would have are, what sort of control do you have over your databases (IE, can you set the recovery model?) and who is responsible for the backups of your databases and what types of backups are they taking?
The reasons behind the first question are because in general, truncating the transaction logs (and I'm presuming you don't mean shrinking them) removes any possibility of point-in-time recovery from backups. Which, if you don't need point-in-time recovery, means if you can set the recovery model you can stop truncating the logs. Set your databases to Simple model and go on about your business.
If you do need point-in-time, then STOP truncating and start taking transaction log backups. If the hosting provider is supposed to be taking the backups, then find out if they are, what kind of backups, and how frequently.
But, if the comment from the technical support people is anything to go by, I'd agree with Ed, change providers ASAP, before you lose data, regardless of the outcome of the backups...
February 6, 2017 at 6:15 am
jasona.work - Monday, February 6, 2017 5:57 AMFirst up, I'm not familiar with Verio, so take this for what it's worth...
The questions I would have are, what sort of control do you have over your databases (IE, can you set the recovery model?) and who is responsible for the backups of your databases and what types of backups are they taking?
The reasons behind the first question are because in general, truncating the transaction logs (and I'm presuming you don't mean shrinking them) removes any possibility of point-in-time recovery from backups. Which, if you don't need point-in-time recovery, means if you can set the recovery model you can stop truncating the logs. Set your databases to Simple model and go on about your business.
If you do need point-in-time, then STOP truncating and start taking transaction log backups. If the hosting provider is supposed to be taking the backups, then find out if they are, what kind of backups, and how frequently.But, if the comment from the technical support people is anything to go by, I'd agree with Ed, change providers ASAP, before you lose data, regardless of the outcome of the backups...
One addition to what Jason posted - get them to work with you in testing your backups. After all, they can say they're taking backups, but if the backups can't be used to recover, their words are useless. First, find out your RPO and RTO, which will likely be spelled out in your contract. Then, do a practice restore and test it. This will test both the people and the backups themselves for viability. Without both, you don't have usable backups.
February 6, 2017 at 7:31 am
Tatay_2016 - Saturday, February 4, 2017 5:25 PMI have a MS SQL 2008 database, remote hosted. Recently the host upgraded the server platform and the new CP has no option to truncate the transaction logs. They have replaced it with a "my little admin" page, which has a field where you can insert code to perform actions to the database.Is there a standard code that will clear a mssql database transaction log? I tried calling technical support, but they "had never heard of transaction logs before" Really? Unbelieveable. The hosting company is Verio. I've had nothing but problems with them.
Thank you
I've had clients that have run into similar stupid problems with backups by the provider. They would do FULL backups only once per week and transaction log backups only once per day. Their excuse was that doing more TLog backups would use more disk space. Heh... idiots. They didn't even have a DBA on staff until several months of me beating them over the head with threats to leave if they didn't get their act together. Keep in mind that 90% of their larger customers are using SQL Server on their boxes I have no trust in such 3rd party companies because everyone I've dealt with so far has been equally stupid. And, no... it's not simple ignorance... it's downright stupidity. If you're going to advertise that you "do backups" and "safeguard your data", they you should actually be able to do it correctly.
--Jeff Moden
February 6, 2017 at 3:20 pm
Hello Ed,
This provider was good until the take over last year, then it went south FAST, like 2 weeks fast.
Yes we have to move, but that is difficult for a small business with a limited budget to do that immediately.
I know I have a lot to learn. I was just looking for a start, so thank you for that link.
jasona.work - Monday, February 6, 2017 5:57 AMFirst up, I'm not familiar with Verio, so take this for what it's worth...
The questions I would have are, what sort of control do you have over your databases (IE, can you set the recovery model?) and who is responsible for the backups of your databases and what types of backups are they taking?
The reasons behind the first question are because in general, truncating the transaction logs (and I'm presuming you don't mean shrinking them) removes any possibility of point-in-time recovery from backups. Which, if you don't need point-in-time recovery, means if you can set the recovery model you can stop truncating the logs. Set your databases to Simple model and go on about your business.
If you do need point-in-time, then STOP truncating and start taking transaction log backups. If the hosting provider is supposed to be taking the backups, then find out if they are, what kind of backups, and how frequently.But, if the comment from the technical support people is anything to go by, I'd agree with Ed, change providers ASAP, before you lose data, regardless of the outcome of the backups...
Hello,
Thank you for the reply.
By "Truncating the transaction logs" I was referring to a process that was available on the old platform. Every time we got a "transaction log is full" message, we would hit the truncate button and it would work normally again. It was automated from the CP.
The database is remote hosted by a web hosting company called Verio. They just upgraded our windows server platform, and much of the dashboard user functionality was taken away. They use a "phone technician" to screen calls for the "Technical specialists". I have never spoken to one of the Tech Specs. Ever. The phone tech is book trained. They know very little. I'm pretty sure that all of the questions you asked above would be answered with "I'm not familiar with that" or " I don't have access to that". It used to be better than it is now.
They are responsible for backups. I asked them for a backup copy of the database, and they placed an encrypted .bak file in a directory on my website that I imagine can only be opened back into their server, I don't think I can use it elsewhere.
So yes the real solution is to leave. We are in the process of that, but still need to access this database until the new site is ready, thus I was trying to determine if there was something I could do, but I see no clear path to that. There is nothing in the control panel that indicates I can set the recovery model.
Thanks again. All new information is good information.
February 12, 2017 at 8:24 pm
OK just an update, with a couple of questions.
I spent several hours online with customer support. This provider has at least 4 tiers of support. The first is phone, second is low level fixes, 3rd is more complex, 4th is administrative level (I'm assuming the DBA).
The first three tiers had no permission to access the database to do anything to resolve the "transaction logs are full" error message, and I was told by the phone tech that they "had never heard of transaction logs".
I received an email message from the DBA saying "Transaction logs should clear automatically. There is nothing wrong with your database. Check things on your end"
Still, we get the error message "transaction log for database (mydatabase) is full. To find out why the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (#9002)
So my two questions:
I need to ask them what is in that column in sys.databases. I have no access to it. What should I ask for?
Also the error message starts with "ODBC - update on a linked table 'tableinv' failed." We all use MS Access. Could the error be solved somehow on our end?
Thanks. This is so frustrating to have such little support from people we are paying money.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply