This is terribly urgent, any advice is seriously appreciated. SQL v2K, I
have a procedure whcih bulk inserts 31 files nightly, using xp_fileexist,
like this:

BEGIN
DECLARE @fileExists4 INT
EXEC master.dbo.xp_fileExist '\\server\directory\filename.csv',@fileExists4
OUTPUT
IF @fileExists4 = 1
BEGIN
BULK INSERT database.dbo.table
FROM '\\server\directory\filename.csv'
WITH(FORMATFILE = 'E:\MSSQL\Tools\message.fmt',FIELDTERMINATOR =
',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH = 100000)
END
ELSE
PRINT 'filename does not exist.'
END


As I said, it walks thru 31 files just like that -- it has been in
production for more than a year, running 5 nights per week successfully...
until last night. It failed w/this error:


Server: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object '(SYSTEM table id: -499266526)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
The statement has been terminated.

My tempdb, mind you, was 3G at start up, with 1G log. It expanded to 4358MB
during execution, leaving only 9MB on the data drive. (not going to work)

I cleaned it all up, shrunk tempdev and tried again -- same failure. I then
walked thru each of the files and noticed one of them was near 2G itself. I
excluded that one in the procedure logic (commented it out), and re-ran the
whole thing, succesffully. Since then I have been busting my butt all day
long trying to get this darned file in --- every single attempt fails, same
error. I have changed it from 100000 ROWS_PER_BATCH, to 50000, to 25000,
even 5000 and 1000... every single one fails, same error. And, my tempdb
grows to near the capacity of the data drive upon every failed attempt to
load the file.

I don't know if this is an issue of too large a file. I have noted some
very large files in the past, but it has never failed w/this error. And, I
would be very surprised if that is the case -- bulk insert -- this file is
not even 2G, is that really the problem?

Best case scenario, I MUST get this one file in asap, as there will be
several people at the application layer looking for this data tomorrow.
Then, of course, I need to identify this problem and get around it. An
upgrade to v2008 is on the way, of course, but not soon enough to avoid a fix
for the v2000 instance.

Does anybody have any suggestions?

-- rlynn

Easiest solution is to add a data file to tempdb on another disk. See BOL
for how to do this.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"rlynn" <rlynn@discussions.microsoft.com> wrote in message
news:B54B3210-8938-48F2-9E4A-B168CB5C1957@microsoft.com...
> This is terribly urgent, any advice is seriously appreciated. SQL v2K, I
> have a procedure whcih bulk inserts 31 files nightly, using xp_fileexist,
> like this:
>
> BEGIN
> DECLARE @fileExists4 INT
> EXEC master.dbo.xp_fileExist
> '\\server\directory\filename.csv',@fileExists4
> OUTPUT
> IF @fileExists4 = 1
> BEGIN
> BULK INSERT database.dbo.table
> FROM '\\server\directory\filename.csv'
> WITH(FORMATFILE = 'E:\MSSQL\Tools\message.fmt',FIELDTERMINATOR =
> ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH = 100000)
> END
> ELSE
> PRINT 'filename does not exist.'
> END
>
>
> As I said, it walks thru 31 files just like that -- it has been in
> production for more than a year, running 5 nights per week successfully...
> until last night. It failed w/this error:
>
>
> Server: Msg 1105, Level 17, State 2, Line 1
> Could not allocate space for object '(SYSTEM table id: -499266526)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> The statement has been terminated.
>
> My tempdb, mind you, was 3G at start up, with 1G log. It expanded to
> 4358MB
> during execution, leaving only 9MB on the data drive. (not going to work)
>
> I cleaned it all up, shrunk tempdev and tried again -- same failure. I
> then
> walked thru each of the files and noticed one of them was near 2G itself.
> I
> excluded that one in the procedure logic (commented it out), and re-ran
> the
> whole thing, succesffully. Since then I have been busting my butt all day
> long trying to get this darned file in --- every single attempt fails,
> same
> error. I have changed it from 100000 ROWS_PER_BATCH, to 50000, to 25000,
> even 5000 and 1000... every single one fails, same error. And, my tempdb
> grows to near the capacity of the data drive upon every failed attempt to
> load the file.
>
> I don't know if this is an issue of too large a file. I have noted some
> very large files in the past, but it has never failed w/this error. And,
> I
> would be very surprised if that is the case -- bulk insert -- this file is
> not even 2G, is that really the problem?
>
> Best case scenario, I MUST get this one file in asap, as there will be
> several people at the application layer looking for this data tomorrow.
> Then, of course, I need to identify this problem and get around it. An
> upgrade to v2008 is on the way, of course, but not soon enough to avoid a
> fix
> for the v2000 instance.
>
> Does anybody have any suggestions?
>
> -- rlynn

Are you saying the cause of this is the tempdb is not large enough? And if
so, is it a matter of adding another data file to the existing, or moving the
entire tempdb to another disk? Specifically, which BOL doc is it, please?
-- rlynn


"TheSQLGuru" wrote:

> Easiest solution is to add a data file to tempdb on another disk. See BOL
> for how to do this.
>
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "rlynn" <rlynn@discussions.microsoft.com> wrote in message
> news:B54B3210-8938-48F2-9E4A-B168CB5C1957@microsoft.com...
> > This is terribly urgent, any advice is seriously appreciated. SQL v2K, I
> > have a procedure whcih bulk inserts 31 files nightly, using xp_fileexist,
> > like this:
> >
> > BEGIN
> > DECLARE @fileExists4 INT
> > EXEC master.dbo.xp_fileExist
> > '\\server\directory\filename.csv',@fileExists4
> > OUTPUT
> > IF @fileExists4 = 1
> > BEGIN
> > BULK INSERT database.dbo.table
> > FROM '\\server\directory\filename.csv'
> > WITH(FORMATFILE = 'E:\MSSQL\Tools\message.fmt',FIELDTERMINATOR =
> > ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH = 100000)
> > END
> > ELSE
> > PRINT 'filename does not exist.'
> > END
> >
> >
> > As I said, it walks thru 31 files just like that -- it has been in
> > production for more than a year, running 5 nights per week successfully...
> > until last night. It failed w/this error:
> >
> >
> > Server: Msg 1105, Level 17, State 2, Line 1
> > Could not allocate space for object '(SYSTEM table id: -499266526)' in
> > database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> > The statement has been terminated.
> >
> > My tempdb, mind you, was 3G at start up, with 1G log. It expanded to
> > 4358MB
> > during execution, leaving only 9MB on the data drive. (not going to work)
> >
> > I cleaned it all up, shrunk tempdev and tried again -- same failure. I
> > then
> > walked thru each of the files and noticed one of them was near 2G itself.
> > I
> > excluded that one in the procedure logic (commented it out), and re-ran
> > the
> > whole thing, succesffully. Since then I have been busting my butt all day
> > long trying to get this darned file in --- every single attempt fails,
> > same
> > error. I have changed it from 100000 ROWS_PER_BATCH, to 50000, to 25000,
> > even 5000 and 1000... every single one fails, same error. And, my tempdb
> > grows to near the capacity of the data drive upon every failed attempt to
> > load the file.
> >
> > I don't know if this is an issue of too large a file. I have noted some
> > very large files in the past, but it has never failed w/this error. And,
> > I
> > would be very surprised if that is the case -- bulk insert -- this file is
> > not even 2G, is that really the problem?
> >
> > Best case scenario, I MUST get this one file in asap, as there will be
> > several people at the application layer looking for this data tomorrow.
> > Then, of course, I need to identify this problem and get around it. An
> > upgrade to v2008 is on the way, of course, but not soon enough to avoid a
> > fix
> > for the v2000 instance.
> >
> > Does anybody have any suggestions?
> >
> > -- rlynn
>
>
>

sp_file to get the logical names, then alter database modify file to move
them all (?) to the new disk, then restart sql server, and expand them after
startup?
is that it?


i say 'all' just as in tempdev and templog. is that it?

-- rlynn


"rlynn" wrote:

> Are you saying the cause of this is the tempdb is not large enough? And if
> so, is it a matter of adding another data file to the existing, or moving the
> entire tempdb to another disk? Specifically, which BOL doc is it, please?
> -- rlynn
>
>
> "TheSQLGuru" wrote:
>
> > Easiest solution is to add a data file to tempdb on another disk. See BOL
> > for how to do this.
> >
> >
> > --
> > Kevin G. Boles
> > Indicium Resources, Inc.
> > SQL Server MVP
> > kgboles a earthlink dt net
> >
> >
> > "rlynn" <rlynn@discussions.microsoft.com> wrote in message
> > news:B54B3210-8938-48F2-9E4A-B168CB5C1957@microsoft.com...
> > > This is terribly urgent, any advice is seriously appreciated. SQL v2K, I
> > > have a procedure whcih bulk inserts 31 files nightly, using xp_fileexist,
> > > like this:
> > >
> > > BEGIN
> > > DECLARE @fileExists4 INT
> > > EXEC master.dbo.xp_fileExist
> > > '\\server\directory\filename.csv',@fileExists4
> > > OUTPUT
> > > IF @fileExists4 = 1
> > > BEGIN
> > > BULK INSERT database.dbo.table
> > > FROM '\\server\directory\filename.csv'
> > > WITH(FORMATFILE = 'E:\MSSQL\Tools\message.fmt',FIELDTERMINATOR =
> > > ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH = 100000)
> > > END
> > > ELSE
> > > PRINT 'filename does not exist.'
> > > END
> > >
> > >
> > > As I said, it walks thru 31 files just like that -- it has been in
> > > production for more than a year, running 5 nights per week successfully...
> > > until last night. It failed w/this error:
> > >
> > >
> > > Server: Msg 1105, Level 17, State 2, Line 1
> > > Could not allocate space for object '(SYSTEM table id: -499266526)' in
> > > database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> > > The statement has been terminated.
> > >
> > > My tempdb, mind you, was 3G at start up, with 1G log. It expanded to
> > > 4358MB
> > > during execution, leaving only 9MB on the data drive. (not going to work)
> > >
> > > I cleaned it all up, shrunk tempdev and tried again -- same failure. I
> > > then
> > > walked thru each of the files and noticed one of them was near 2G itself.
> > > I
> > > excluded that one in the procedure logic (commented it out), and re-ran
> > > the
> > > whole thing, succesffully. Since then I have been busting my butt all day
> > > long trying to get this darned file in --- every single attempt fails,
> > > same
> > > error. I have changed it from 100000 ROWS_PER_BATCH, to 50000, to 25000,
> > > even 5000 and 1000... every single one fails, same error. And, my tempdb
> > > grows to near the capacity of the data drive upon every failed attempt to
> > > load the file.
> > >
> > > I don't know if this is an issue of too large a file. I have noted some
> > > very large files in the past, but it has never failed w/this error. And,
> > > I
> > > would be very surprised if that is the case -- bulk insert -- this file is
> > > not even 2G, is that really the problem?
> > >
> > > Best case scenario, I MUST get this one file in asap, as there will be
> > > several people at the application layer looking for this data tomorrow.
> > > Then, of course, I need to identify this problem and get around it. An
> > > upgrade to v2008 is on the way, of course, but not soon enough to avoid a
> > > fix
> > > for the v2000 instance.
> > >
> > > Does anybody have any suggestions?
> > >
> > > -- rlynn
> >
> >
> >

well, that's what I did... but, on a dev box. i just altered tempdb modified
file, like this example: http://support.microsoft.com/kb/224071

then i restarted sql server and sql server agent.

sp_file now shows them in the 'new' location, log looks good, no errors
anywhere that i can find.

is that really it? do you feel that is the reason for this failure?

great, if that is it, that will get me to the point when i can upgrade...
but, i don't mind saying i'm scared to do this on the prod box. i've never
had this problem before, a little nervous, three to four hours before the open

what do you think?

-- rlynn


"rlynn" wrote:

> sp_file to get the logical names, then alter database modify file to move
> them all (?) to the new disk, then restart sql server, and expand them after
> startup?
> is that it?
>
>
> i say 'all' just as in tempdev and templog. is that it?
>
> -- rlynn
>
>
> "rlynn" wrote:
>
> > Are you saying the cause of this is the tempdb is not large enough? And if
> > so, is it a matter of adding another data file to the existing, or moving the
> > entire tempdb to another disk? Specifically, which BOL doc is it, please?
> > -- rlynn
> >
> >
> > "TheSQLGuru" wrote:
> >
> > > Easiest solution is to add a data file to tempdb on another disk. See BOL
> > > for how to do this.
> > >
> > >
> > > --
> > > Kevin G. Boles
> > > Indicium Resources, Inc.
> > > SQL Server MVP
> > > kgboles a earthlink dt net
> > >
> > >
> > > "rlynn" <rlynn@discussions.microsoft.com> wrote in message
> > > news:B54B3210-8938-48F2-9E4A-B168CB5C1957@microsoft.com...
> > > > This is terribly urgent, any advice is seriously appreciated. SQL v2K, I
> > > > have a procedure whcih bulk inserts 31 files nightly, using xp_fileexist,
> > > > like this:
> > > >
> > > > BEGIN
> > > > DECLARE @fileExists4 INT
> > > > EXEC master.dbo.xp_fileExist
> > > > '\\server\directory\filename.csv',@fileExists4
> > > > OUTPUT
> > > > IF @fileExists4 = 1
> > > > BEGIN
> > > > BULK INSERT database.dbo.table
> > > > FROM '\\server\directory\filename.csv'
> > > > WITH(FORMATFILE = 'E:\MSSQL\Tools\message.fmt',FIELDTERMINATOR =
> > > > ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH = 100000)
> > > > END
> > > > ELSE
> > > > PRINT 'filename does not exist.'
> > > > END
> > > >
> > > >
> > > > As I said, it walks thru 31 files just like that -- it has been in
> > > > production for more than a year, running 5 nights per week successfully...
> > > > until last night. It failed w/this error:
> > > >
> > > >
> > > > Server: Msg 1105, Level 17, State 2, Line 1
> > > > Could not allocate space for object '(SYSTEM table id: -499266526)' in
> > > > database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> > > > The statement has been terminated.
> > > >
> > > > My tempdb, mind you, was 3G at start up, with 1G log. It expanded to
> > > > 4358MB
> > > > during execution, leaving only 9MB on the data drive. (not going to work)
> > > >
> > > > I cleaned it all up, shrunk tempdev and tried again -- same failure. I
> > > > then
> > > > walked thru each of the files and noticed one of them was near 2G itself.
> > > > I
> > > > excluded that one in the procedure logic (commented it out), and re-ran
> > > > the
> > > > whole thing, succesffully. Since then I have been busting my butt all day
> > > > long trying to get this darned file in --- every single attempt fails,
> > > > same
> > > > error. I have changed it from 100000 ROWS_PER_BATCH, to 50000, to 25000,
> > > > even 5000 and 1000... every single one fails, same error. And, my tempdb
> > > > grows to near the capacity of the data drive upon every failed attempt to
> > > > load the file.
> > > >
> > > > I don't know if this is an issue of too large a file. I have noted some
> > > > very large files in the past, but it has never failed w/this error. And,
> > > > I
> > > > would be very surprised if that is the case -- bulk insert -- this file is
> > > > not even 2G, is that really the problem?
> > > >
> > > > Best case scenario, I MUST get this one file in asap, as there will be
> > > > several people at the application layer looking for this data tomorrow.
> > > > Then, of course, I need to identify this problem and get around it. An
> > > > upgrade to v2008 is on the way, of course, but not soon enough to avoid a
> > > > fix
> > > > for the v2000 instance.
> > > >
> > > > Does anybody have any suggestions?
> > > >
> > > > -- rlynn
> > >
> > >
> > >

also, one last thing -- the old ones are still in the old location. need i
really delete them?

-- rlynn


"rlynn" wrote:

> sp_file to get the logical names, then alter database modify file to move
> them all (?) to the new disk, then restart sql server, and expand them after
> startup?
> is that it?
>
>
> i say 'all' just as in tempdev and templog. is that it?
>
> -- rlynn
>
>
> "rlynn" wrote:
>
> > Are you saying the cause of this is the tempdb is not large enough? And if
> > so, is it a matter of adding another data file to the existing, or moving the
> > entire tempdb to another disk? Specifically, which BOL doc is it, please?
> > -- rlynn
> >
> >
> > "TheSQLGuru" wrote:
> >
> > > Easiest solution is to add a data file to tempdb on another disk. See BOL
> > > for how to do this.
> > >
> > >
> > > --
> > > Kevin G. Boles
> > > Indicium Resources, Inc.
> > > SQL Server MVP
> > > kgboles a earthlink dt net
> > >
> > >
> > > "rlynn" <rlynn@discussions.microsoft.com> wrote in message
> > > news:B54B3210-8938-48F2-9E4A-B168CB5C1957@microsoft.com...
> > > > This is terribly urgent, any advice is seriously appreciated. SQL v2K, I
> > > > have a procedure whcih bulk inserts 31 files nightly, using xp_fileexist,
> > > > like this:
> > > >
> > > > BEGIN
> > > > DECLARE @fileExists4 INT
> > > > EXEC master.dbo.xp_fileExist
> > > > '\\server\directory\filename.csv',@fileExists4
> > > > OUTPUT
> > > > IF @fileExists4 = 1
> > > > BEGIN
> > > > BULK INSERT database.dbo.table
> > > > FROM '\\server\directory\filename.csv'
> > > > WITH(FORMATFILE = 'E:\MSSQL\Tools\message.fmt',FIELDTERMINATOR =
> > > > ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH = 100000)
> > > > END
> > > > ELSE
> > > > PRINT 'filename does not exist.'
> > > > END
> > > >
> > > >
> > > > As I said, it walks thru 31 files just like that -- it has been in
> > > > production for more than a year, running 5 nights per week successfully...
> > > > until last night. It failed w/this error:
> > > >
> > > >
> > > > Server: Msg 1105, Level 17, State 2, Line 1
> > > > Could not allocate space for object '(SYSTEM table id: -499266526)' in
> > > > database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> > > > The statement has been terminated.
> > > >
> > > > My tempdb, mind you, was 3G at start up, with 1G log. It expanded to
> > > > 4358MB
> > > > during execution, leaving only 9MB on the data drive. (not going to work)
> > > >
> > > > I cleaned it all up, shrunk tempdev and tried again -- same failure. I
> > > > then
> > > > walked thru each of the files and noticed one of them was near 2G itself.
> > > > I
> > > > excluded that one in the procedure logic (commented it out), and re-ran
> > > > the
> > > > whole thing, succesffully. Since then I have been busting my butt all day
> > > > long trying to get this darned file in --- every single attempt fails,
> > > > same
> > > > error. I have changed it from 100000 ROWS_PER_BATCH, to 50000, to 25000,
> > > > even 5000 and 1000... every single one fails, same error. And, my tempdb
> > > > grows to near the capacity of the data drive upon every failed attempt to
> > > > load the file.
> > > >
> > > > I don't know if this is an issue of too large a file. I have noted some
> > > > very large files in the past, but it has never failed w/this error. And,
> > > > I
> > > > would be very surprised if that is the case -- bulk insert -- this file is
> > > > not even 2G, is that really the problem?
> > > >
> > > > Best case scenario, I MUST get this one file in asap, as there will be
> > > > several people at the application layer looking for this data tomorrow.
> > > > Then, of course, I need to identify this problem and get around it. An
> > > > upgrade to v2008 is on the way, of course, but not soon enough to avoid a
> > > > fix
> > > > for the v2000 instance.
> > > >
> > > > Does anybody have any suggestions?
> > > >
> > > > -- rlynn
> > >
> > >
> > >

> also, one last thing -- the old ones are still in the old location. need
> i
> really delete them?

It is safe to delete the old tempdb files.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"rlynn" <rlynn@discussions.microsoft.com> wrote in message
news:8C3AC221-D6DE-43E1-9D34-E68FC374100E@microsoft.com...
> also, one last thing -- the old ones are still in the old location. need
> i
> really delete them?
>
> -- rlynn
>
>
> "rlynn" wrote:
>
>> sp_file to get the logical names, then alter database modify file to
>> move
>> them all (?) to the new disk, then restart sql server, and expand them
>> after
>> startup?
>> is that it?
>>
>>
>> i say 'all' just as in tempdev and templog. is that it?
>>
>> -- rlynn
>>
>>
>> "rlynn" wrote:
>>
>> > Are you saying the cause of this is the tempdb is not large enough?
>> > And if
>> > so, is it a matter of adding another data file to the existing, or
>> > moving the
>> > entire tempdb to another disk? Specifically, which BOL doc is it,
>> > please?
>> > -- rlynn
>> >
>> >
>> > "TheSQLGuru" wrote:
>> >
>> > > Easiest solution is to add a data file to tempdb on another disk.
>> > > See BOL
>> > > for how to do this.
>> > >
>> > >
>> > > --
>> > > Kevin G. Boles
>> > > Indicium Resources, Inc.
>> > > SQL Server MVP
>> > > kgboles a earthlink dt net
>> > >
>> > >
>> > > "rlynn" <rlynn@discussions.microsoft.com> wrote in message
>> > > news:B54B3210-8938-48F2-9E4A-B168CB5C1957@microsoft.com...
>> > > > This is terribly urgent, any advice is seriously appreciated. SQL
>> > > > v2K, I
>> > > > have a procedure whcih bulk inserts 31 files nightly, using
>> > > > xp_fileexist,
>> > > > like this:
>> > > >
>> > > > BEGIN
>> > > > DECLARE @fileExists4 INT
>> > > > EXEC master.dbo.xp_fileExist
>> > > > '\\server\directory\filename.csv',@fileExists4
>> > > > OUTPUT
>> > > > IF @fileExists4 = 1
>> > > > BEGIN
>> > > > BULK INSERT database.dbo.table
>> > > > FROM '\\server\directory\filename.csv'
>> > > > WITH(FORMATFILE = 'E:\MSSQL\Tools\message.fmt',FIELDTERMINATOR =
>> > > > ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH =
>> > > > 100000)
>> > > > END
>> > > > ELSE
>> > > > PRINT 'filename does not exist.'
>> > > > END
>> > > >
>> > > >
>> > > > As I said, it walks thru 31 files just like that -- it has been in
>> > > > production for more than a year, running 5 nights per week
>> > > > successfully...
>> > > > until last night. It failed w/this error:
>> > > >
>> > > >
>> > > > Server: Msg 1105, Level 17, State 2, Line 1
>> > > > Could not allocate space for object '(SYSTEM table id: -499266526)'
>> > > > in
>> > > > database 'TEMPDB' because the 'DEFAULT' filegroup is full.
>> > > > The statement has been terminated.
>> > > >
>> > > > My tempdb, mind you, was 3G at start up, with 1G log. It expanded
>> > > > to
>> > > > 4358MB
>> > > > during execution, leaving only 9MB on the data drive. (not going
>> > > > to work)
>> > > >
>> > > > I cleaned it all up, shrunk tempdev and tried again -- same
>> > > > failure. I
>> > > > then
>> > > > walked thru each of the files and noticed one of them was near 2G
>> > > > itself.
>> > > > I
>> > > > excluded that one in the procedure logic (commented it out), and
>> > > > re-ran
>> > > > the
>> > > > whole thing, succesffully. Since then I have been busting my butt
>> > > > all day
>> > > > long trying to get this darned file in --- every single attempt
>> > > > fails,
>> > > > same
>> > > > error. I have changed it from 100000 ROWS_PER_BATCH, to 50000, to
>> > > > 25000,
>> > > > even 5000 and 1000... every single one fails, same error. And, my
>> > > > tempdb
>> > > > grows to near the capacity of the data drive upon every failed
>> > > > attempt to
>> > > > load the file.
>> > > >
>> > > > I don't know if this is an issue of too large a file. I have noted
>> > > > some
>> > > > very large files in the past, but it has never failed w/this error.
>> > > > And,
>> > > > I
>> > > > would be very surprised if that is the case -- bulk insert -- this
>> > > > file is
>> > > > not even 2G, is that really the problem?
>> > > >
>> > > > Best case scenario, I MUST get this one file in asap, as there will
>> > > > be
>> > > > several people at the application layer looking for this data
>> > > > tomorrow.
>> > > > Then, of course, I need to identify this problem and get around it.
>> > > > An
>> > > > upgrade to v2008 is on the way, of course, but not soon enough to
>> > > > avoid a
>> > > > fix
>> > > > for the v2000 instance.
>> > > >
>> > > > Does anybody have any suggestions?
>> > > >
>> > > > -- rlynn
>> > >
>> > >
>> > >

yep, that was it. having moved/expanded tempdb, it completed w/out error.
and... i deleted the old tempdb files.

thank you both very much.
-- rlynn


"Dan Guzman" wrote:

> > also, one last thing -- the old ones are still in the old location. need
> > i
> > really delete them?
>
> It is safe to delete the old tempdb files.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "rlynn" <rlynn@discussions.microsoft.com> wrote in message
> news:8C3AC221-D6DE-43E1-9D34-E68FC374100E@microsoft.com...
> > also, one last thing -- the old ones are still in the old location. need
> > i
> > really delete them?
> >
> > -- rlynn
> >
> >
> > "rlynn" wrote:
> >
> >> sp_file to get the logical names, then alter database modify file to
> >> move
> >> them all (?) to the new disk, then restart sql server, and expand them
> >> after
> >> startup?
> >> is that it?
> >>
> >>
> >> i say 'all' just as in tempdev and templog. is that it?
> >>
> >> -- rlynn
> >>
> >>
> >> "rlynn" wrote:
> >>
> >> > Are you saying the cause of this is the tempdb is not large enough?
> >> > And if
> >> > so, is it a matter of adding another data file to the existing, or
> >> > moving the
> >> > entire tempdb to another disk? Specifically, which BOL doc is it,
> >> > please?
> >> > -- rlynn
> >> >
> >> >
> >> > "TheSQLGuru" wrote:
> >> >
> >> > > Easiest solution is to add a data file to tempdb on another disk.
> >> > > See BOL
> >> > > for how to do this.
> >> > >
> >> > >
> >> > > --
> >> > > Kevin G. Boles
> >> > > Indicium Resources, Inc.
> >> > > SQL Server MVP
> >> > > kgboles a earthlink dt net
> >> > >
> >> > >
> >> > > "rlynn" <rlynn@discussions.microsoft.com> wrote in message
> >> > > news:B54B3210-8938-48F2-9E4A-B168CB5C1957@microsoft.com...
> >> > > > This is terribly urgent, any advice is seriously appreciated. SQL
> >> > > > v2K, I
> >> > > > have a procedure whcih bulk inserts 31 files nightly, using
> >> > > > xp_fileexist,
> >> > > > like this:
> >> > > >
> >> > > > BEGIN
> >> > > > DECLARE @fileExists4 INT
> >> > > > EXEC master.dbo.xp_fileExist
> >> > > > '\\server\directory\filename.csv',@fileExists4
> >> > > > OUTPUT
> >> > > > IF @fileExists4 = 1
> >> > > > BEGIN
> >> > > > BULK INSERT database.dbo.table
> >> > > > FROM '\\server\directory\filename.csv'
> >> > > > WITH(FORMATFILE = 'E:\MSSQL\Tools\message.fmt',FIELDTERMINATOR =
> >> > > > ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH =
> >> > > > 100000)
> >> > > > END
> >> > > > ELSE
> >> > > > PRINT 'filename does not exist.'
> >> > > > END
> >> > > >
> >> > > >
> >> > > > As I said, it walks thru 31 files just like that -- it has been in
> >> > > > production for more than a year, running 5 nights per week
> >> > > > successfully...
> >> > > > until last night. It failed w/this error:
> >> > > >
> >> > > >
> >> > > > Server: Msg 1105, Level 17, State 2, Line 1
> >> > > > Could not allocate space for object '(SYSTEM table id: -499266526)'
> >> > > > in
> >> > > > database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> >> > > > The statement has been terminated.
> >> > > >
> >> > > > My tempdb, mind you, was 3G at start up, with 1G log. It expanded
> >> > > > to
> >> > > > 4358MB
> >> > > > during execution, leaving only 9MB on the data drive. (not going
> >> > > > to work)
> >> > > >
> >> > > > I cleaned it all up, shrunk tempdev and tried again -- same
> >> > > > failure. I
> >> > > > then
> >> > > > walked thru each of the files and noticed one of them was near 2G
> >> > > > itself.
> >> > > > I
> >> > > > excluded that one in the procedure logic (commented it out), and
> >> > > > re-ran
> >> > > > the
> >> > > > whole thing, succesffully. Since then I have been busting my butt
> >> > > > all day
> >> > > > long trying to get this darned file in --- every single attempt
> >> > > > fails,
> >> > > > same
> >> > > > error. I have changed it from 100000 ROWS_PER_BATCH, to 50000, to
> >> > > > 25000,
> >> > > > even 5000 and 1000... every single one fails, same error. And, my
> >> > > > tempdb
> >> > > > grows to near the capacity of the data drive upon every failed
> >> > > > attempt to
> >> > > > load the file.
> >> > > >
> >> > > > I don't know if this is an issue of too large a file. I have noted
> >> > > > some
> >> > > > very large files in the past, but it has never failed w/this error.
> >> > > > And,
> >> > > > I
> >> > > > would be very surprised if that is the case -- bulk insert -- this
> >> > > > file is
> >> > > > not even 2G, is that really the problem?
> >> > > >
> >> > > > Best case scenario, I MUST get this one file in asap, as there will
> >> > > > be
> >> > > > several people at the application layer looking for this data
> >> > > > tomorrow.
> >> > > > Then, of course, I need to identify this problem and get around it.
> >> > > > An
> >> > > > upgrade to v2008 is on the way, of course, but not soon enough to
> >> > > > avoid a
> >> > > > fix
> >> > > > for the v2000 instance.
> >> > > >
> >> > > > Does anybody have any suggestions?
> >> > > >
> >> > > > -- rlynn
> >> > >
> >> > >
> >> > >
>
>

I may have a related problem.
I was testing a bulk insert operation a couple of months ago and since
the data was sorted according to primary key, the data went straight
from the input file to the data file.
When testing it again today, all of a sudden, the tempdb is used to
store all the data first.

In your case, it may be the addition of an index to the source table
that causes an increase of tempdb use.


*** Sent via Developersdex http://www.developersdex.com ***