sql query out of memory error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query out of memory error

Author

Topic

agismarkos
Starting Member

17 Posts

Posted - 2012-08-13 : 09:42:54

hello ,
they have give me an sql query that is suppose to build the tables i need for my work
problem is that te query is way to big. Its 700mb of size . I get a system out of memory error. My pc has 5gb installed memmory . The 3.2gb are usually free for the sql.
Is there any workaround or a way i could change my query?
thank you,
Agisilaos

the query is:

USE [ShippingDev2]
GO
/****** Object: Table [dbo].[Ship_Type_Codes] Script Date: 06/29/2012 19:26:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Ship_Type_Codes](
[code] [tinyint] NOT NULL,
[ship_type] [varchar](50) NOT NULL,
[hazard_cat] [char](1) NULL,
[all_ships_of_type] [bit] NOT NULL,
[reserved_future_use] [bit] NOT NULL,
[no_additional_info] [bit] NOT NULL,
[other] [varchar](50) NULL,
CONSTRAINT [PK_Ship_Type_Codes] PRIMARY KEY CLUSTERED
(
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Ship_Type_Codes] ([code], [ship_type], [hazard_cat], [all_ships_of_type], [reserved_future_use], [no_additional_info], [other]) VALUES (20, N'Wing in Ground', N' ', 1, 0, 0, N'')
INSERT [dbo].[Ship_Type_Codes] ([code], [ship_type], [hazard_cat], [all_ships_of_type], [reserved_future_use], [no_additional_info], [other]) VALUES (21, N'Wing in Ground', N'A', 0, 0, 0, N'')

/***** inserts continue for many many lines and i casually see this line between inserts ************************/
GO
print 'Processed 574000 total records'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-13 : 09:57:30

Break up the INSERT statements into multiple files and run them separately. I'd suggest no more than 50,000 rows per file.

agismarkos
Starting Member

17 Posts

Posted - 2012-08-13 : 10:10:12

is there any easy way to select the lines?
the classic shift + pagedown is hard . there are over 300.000 lines there

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-13 : 10:45:42

Grab a Unix split utility, there's a Windows version here: http://unxutils.sourceforge.net/

Powershell can also do it, Google "powershell split files".

Or if you want to be hardcore, do it in SQL:

create table script(line nvarchar(max) null)
bulk insert script from 'x:\original_script.sql' with(fieldterminator='')
delete script where line not like 'INSERT%'

That will create a table and import all the SQL text, 1 line per row. It will then remove all lines that are not INSERT statements. Then use the bcp utility from the command line:

bcp "mydb.dbo.script" out script1.sql -Sserver -T -c -F1 -L50000
bcp "mydb.dbo.script" out script2.sql -Sserver -T -c -F50001 -L100000
bcp "mydb.dbo.script" out script3.sql -Sserver -T -c -F100001 -L150000
bcp "mydb.dbo.script" out script4.sql -Sserver -T -c -F150001 -L200000
bcp "mydb.dbo.script" out script5.sql -Sserver -T -c -F200001 -L250000
bcp "mydb.dbo.script" out script6.sql -Sserver -T -c -F250001 -L300000
bcp "mydb.dbo.script" out script7.sql -Sserver -T -c -F300001 -L350000
bcp "mydb.dbo.script" out script8.sql -Sserver -T -c -F350001 -L400000

However you split them, you then run all those SQL scripts, which can also be done handily from the command line:

for %a in (script*.sql) do sqlcmd -Sserver -E -dShippingDev2 -i"%a"

Change sample server, database and file names to match your settings.

agismarkos
Starting Member

17 Posts

Posted - 2012-08-13 : 11:20:57

ok!
thank you very much!
Agisilaos

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources