|
Home > Archive > MS SQL Server > January 2006 > log files
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Ken Abe 2006-01-23, 8:23 pm |
| 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)
| |
| Andrew J. Kelly 2006-01-24, 3:23 am |
| 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@microso
ft.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)
| |
| Tibor Karaszi 2006-01-24, 3:23 am |
| > (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)
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@microso
ft.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)
| |
| Ken Abe 2006-01-24, 9:23 am |
| 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@microso
ft.com...
>
>
>
| |
|
|
| Tibor Karaszi 2006-01-24, 11:23 am |
| 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@microso
ft.com...[color=darkred]
> 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:
>
| |
| James 2006-01-24, 11:23 am |
| 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@hotmai
l.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@microso
ft.com...
>
| |
| Tibor Karaszi 2006-01-24, 1:23 pm |
| 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@hotma
il.com> wrote in message
news:%236HH2kQIGHA.1192@TK2MSFTNGP11.phx.gbl...
> 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@hotmai
l.nomail.com> wrote in message
> news:%239%232RJQIGHA
.2628@TK2MSFTNGP15.phx.gbl...
>
>
| |
| Tibor Karaszi 2006-01-24, 1:23 pm |
| 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@hotma
il.com> wrote in message
news:%236HH2kQIGHA.1192@TK2MSFTNGP11.phx.gbl...
> 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@hotmai
l.nomail.com> wrote in message
> news:%239%232RJQIGHA
.2628@TK2MSFTNGP15.phx.gbl...
>
>
|
|
|
|
|