Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS-SQL 6.5, Command line sql

Posted on 2001-06-09
9
Medium Priority
?
950 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
[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
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 200 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 143

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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