Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 956
  • Last Modified:

MS-SQL 6.5, Command line sql

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
j_k
Asked:
j_k
  • 2
  • 2
  • 2
  • +2
1 Solution
 
amitpagarwalCommented:
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
 
amitpagarwalCommented:
i guess even select * into is a non logged operation

select * into newtable from oldtable
truncate oldtable
0
 
hinkCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
hinkCommented:
If You want to delete all rows, You can drop the table and recreate it. It takes not time.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You might try to specify the locking options:

DELETE MyTable (UPDLOCK,TABLOCK)
WHERE ...

Cheer
0
 
nigelrivettCommented:
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
 
j_kAuthor Commented:
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
 
j_kAuthor Commented:
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
 
nigelrivettCommented:
try shrink database.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now