1

I need to clear the log file of database in SQL server

here is the code snippet

engine.execute("  DBCC SHRINKFILE  (TEMP2_log, 1)  ")

When I run the query in SQL Server , the database log file is reduced.

when I run the above code I got error like this

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement. (8920) (SQLExecDirectW)')
[SQL:   DBCC SHRINKFILE  (TEMP2_log, 1)  ]

can anyone help me where am I going wrong

Aishwarya
  • 11
  • 2
  • Cross site duplicate candidate: [Shrink logs in between user transaction in SQL Server](https://dba.stackexchange.com/questions/87098/shrink-logs-in-between-user-transaction-in-sql-server) TL;DR: Why do you want to shrink the log file in the first place? Assuming you're using a Full Recovery Model then run a log backup if you want it to get smaller (which you should be doing frequently anyway). – Thom A Jan 18 '22 at 10:09
  • when i run the query in SQL server , the database log file is reduced and samething as above code when i run in python i got error @Larnu – Aishwarya Jan 18 '22 at 10:30
  • Does your Alchemy wrapper thingy support turning on/off the implicit transactions settings? What seems to be happening right now is that it is issuing `BEGIN TRANSACTION; ; COMMIT TRANSACTION;` - this is just one of many problems when you use some kind of library that "simplifies" things for you. – Aaron Bertrand Jan 18 '22 at 14:55
  • ...you could also consider being proactive about database/file management and - in the rare event you do really need to shrink a log file (which you shouldn't, generally, if you read [this in full](https://dba.stackexchange.com/q/29829/1186) and follow the guidance there) - do it from SQL Server, not from some application. – Aaron Bertrand Jan 18 '22 at 16:08
  • 1
    Does this answer your question? [how to set autocommit = 1 in a sqlalchemy.engine.Connection](https://stackoverflow.com/questions/26717790/how-to-set-autocommit-1-in-a-sqlalchemy-engine-connection) – SMor Jan 18 '22 at 19:13
  • nope.. i got same error @SMor – Aishwarya Jan 19 '22 at 04:04

0 Answers0