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

Need help with delete query

Hello.

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 198.143.187.202 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
 WHERE Remote_Host='198.143.187.202'

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!

Thanks
0
pcalabria
Asked:
pcalabria
  • 8
  • 4
  • 3
  • +1
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to delete them in smaller batches, say 10,000 at a time;  delete only when during off peak hrs

lp:
DELETE top 10000 FROM logSearch
WHERE Remote_Host='198.143.187.202'
IF @@rowcount = 0 GOTO exit;
WAITFOR DELAY '000:00:20'
goto lp;
exit:
0
 
pcalabriaAuthor Commented:
Thanks, but why do we need to wait?
and can you explain what WaitForDelay is about?
0
 
pcalabriaAuthor Commented:
Also, where do I put the code?  I know how to run a query but that's about it.
I think I understand what the code should do..try again every 20s?
not sure why.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Aneesh RetnakaranDatabase AdministratorCommented:
that's for the check point to run, you can replace it with a checkpoint statement or a backup log statement to shrink the log file
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
So the query continues to run till it deletes all the records,( I didn't mean to wait for 20 secs looking for 2 secs ).  An alternate option if you have to delete most of the  records is to use a select into statement to create another table again off peak hours.
0
 
pcalabriaAuthor Commented:
Our messages may have crossed.  Did you see my question asking where the code goes?  I have not idea what a checkpoint statement is or a backup log statement to shrink the log file is, but I do learn fast and that is exactly what I think I need to do!
0
 
pcalabriaAuthor Commented:
Can someone tell me where to put code (such as the code provided in this post) so that it can be executed on MS SQL server?  I'm familiar with Access but that's not much help here!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi pcalabria,

There's no DBA working with you?
You need to perform a transaction log backup to free space. Without that you can't do any kind of actions in the database.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to run that query in SSMS against the database which is experiencing the issue
0
 
Scott PletcherSenior DBACommented:
Now that you've gotten that "log full" error, you need to have someone check that db immediately and verify that it is functioning correctly.  Whoever the most DBA-like person you can get to do this, and soon, as that db could be completely nonfunctional at the moment.
0
 
pcalabriaAuthor Commented:
No DBA person here, I'm the best we have.  :-(  We are a small company.

The database is NOT function and our website is now.  I'm in the process of copying it from the hosted server to our local machine because I expect security problems working in the hosted environment.

What will I need to do to empty or shrink the transaction log?
I'm pretty good with Access and I am the one who set up the MS-SQL server and I have used SSMS...so I'm not totally green... :-)

So once I copy it to my local machine I think I should be able to delete some stuff.  ???
0
 
Scott PletcherSenior DBACommented:
The fast way to fix is to add another log file, on a different drive if necessary with sufficient drive space, to the db(s) affected.  The new log file will start being used and you can resolve issues on the old log file while the db is up and running.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Being good with SSMS doesn't mean that you are good with SQL Server. You need to understand how databases work. In this case, how the Recovery Model works. Which options exists and which one you should chose for your database. And depending on that you need to define a backup plan, that it looks what are you missing here. Moving the database it's only postponing the problem. It will happen again soon in the future.
Please give a look on the Recovery Models.
0
 
pcalabriaAuthor Commented:
The database is used by our website, which is hosted by web.com.
I am not an administrator as we have a shared hosting plan.

The database uses a SIMPLE recovery model.
The database has reached maximum size based upon the space allocated to me
web.com is not able to increase the space allocated to my database.
I can reduce the size of my database if I delete rows of data that I don't care about
The problem is that I am unable to run queries that delete rows because I get a message that the query fails because the log file is full.

What should I do?

Thanks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Contact web.com and request their support on this.
0
 
pcalabriaAuthor Commented:
Thanks everyone, I solved the problem on my own.

I used SMSS to copy the database to my local machine, deleted unwanted data, used the shrink function, and then uploaded a more trim database.  It turns out the the log file was not the problem at all, the data section was obese.

I appreciated your attempts to help.  I did read the links and do lots of research on my own.
I will split the points as equally as possible.

Thanks again.
0
 
pcalabriaAuthor Commented:
I appreciated your attempts to help.  I did read the links and do lots of research on my own.
I will split the points as equally as possible.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now