Are there any programs that will shrink the size of a sql script file?

▲
4
▼

I have a SQL script which is extremely large (about 700 megabytes). I am wondering if there is a good way to reduce the size of the script?

I know there are code minimizers for JavaScript and am looking for one to use with SQL scripts.

I am not looking to get performance on the SQL script. I am trying to make the file size smaller. Removing excess whitespace. Keeping name-qualification down so that the script file sizes can be smaller.

If I attempt to load the file in SQL Server Management Studio I get this error.

Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008) (mscorlib)

sql
sql-server
asked on Stack Overflow Oct 20, 2009 by Brendan Enrick • edited Oct 20, 2009 by Brendan Enrick

7 Answers

▲
3
▼

What about breaking your script into several small files, and calling those files from a single master script?

This link describes how to do it from a stored procedure.

Or you can do it from a batch file like this:

REM =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
REM Define widely-used variables up here so they can be changed in one place
REM Search for "sqlcmd.exe" and make sure this path is valid for you
REM =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
set sqlcmd="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe"
set uname="your_uname_here"
set pwd="your_pwd_here"
set database="your_db_name_here"
set server="your_server_name_here"

%sqlcmd% -S %server% -d %database% -U %uname% -P %pwd% -i "c:\script1.sql"
%sqlcmd% -S %server% -d %database% -U %uname% -P %pwd% -i "c:\script2.sql"
%sqlcmd% -S %server% -d %database% -U %uname% -P %pwd% -i "c:\script3.sql"

pause

I like the batch file approach myself, because it is easier to tinker with it, and you can schedule it as a windows job.

Make sure the .BAT file is in a folder with the appropriate security restrictions, since it has your credentials in a plain text .BAT file.

answered on Stack Overflow Oct 20, 2009 by JosephStyons • edited Oct 20, 2009 by JosephStyons
▲
3
▼

What's in this script of 700MB?! I would hope that there are some similarities/repetitions that would allow it to shorten the file.

Just some guesses:

  • Instead of inserting a million records using Insert statements, use a bulk loading tool
  • Instead of updating a number of individual records, try to batch updates to the same value into one (e.g. Update tab set col=1 where id in (..) instead of individual updates)
  • long manipulations can be defined as a stored procedure (before running the script) and the script would only have to call the stored proc

Of course, splitting the script up into smaller portions and calling each one from a simple batch file would work too. But I'd be a little worried about performance (how long does the execution take?!) and would look for some faster ways.

answered on Stack Overflow Oct 20, 2009 by Thorsten
▲
1
▼

gzip should do.

answered on Stack Overflow Oct 20, 2009 by troelskn
▲
1
▼

SQL is much harder to shrink, the field, table names and commands need to be what they are. Plus, you wouldn't just want to rewrite the commands as something shorter because it could have implications on performance.

Depending on the DBMS that you use, it may allow short names for commands, and then there might be a converter.

answered on Stack Overflow Oct 20, 2009 by Lou Franco
▲
1
▼

(Answering this because it is the top item returned when I searched for "SQL script size")

I got the same error when trying to load a large script into Management Studio. In my case I was trying to downgrade a database from SQL2008 R2 to SQL 2008 by using the SQL Server script generator, which created a 700mb structure and data .sql file.

To get around it I used the command line to run the script instead:

C:>sqlcmd -S [SQLSERVER\INSTANCE] -i [FILELOCATION\FILENAME].sql

Hopefully this helps someone else.

answered on Stack Overflow Mar 27, 2012 by conradj
▲
0
▼

Compress the sql file will have the most compression ratio.

Minimizing the txt sql file will reduce some bytes/kilobytes per mega.. is not worth...

The better approach is to create a "function" to unzip and read the file. The best benefit I guess.

Today, filesize shouldn't be a problem. Dial-up connection? Floppy disks?

answered on Stack Overflow Oct 20, 2009 by Ismael
▲
0
▼

pg-minify can do it, and not just for PostgreSQL, but for most notations, including MS, MySql, etc.

answered on Stack Overflow Apr 1, 2016 by vitaly-t • edited Aug 3, 2017 by vitaly-t

User contributions licensed under CC BY-SA 3.0