search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.88k stars 562 forks source link

What could cause a problem with pyodbc and SQL Server tempdb? #1220

Closed preneram closed 1 year ago

preneram commented 1 year ago

What could cause a problem with pyodbc and SQL Server tempdb?

When executing a user stored procedure on some database on SQL Server, tempdb properties are not accessible and templog shrinking (for processes running parallel) is somehow blocked. The usp makes use of tempdb during execution.

Therefore, templog is growing infinitely if there is no “close” while debugging (and temporarily during regular execution).

Running the same usp with Excel Power query has no influence on accessing tempdb properties.

gordthompson commented 1 year ago

Have you tried connecting with autocommit=True to see if that makes any difference?

v-chojas commented 1 year ago

Besides what Gord asked above, could there be permissions differences? Are you using the same login to the server?

preneram commented 1 year ago

Thank you for your replies! With "autocommit=True" the problem is solved in our use case. Does it mean pyodbc is always using transactions for any executed SQL if auto commit is not set True? Do I understand this right? It does something like:

 begin transaction

 exec dbo.LongRunningUspUsingTempdb @takesometime = 3600

 commit transaction

v-chojas commented 1 year ago

Autocommit is by default off, hence you would need to commit transaction manually.