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)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 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@.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)|||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...
> > 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)
>
>|||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:
> > (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@.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)
>|||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...
> 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:
>> > (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@.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)
>>|||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...
>> 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:
>> > (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@.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)
>>
>|||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 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...
>> 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:
>> > (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@.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)
>>
>|||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 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...
>> 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:
>> > (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@.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)
>>
>sql
Showing posts with label speed. Show all posts
Showing posts with label speed. Show all posts
Monday, March 26, 2012
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...
>
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...
>
log files
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)
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 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@.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)
|||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 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...[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 sure
> 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 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...
>
|||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 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...
>
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)
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 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@.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)
|||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 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...[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 sure
> 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 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...
>
|||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 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...
>
Monday, March 19, 2012
Log File Help!
I have a database that has a data file of size 50GB (500 million records). It's a lot of data. For speed, we have set up a 9 disc SCSI RAID 5 (4 drives striped, mirrored, and parity). Total space of 130GB. As I said, the data file consumes 50GB.
I needed to run a pretty vanila SQL INSERT/UPDATE on the data. 7 hours later, the query had failed due to a "log file full" error. The log file was at 80+GB and shortly dropped to 1MB by itself.
I'm running SQL Server 2000 SP3. Recovery model is simple and auto shrink is enabled. What could possibly consume 80GB of disc? That is way larger than the amount of data that I have!
What is the best solution? Get a 200GB IDE disc to house the log file?
I need a solution fast! This project is way over schedule; these 7 hour statement run times are killing me.
Thank you in advance!you will be better off breaking your operation into separate insert and then update. but i'd recommend to replace insert with bulk insert, and update with bcp...out that would result in the contents of the file with desired values, then truncate the table, and then bulk insert clean "updated" data. from what you describe it sounds like you are updating the majority of 500 mil records.|||SCSI RAID 5 (4 drives striped, mirrored, and parity)
I don't know how you set this up, but this "is not" RAID 5. What you should probably have done from a performance perspective was have your logs on RAID 1 or 10 and the data files on RAID 5.
In addition, it does sound like you need to break your operation up into smaller steps. If you want to post the script, we can probably give you some pretty fast pointers on how to make it run faster and not use as much log.|||Thanks for the feedback!
Last night, I ran out and bought a 250GB IDE disc, put the log file on that and reran. It finished in 5.5 hours and consumed 150GB of log (which shortly after shrunk down to 1MB). That's a damn lot of log space! My entire data file is only 50GB!
Here are is the exact SQL I ran:
INSERT INTO Domains (Domain)
SELECT DISTINCT Domain FROM Stages WHERE Domain NOT IN (SELECT Domain FROM Domains)
UPDATE Stages SET DomainID = (SELECT Domains.[ID] FROM Domains WHERE Domains.Domain = Stages.Domain)
Now for the more complex TSQL... Hopefully the 250GB will hold and everything will be complete in 48 hours...|||I don't suppose you've wrapped a BEGIN TRANSACTION around this whole thing have you? Just for fun, put a CHECKPOINT between each one of your statement. This shouldn't help keep the log small, but it will force it to write to disk after each statement. You will want to begin and commit each statement seperately also.|||Now, I didn't use transactions at all. I ran the exact SQL that I posted in a Query Analyzer window.
That piece is done. However, if you can suggest an optimization for the dedup process, I would be really grateful. I'm posting that under a separate thread: "Optimize Dedup TSQL?"
I needed to run a pretty vanila SQL INSERT/UPDATE on the data. 7 hours later, the query had failed due to a "log file full" error. The log file was at 80+GB and shortly dropped to 1MB by itself.
I'm running SQL Server 2000 SP3. Recovery model is simple and auto shrink is enabled. What could possibly consume 80GB of disc? That is way larger than the amount of data that I have!
What is the best solution? Get a 200GB IDE disc to house the log file?
I need a solution fast! This project is way over schedule; these 7 hour statement run times are killing me.
Thank you in advance!you will be better off breaking your operation into separate insert and then update. but i'd recommend to replace insert with bulk insert, and update with bcp...out that would result in the contents of the file with desired values, then truncate the table, and then bulk insert clean "updated" data. from what you describe it sounds like you are updating the majority of 500 mil records.|||SCSI RAID 5 (4 drives striped, mirrored, and parity)
I don't know how you set this up, but this "is not" RAID 5. What you should probably have done from a performance perspective was have your logs on RAID 1 or 10 and the data files on RAID 5.
In addition, it does sound like you need to break your operation up into smaller steps. If you want to post the script, we can probably give you some pretty fast pointers on how to make it run faster and not use as much log.|||Thanks for the feedback!
Last night, I ran out and bought a 250GB IDE disc, put the log file on that and reran. It finished in 5.5 hours and consumed 150GB of log (which shortly after shrunk down to 1MB). That's a damn lot of log space! My entire data file is only 50GB!
Here are is the exact SQL I ran:
INSERT INTO Domains (Domain)
SELECT DISTINCT Domain FROM Stages WHERE Domain NOT IN (SELECT Domain FROM Domains)
UPDATE Stages SET DomainID = (SELECT Domains.[ID] FROM Domains WHERE Domains.Domain = Stages.Domain)
Now for the more complex TSQL... Hopefully the 250GB will hold and everything will be complete in 48 hours...|||I don't suppose you've wrapped a BEGIN TRANSACTION around this whole thing have you? Just for fun, put a CHECKPOINT between each one of your statement. This shouldn't help keep the log small, but it will force it to write to disk after each statement. You will want to begin and commit each statement seperately also.|||Now, I didn't use transactions at all. I ran the exact SQL that I posted in a Query Analyzer window.
That piece is done. However, if you can suggest an optimization for the dedup process, I would be really grateful. I'm posting that under a separate thread: "Optimize Dedup TSQL?"
Subscribe to:
Posts (Atom)