Solved

Need help with delete query

Posted on 2014-11-18
17
121 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 47

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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 26 64
Linked Server Issue with SQL2012 3 26
SQL Insert parts by customer 12 34
sql 2008 how to table join 2 17
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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