log files

I'm using SQL Server 2005 Developer Edition and doing mass data insertion. I
n
order to optimize for speed, I set the database recovery mode to Simple, bot
h
on my user DB and tempdb in order to avoid huge log files.
(1) why does my tempdb log file still grow large (10 GB) ?
(2) why can't I specify "unrestricted growth" on additional log files for
tempdb? (every time I specify a location with ample HD space for an
additional tempdb log file in the DB Properties, the server sets Autogrowth
to a restricted growth and my changes don't take effect)Can you elaborate on exactly how you are doing these mass insertions?
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> I'm using SQL Server 2005 Developer Edition and doing mass data insertion.
> In
> order to optimize for speed, I set the database recovery mode to Simple,
> both
> on my user DB and tempdb in order to avoid huge log files.
> (1) why does my tempdb log file still grow large (10 GB) ?
> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets
> Autogrowth
> to a restricted growth and my changes don't take effect)|||> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets Autogrowt
h
> to a restricted growth and my changes don't take effect)
What max size are the files set to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> I'm using SQL Server 2005 Developer Edition and doing mass data insertion.
In
> order to optimize for speed, I set the database recovery mode to Simple, b
oth
> on my user DB and tempdb in order to avoid huge log files.
> (1) why does my tempdb log file still grow large (10 GB) ?
> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets Autogrowt
h
> to a restricted growth and my changes don't take effect)|||The raw data is in text files. I created a SSIS package in Management Studio
using the DTS-like import/export tool that reads the file and fills a table
in tempdb. Every time I read in a new table, the SSIS package drops and
re-creates the existing table in tempdb.
"Andrew J. Kelly" wrote:

> Can you elaborate on exactly how you are doing these mass insertions?
> --
> Andrew J. Kelly SQL MVP
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>
>|||The default for log files was 100MB, I think... But since I could not set it
to "unrestricted growth", I simply changed the max size to 20GB to make sure
I never run out of space.
"Tibor Karaszi" wrote:

> What max size are the files set to?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>|||Reason I asked is that when you set to unlimited, SQL Server will actually s
tore and show the
maximum size for a log file (which is 2TB). This is, of course the same as u
nlimited (as any
database file cannot grow beyond the max specification) but the presentation
might confuse a bit. So
if you set to maxsize and it show 2TB, all is fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...[vbcol=seagreen]
> The default for log files was 100MB, I think... But since I could not set
it
> to "unrestricted growth", I simply changed the max size to 20GB to make su
re
> I never run out of space.
> "Tibor Karaszi" wrote:
>|||Will SQL delete the table in TEMPDB when it get restarted? Would it be
easier just to create a blank database and store data there?
James
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Reason I asked is that when you set to unlimited, SQL Server will actually
> store and show the maximum size for a log file (which is 2TB). This is, of
> course the same as unlimited (as any database file cannot grow beyond the
> max specification) but the presentation might confuse a bit. So if you set
> to maxsize and it show 2TB, all is fine.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
>|||Sorry, but I didn't follow the other threads of this discussion, so I don't
know that table you are
referring to...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James" <hushdontspamme@.hotmail.com> wrote in message
news:%236HH2kQIGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Will SQL delete the table in TEMPDB when it get restarted? Would it be ea
sier just to create a
> blank database and store data there?
> James
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||Perhaps I should add: Tempdb is re-created every time SQL Server starts.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James" <hushdontspamme@.hotmail.com> wrote in message
news:%236HH2kQIGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Will SQL delete the table in TEMPDB when it get restarted? Would it be ea
sier just to create a
> blank database and store data there?
> James
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>