Solved

MS-SQL 6.5, Command line sql

Posted on 2001-06-09
9
921 Views
Last Modified: 2007-12-19
I have inherited a MS-SQL 6.5 system and brand to to it.  One table has over 7million records, of which I need to purge out.  It seems I can only sql-delete about 100,000 records at one time - safely, otherwise I get record locking errors.  I would like to batch this process using the isql command-line tool.  
How could I enter the isql command prompt using batch files?  When I do "isql -U username", it prompts for the password.  Is there anyway to specify the password within the batch?  My plan is to do some kind of loop in a batch file to delete 100,000 records at a time.  It takes about an hour for each.

One other related question.  I am deleteing because of device space limits.  When I delete a couple of millions records, will I see the database usage go down immediately, or do I have to compact, pack or something like that?

Thanks.
0
Comment
Question by:j_k
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 50 total points
ID: 6172737
i guess u create a store proc

create proc PurgeTable as

begin

declare @rowcount int
select @rowcount = count(1) from mytable

while(@rowcount > 0)
begin
set rowcount 100000
delete mytable
select @rowcount = @rowcount - 100000
end

end

hope this helps.

u can even call this procedure from the isql command line itself.

Thanks.
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6172739
i guess even select * into is a non logged operation

select * into newtable from oldtable
truncate oldtable
0
 
LVL 3

Expert Comment

by:hink
ID: 6172870
Even if select into is nonlogged, it requires the same space in the log space (due to rollback when truncate command fails). The only way is to delete it using several delete commands step by step. To avoid locking errors only (when space requiring doesn't mind), delete with(tablock).
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 3

Expert Comment

by:hink
ID: 6172886
If You want to delete all rows, You can drop the table and recreate it. It takes not time.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6172890
You might try to specify the locking options:

DELETE MyTable (UPDLOCK,TABLOCK)
WHERE ...

Cheer
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6173023
You could

truncate table tbl

or

set rowcount 10000
  while exists (select * from tbl)
       delete tbl

set rowcount 0

You could even put a dely between the deletes if you have to do this while other users are doing things

set rowcount 10000
  while exists (select * from tbl)
  begin
       delete tbl
       waitfor delay, '00:00:05'
  end
set rowcount 0
0
 

Author Comment

by:j_k
ID: 6183774
I'll try to use something like this when I get in procedures.  For now, I used straight SQL and isql command-line to delete the records.

Another question to the forum - How do I reclaim the database space after removing 3million records?
0
 

Author Comment

by:j_k
ID: 6183775
I'll try to use something like this when I get in procedures.  For now, I used straight SQL and isql command-line to delete the records.

Another question to the forum - How do I reclaim the database space after removing 3million records?
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6186186
try shrink database.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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