Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using sqlcmd.exe to run a large SQL script, and the server CPU usage has been at 100% for the past 5 hours. The SQL script file has a size of 156 MB. I am running the script on SQL Server 2005. This does not look normal to me.

Using SQL Server Management Studio to run the script is not an option, since it won't run very large files.

Note: I know the SQL script is good, since I was able to run it on my local machine with another tool (xSQL data). Unfortunately, I don't have access to that tool on the server.

share|improve this question
    
In my experience, high CPU means index issues. How big is the script? Can you post part of it? –  datagod Sep 28 '11 at 4:41
    
The script file has a size of 156 MB. Unfortunately, I cannot post it, since it would expose my client's data. Finally, the script ended after 5 1/2 hours with the following error message: Sqlcmd: Error: Syntax error at line 619987 in file 'SyncMammouthDev.sql'. However, this may be due to the fact that I generated the script on SQL Server Express 2008 RC and I was trying to run it on SQL Server 2005. –  Jean-François Beauchamp Sep 28 '11 at 4:52
    
Can you elaborate on "index issues"? –  Jean-François Beauchamp Sep 28 '11 at 4:54
1  
Is it possible to break the script down into smaller parts? I assume if the script is as big as you say it is, you are doing some kind of data import. Can it be split into transactional blocks? –  Miles D Sep 28 '11 at 7:20
    
@MilesD I guess it could, but it would be a lot of work, I found another solution. See below... –  Jean-François Beauchamp Sep 28 '11 at 15:46

1 Answer 1

up vote 1 down vote accepted

If the sql script is 156 MB I suppose that you're doing data load using a single statement for each row.

Is that everything in one big batch (all statements one after the other without GO)? If yes, then SQL Server will try to parse that file all at once... on a file that big you're killing the CPU.

Suggestions:

  • can you split that file in more than one batch? so the parsing will be done batch after batch..
  • can you load the data from bcp files instead of sql file?
share|improve this answer
    
You were right. It is a script to load data in the database, and although there are a few 'GO' at the beginning of the script where triggers are being disabled and constraints are dropped, there is only one GO command for the big bunch of INSERT statements. –  Jean-François Beauchamp Sep 28 '11 at 15:32
    
Glad to be of service. I'd still suggest that instead of doing a big SQL file (even with small batches), you'd get huge improvements by going the BCP import route. –  Marian Sep 28 '11 at 15:35
    
Can BCP handle an SQL script? Because I don't think the tool I am using can generate something else than SQL scripts. Here how I solved my problem: The file was generated by xSQL Data Compare, and it would have been quite time consuming to edit it to add more GO statements. However, my copy of xSQL Data Compare is able to run the script on my local instance of SQL Server Express, so I backed up the remote database I was working on and restored it locally. I was then able to run the script with the tool on my local machine and to bring back that database to the remote server. –  Jean-François Beauchamp Sep 28 '11 at 15:38
1  
You can also use SSIS or the import / export wizard (which uses ssis) to transfer the data from your local copy to the remote copy. See if your tool has a batch size option as well. Furthermore, data compare might not be the best approach for this unless it's a one time deal. –  Eric Humphrey - lotsahelp Sep 28 '11 at 16:01
    
@Jean-FrançoisBeauchamp: nope, BCP is a command line tool used to transfer tables' data in files and then back in tables. It handles binary files, not sql files. Instead of generating comparison scripts with a diff tool, you could build a more general script that takes a tables list, goes through the differences between each table in both dbs, builds staging tables for the differences, use Bcp to generate binary files.. than use those files in the second environment. This is a bit of a hassle at the beginning, to build the flow, but it will work much faster than generating huge SQL files. –  Marian Sep 29 '11 at 13:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.