• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

SQL Server - Transaction Log Full

I'm trying to delete all record in a table.. but I get transaction log full.
I've tried this.. but same result.. How can delete the log and delete all my records.
what's wrong

DBCC SHRINKFILE (MY_LOG,2)



DELETE FROM MYtable
0
JElster
Asked:
JElster
  • 2
1 Solution
 
EvilPostItCommented:
If you use the following it should work.

TRUNCATE TABLE MyTable

Open in new window


Although this should only be used if you want to remove all data from a table.
0
 
xDUCKxCommented:
Usually the Transaction logs are flushed after a backup.  The above from Evil will flush the logs but you'll lose any pending transactions that haven't been committed.  Run a backup of the database and then the logs should shrink.
0
 
EvilPostItCommented:
No, what I posted above does not flush the logs. It flushes the table by resetting the high watermark of the table. The reason i am suggesting this is because it is a non-logged operation and will therefore no fill the transaction log up.

Also you will not loose any pending transactions as you would have to kill the SQL Server process and delete the transaction log to do that.

Truncating the transaction is a way to clear down the transaction log, which was supported previously by running BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY

These days, you should change the recovery model to simple and back again.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now