Solved

Need help with delete query

Posted on 2014-11-18
17
117 Views
Last Modified: 2014-11-21
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
Comment
Question by:pcalabria
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 167 total points
ID: 40451168
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
 

Author Comment

by:pcalabria
ID: 40451175
Thanks, but why do we need to wait?
and can you explain what WaitForDelay is about?
0
 

Author Comment

by:pcalabria
ID: 40451181
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40451183
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40451187
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
 

Author Comment

by:pcalabria
ID: 40451188
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
 

Author Comment

by:pcalabria
ID: 40451740
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40451771
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40452540
you need to run that query in SSMS against the database which is experiencing the issue
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 total points
ID: 40452845
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
 

Author Comment

by:pcalabria
ID: 40452939
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40453005
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40454457
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
 

Author Comment

by:pcalabria
ID: 40456873
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40456877
Contact web.com and request their support on this.
0
 

Author Comment

by:pcalabria
ID: 40457299
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
 

Author Closing Comment

by:pcalabria
ID: 40457306
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now