Deleting records but Transaction log full
Posted on 2010-08-26
We have made the unfortunate mistake of not closely monitoring the disk space on our sql server. (The drive that contains the data)
We would like to delete some old reporting data older than 2009 but we cant now because when we run a query to delete these records we get "Transaction log full"
sp_helpfile comes back with the below:
KPDatamart_Data 1 D:\Data\KPdatamart.mdf PRIMARY 44887104 KB Unlimited 10% data only
KPDatamart_Log 2 D:\Transactions\KPdatamart_log.ldf NULL 7488 KB Unlimited 10% log only
We have run the below commands but we still cannot delete data from the table in order to reduce the physical size of the .mdf file
BACKUP LOG kpdatamart WITH TRUNCATE_ONLY
What can we do to delete records from one of the big reporting tables in order to reduce the size of this database now that we are physically in a catch 22 situation?
I'm running the query:
DELETE FROM ReportingData
WHERE (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))
The data starts on 02/10/07 and there's only about 1000 records from that day but it will not delete