| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-05-14 : 19:57:34
|
| Hi, What does this error indicate? I was running a stored procedure for indexing in QA.--[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL ServerConnection Broken--I didn't see any error msg on error log.Thanks,SaratTo be intoxicated is to feel sophisticated, but not be able to say it. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-14 : 20:05:36
|
| Connection Broken <usually> means that someone stopped the MSSQLSERVER service or rebooted your server. If you look at the previous error log, you should see what happened.I haven't seen this part though: Unknown token received from SQL Server.Tara |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-15 : 16:24:10
|
| Ya, I see 2 gaps (the connection broke two times while running the sp) in the log file, it does say though 'unknown token recieved from sql server). i cannot think that sql server was stopped or restarted (as no-one did anything) and if it was restarted, should i not the see the log for sql agent, databases being started?i think this happenned because the backup job step with the Build-Index stored procedure failed as the both the times the last command in the log was:2003-05-14 16:43:33.31 backup BACKUP failed to complete the command exec dtc_DoReindex-- Thanks,Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-15 : 16:44:42
|
| What is your job trying to do? Backup and reindex? What is the code of your job step?If this is a maintenance plan, you might want to just recreate it since that sometimes fixes problems with them. A lot of people here would not recommend using maintenance plans because you don't really have any idea what it is doing (exact syntax at least) unless you do a trace on it.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-15 : 16:51:10
|
| Ya, I somehow felt it was stored procedure (performing index rebuilding instead of using maintenance plan) i wrote was causing but i was expecting a different error than 'connection broken' or 'unknown token ....' - a bit vague!i did recreate the stored procedure and i am testing it now. i will see if i get the error again then i will post the code.Thanks,Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-15 : 16:53:09
|
| Why would you even want to do your backups and reindexing in the same job?Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-15 : 17:14:51
|
| Here is the entire story:-> Because I have the database recovery model set to Full (because it is required for Log shipping and if i change recovery model, the log shipping fails and it is a pain to fix it every week because there is a bug which when you delete the log shipping doesn't remove some old entries which doesn't let you recreate a clean log shipping plan), and -> so it fills up the log quickly even though I have transaction log backup setup for every 15 mins, the indexing job (through EM) takes forever (the 15min TLog backup seems to fight processor time with reindex job). I started running the job after 5 pm and manually doing additonal backups in QA sped up the job and it gets done in 1 hr.-> But since we are rolling out some PSoft Apps to all employees all over US, I cannot expect everyone to be off the system at 5PM every thursday (plus i don't want to work too late to do additional log backups - don't have remote access) so I wanted to create a stored procedure which can be run on sundays with log backups after every 50 tables of reindexing.-Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-15 : 17:24:56
|
| Ah! I see. We've got a similar situation here. One of our databases gets a 2GB transaction log file when the reindex is done. But what does it matter if the transaction log gets big during the reindex? Of course it is going to take a little bit longer to copy it over to your secondary server for log shipping, but that shouldn't matter.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-15 : 17:40:30
|
| You are right but guess what ....we have space issues (very limited) on production, test and dev servers that is why i was working on a 5 yr db estimated growth to convince mgmt to buy more space.Moreover, i think they feel maintaining sql server is not a big deal(compared to as400 and oracle which may be true but it does require decent time and resources to manage right?) so it is hard to get what we want.-Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-15 : 17:52:08
|
| I was hoping you were going to say disk space because that would really be the only reason. Anyway, disk space is cheap these days, so why don't they buy some to save you a headache? Instead of creating a custom job to fix this problem without adding disk space, why don't you just change transaction log backup job to occur every 5 minutes or something smaller than 15 minutes? If you only want the backup to run more frequently during the reindex, then create a job step that runs before the reindex that modifies the transaction log backup job so that it runs every 5 minutes. Then create another job step that runs after the reindex is done that modifies it back to 15 minutes. What do you think? It's only a bandaid for your disk space problem, but it would help you out for a little while at least.TaraEdited by - tduggan on 05/15/2003 17:52:41 |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-15 : 18:02:06
|
| Ya, I guess I could do that. I will try that first thing next week.Thank You!Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
|
Connection Broken Error