Solved

Need help with delete query

Posted on 2014-11-18
17
126 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 50

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
 
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:Scott Pletcher
Scott Pletcher 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:Scott Pletcher
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 50

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 50

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Checking for column changes SQL 2014 4 42
grouping by date only 6 22
Replacing unrecognized function name in SQL Query 4 48
Complex SQL Server WHERE CLause 9 40
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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