Ошибка sql 0 время ожидания запроса истекло

Здравствуйте, товарищи!
Озаботился написанием скрипта на Power Shell для бэкапа нескольких баз данных из MS SQL.

$Server = "localhost"
$BaseName = "master"
$BaseLogin = "user"
$BasePassw = "password"
$connection = New-Object -com "ADODB.Connection"
$ConnectionString = "Provider=SQLOLEDB.1;
                     Data Source=$Server;
                     Initial Catalog=$BaseName;
                     User ID=$BaseLogin;
                     Password=$BasePassw;
		     Connection Timeout=0;"
$connection.Open($ConnectionString)
$connection.Execute("BACKUP DATABASE [db_name] TO DISK = 'd:bakdb_name.sql' WITH INIT, NOFORMAT, SKIP, NOUNLOAD")
$connection.Close()

Это, разумеется, урезанный код и он возвращает ошибку: Исключение при вызове «Execute» с «1» аргументами: «Время ожидания запроса истекло».
При этом, если выполнять запрос из Management Studio, то запрос выполняется без ошибок. Время выполнения чуть более 2х минут.
Поковыряв интернет, нашел совет поиграться с Connecction Timeout в строке подключения. Обещают, что при значении «0» ошибка таймаута должна исчезнуть. Не тут-то было…
Подскажите, плз, как побороть эту беду?

While I would be tempted to blame my issues — I’m getting the same error with my query, which is much, much bigger and involves a lot of loops — on the network, I think this is not the case.

Unfortunately it’s not that simple. Query runs for 3+ hours before getting that error and apparently it crashes at the same time if it’s just a query in SSMS and a job on SQL Server (did not look into details of that yet, so not sure if it’s the same error; definitely same spot, though).

So just in case someone comes here with similar problem, this thread:
https://www.sqlservercentral.com/Forums/569962/The-semaphore-timeout-period-has-expired

suggest that it may equally well be a hardware issue or actual timeout.

My loops aren’t even (they depend on sales level in given month) in terms of time required for each, so good month takes about 20 mins to calculate (query looks at 4 years).

That way it’s entirely possible I need to optimise my query. I would even say it’s likely, as some changes I did included new tables, which are heaps… So another round of indexing my data before tearing into VM config and hardware tests.

Being aware that this is old question: I’m on SQL Server 2012 SE, SSMS is 2018 Beta and VM the SQL Server runs on has exclusive use of 132GB of RAM (30% total), 8 cores, and 2TB of SSD SAN.

You’re trying to connect to a SQL Server, and it is taking longer than ADO.NET is willing to wait.

Try connecting to the same server, using the same username and password, using SQL Server Management Studio. If you get the same error, there is either something wrong with your connection string, the server you specify is not running, or you can’t get to the server across the network from where you are (maybe you’re on a public IP address trying to get in to an internal server name). I can’t think of a scenario in which you’d enter the exact same server and credentials into SSMS and connect, then do the same in ADO.NET and fail.

If you’re on a slow network, you can try increasing the timeout value. However, if a connection is going to happen at all, it should happen pretty quickly.

Take a look at both your SQL Native Client settings, and the SQL Server settings on the server. There is a section for allowed protocols; SQL can connect using a variety of protocols. Usually, you want TCP/IP for a server on the network, and Named Pipes for a server running on your own computer.

EDIT FROM YOUR COMMENT: Oh, that’s normal; happens all the time. From time to time on a TCP network, packets «collide», or are «lost» in transmission. It’s a known weakness of packet-switching technologies, which is managed by the TCP protocol itself in most cases. One case in which it isn’t easily detected is when the initial request for a connection is lost in the shuffle. In that case, the server doesn’t know there was a request, and the client didn’t know their request wasn’t received. So, all the client can do is give up.

To make your program more robust, all you have to do is expect a failure or two, and simply re-try your request. Here’s a basic algorithm to do that:

SqlDataAdapter sqlClearQuestDefects;

short retries = 0;
while(true)
{
    try
    {
       sqlClearQuestDefects = new SqlDataAdapter(sql, "Data Source=ab;Initial Catalog=ac;User ID=ad; Password =aa");
         break;
    }
    catch(Exception)
    {
       retries++;
         //will try a total of three times before giving up
       if(retries >2) throw;    
    }
}

   m74y

13.05.10 — 12:42

   Господин ПЖ

1 — 13.05.10 — 12:47

как я его понимаю… таймаут будет Пушкин указывать…

   H A D G E H O G s

2 — 13.05.10 — 12:48

(1) Там вроде по дефолту есть.

   Смешной 1С

3 — 13.05.10 — 12:48

АДОДБКоннект.ConnectionTimeOut=…;

   m74y

4 — 13.05.10 — 12:49

   sapphire

5 — 13.05.10 — 12:51

(4) Небось записей дофига

   m74y

6 — 13.05.10 — 12:51

но только ищет она шаблоны которые НАЧИНАЮТСЯ с 4545, а надо что бы искались все вхождения 4545

   Megas

7 — 13.05.10 — 12:52

А ещё можно выбирать не
*
А что то более конкретное

   sapphire

8 — 13.05.10 — 12:52

(6) А то мы не догадались

   m74y

9 — 13.05.10 — 12:53

(5) Да база очень большая
(7)  это не так важно, можно выбрать и 2 поля разницы нет

   szhukov

10 — 13.05.10 — 12:54

   sapphire

11 — 13.05.10 — 12:54

(0) Пройди Profilerом запрос, посмотри план выполнения запроса на SQL Server.

Небось seek по неиндексированнному полю.

   szhukov

12 — 13.05.10 — 12:54

+(10)если не ошибаюсь
АДОДБКоннект.ConnectionTimeOut=0 — будет выбирать до упора

   Лефмихалыч

13 — 13.05.10 — 12:55

(9) индексировать не пробовал?

   DmitrO

14 — 13.05.10 — 12:57

(11) Seek-ов по неиндексированному полю не бывает

   sapphire

15 — 13.05.10 — 12:57

(9) В SQLSMS запрос выполнять пробовали?

   Смешной 1С

16 — 13.05.10 — 12:58

АДОДБКоннект.ConnectionTimeOut=0 укажи

   sapphire

17 — 13.05.10 — 12:58

(14) Верно. Я не то хотел сказать.

   sapphire

18 — 13.05.10 — 13:00

(12)(16) Если запрос будет возвращать 70 000 000 000 записей … Вы уверены что нужно ждать пока сервак упадет?

   Megas

19 — 13.05.10 — 13:02

Разовое действие ?
Если да.. забобахай промежуточную таблицу , перепиши туда что надо по частям.

   m74y

20 — 13.05.10 — 13:06

все, догадки кончились?

   m74y

21 — 13.05.10 — 13:07

блин, инет тормозит

   sapphire

22 — 13.05.10 — 13:08

(20) И?

   Смешной 1С

23 — 13.05.10 — 13:08

(20) Хоть какие-то попробовали варианты? (10), (19)?

   m74y

24 — 13.05.10 — 13:13

попробовал (10)- то же самое
(16)- в ошибке пишет время ожидания ЗАПРОСА истекло, этот таймаут здесь совсем не причем, но тем не менее то же не помогает

   m74y

25 — 13.05.10 — 13:15

(19) нет, не разовое действие…

   sapphire

26 — 13.05.10 — 13:17

(24) В SQLSMS запрос выполнять пробовали?

   m74y

27 — 13.05.10 — 13:20

нет, сейчас попробую

   Serginio1

28 — 13.05.10 — 13:26

АДОДБКоннект.CommandTimeOut=0

   shuhard

29 — 13.05.10 — 13:28

(27) брось херней изучаться
если без Like работает, то извлекай все записи и фильтруй на клиенте

   Gepard

30 — 13.05.10 — 13:29

(13) +1

   Serginio1

31 — 13.05.10 — 13:31

   szhukov

32 — 13.05.10 — 13:36

(29) Плохой вариант, сервер как правило мощнее, а если клиент не потянет такого объема?

   sapphire

33 — 13.05.10 — 13:39

(32) Тянуть данные в несколько потоков.

Здесь вообще нопонятно, если поле неиндекисровано и проиндексировать его нельзя из-за объема таблицы, то необходимо использовать доп. условия, что бы сократить объемы данных. Т.е. читать данные порциями, можно и параллельно.

   shuhard

34 — 13.05.10 — 13:41

(32) лажа
ставь динамический Recordset

   m74y

35 — 13.05.10 — 13:41

в SQL SMS нет пока ответа, обрабатывается

   shuhard

36 — 13.05.10 — 13:43

(35) самое время тип поля ven огласить

   m74y

37 — 13.05.10 — 13:43

Таблица в почти 30 млн. наименований

   Шляпентох

38 — 13.05.10 — 13:43

(35) вы своим LIKE ‘%4545%’ лишаете sql server возможности использовать индекс.. Вот он и сканирует все 30 млн. наименований..

   m74y

39 — 13.05.10 — 13:46

   shuhard

40 — 13.05.10 — 13:47

(39) потому что %4545% приходится искать по всей длине строки
с 1,2,3..N позиции
чем строка длиннее — тем куевей

   Sadovnikov

41 — 13.05.10 — 13:48

(39)
‘%4545%’ — индекс НЕ используется
‘4545%’- индекс используется

   shuhard

42 — 13.05.10 — 13:49

(39) + тип строка ни о чем не говорит
на сиквеле уйма разных типов строк

   m74y

43 — 13.05.10 — 13:49

в SQL SMS все нашлось, время на выполнение запроса — около 7 минут

   Шляпентох

44 — 13.05.10 — 13:50

(43) значит ставьте соответствующий таймаут, если время выполнения устраивает. Если не устраивает, а запрос должен искать именно %4545% — самое время задуматься и почитать про полнотекстовое индексирование..

   m74y

45 — 13.05.10 — 13:52

(44) как выставить соответствующий таймаут?

   sapphire

46 — 13.05.10 — 13:53

(43) Записей сколько возвращается?

   Шляпентох

47 — 13.05.10 — 13:54

(45) смотри (3), (16), (31)

   szhukov

48 — 13.05.10 — 13:59

(45) см (12)

   szhukov

49 — 13.05.10 — 13:59

не успел

   m74y

50 — 13.05.10 — 14:02

   Шляпентох

51 — 13.05.10 — 14:06

(50) И о чем, по Вашему говорит эта ошибка?

   kiruha

52 — 13.05.10 — 14:06

   kiruha

53 — 13.05.10 — 14:07

http://ru.wikipedia.org/wiki/Полнотекстовый_поиск
Первые версии программ полнотекстового поиска предполагали сканирование всего содержимого всех документов в поиске заданного слова или фразы. При использовании такой технологии поиск занимал очень много времени (в зависимости от размера базы), а в интернете был бы невыполним. Современные алгоритмы заранее формируют для поиска так называемый полнотекстовый индекс — словарь, в котором перечислены все слова и указано, в каких местах они встречаются. При наличии такого индекса достаточно осуществить поиск нужных слов в нём и тогда сразу же будет получен список документов, в которых они встречаются.

   Шляпентох

54 — 13.05.10 — 14:08

+(51) и задумайтесь, пожалуйста, о переводе слова timeout, прежде чем будете (если будете) отвечать

   sapphire

55 — 13.05.10 — 14:11

   sapphire

56 — 13.05.10 — 14:12

   Gepard

57 — 13.05.10 — 14:24

(0) есть возможность правки структуры таблиц, по которым запрос делается?

   sapphire

58 — 13.05.10 — 14:28

(57) 30 лямов записей можно проиндексировать,

включить сжатие page, построить кластерный индекс.

   Gepard

59 — 13.05.10 — 14:31

(58) ну и я об этом… добавить индекс к полю

   Sadovnikov

60 — 13.05.10 — 14:38

(58) «построить кластерный индекс» — а точно ли надо?

   Gepard

61 — 13.05.10 — 14:49

(60) кластерный не нужен точно

   Шляпентох

62 — 13.05.10 — 14:51

(58) а смысл индексирования? там может и так индекс есть.. только вот условие ТС не дает использовать индексы по этому полю..

   m74y

63 — 13.05.10 — 14:59

(57) возможность править структуры таблиц отсутствует, т.к. в настоящий момент база используется рабочей 7- кой

   sapphire

64 — 13.05.10 — 15:03

(63) код из (55) пробовал?

   Sadovnikov

65 — 13.05.10 — 15:04

(61) Вот и я как раз пр ото же…

   m74y

66 — 13.05.10 — 15:12

(64) Да, спасибо, только что все работает, я перепутал CommandTimeout с ConnectionTimeout

   Serginio1

67 — 13.05.10 — 15:13

28 и 31 внимательнее смотреть надо. А то и писать нет смысла

   Gepard

68 — 13.05.10 — 16:00

(63) поставь в конфигурации в свойстве поля «Сортировка» и «Отбор по значению»