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
LVL 1
JElsterAsked:
Who is Participating?
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.