Need help with delete query
Posted on 2014-11-18
I know very little about writing queries for MS-SQL so this may be a very basic question. Thanks in advance.
We have a hosted website which uses an MS-SQL backend database.
Customers can come to our website and search our inventory.
I have ASP code that saves each search in a database called '87_inventory_internet_logsql'
We've run out of space for the data and log file and not the website is down.
THe problem is the logSearch table which is the table where I save search results.
It seems that certain specific IPs have performed numerous searches which have consumed all my space.
For example, a machine at Remote_Host IP 220.127.116.11 has performed over 500,000 searches.
I suspect that this is a spider or search engine, and would like to delete the searches I have saved to make space.
There are probably 100 other IPs that have performed space wasting searches.
I typed the query:
DELETE FROM logSearch
and received the message:
Msg 9002, Level 17, State 4, Line 1
The transaction log for database '87_inventory_internet_logsql' is full due to 'ACTIVE_TRANSACTION'.
Please help, what I would like to do is delete any search results for any machine that has performed more than 1000 searches. THey certainly are not clients of mine!