Transaction log and tempdb

Hello

I have questions about how works transaction log et the database
tempdb in SQL Server and I hop you could help me
- Is it possible to reduce the size of the transaction log fil during
an execution ? Indeed, I have a script inserting a very large quantity
of data (many Go) and during that process my transaction log file use
all the space avaible on my hard drive. Is there any way to solve that
problem ?
- Is it possible to limit the size of the database tempdb ? I have an
another script inserting data using a select joinning 2 tables of
about 20 Go with group by. If I execute that script sql server seems
to freeze and I must kill the process. What can I do ? Is the only
solution is that I must make more avaible space on my hard drive ?

Thanks in advance for your answers.


K.

Re: Transaction log and tempdb

Kurt (nicolas.agrapart@gmail.com) writes:
> I have questions about how works transaction log et the database
> tempdb in SQL Server and I hop you could help me
> - Is it possible to reduce the size of the transaction log fil during
> an execution ? Indeed, I have a script inserting a very large quantity
> of data (many Go) and during that process my transaction log file use
> all the space avaible on my hard drive. Is there any way to solve that
> problem ?

Maybe. It sounds as if you are the database is in full recovery. If you
would switch to simple recovery, the transaction log would truncate
after each committed transaction (more or less). But! If this is a
production database for which there is a requirement of being able
to a point-in-time recovery in case of a disaster, then this is not
an option. Then again, if this is your development database, and you are
content with restoring from the most recent backup, you are probably
better of with simple recovery any way.

When loading data, there are several techniques of optimization, but
without further knowledge of your situation it's difficult to say
what may be better for you.

> - Is it possible to limit the size of the database tempdb ? I have an
> another script inserting data using a select joinning 2 tables of
> about 20 Go with group by. If I execute that script sql server seems
> to freeze and I must kill the process. What can I do ? Is the only
> solution is that I must make more avaible space on my hard drive ?

Yes, it's possible to define a max limit fot the size of tempdb, but
I'm not really sure that I see the point with this.

If you have a wild query, you should rather see what you can do to
improve it, maybe add an index. Or run in it first on a smaller data
set, to verify that the query is logically correct.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Transaction log and tempdb

Hello,

On 22 mai, 23:44, Erland Sommarskog wrote:
> Kurt (nicolas.agrap...@gmail.com) writes:
> > I have questions about how works transaction log et the database
> > tempdb in SQL Server and I hop you could help me
> > - Is it possible to reduce the size of the transaction log fil during
> > an execution ? Indeed, I have a script inserting a very large quantity
> > of data (many Go) and during that process my transaction log file use
> > all the space avaible on my hard drive. Is there any way to solve that
> > problem ?
>
> Maybe. It sounds as if you are the database is in full recovery. If you
> would switch to simple recovery, the transaction log would truncate
> after each committed transaction (more or less). But! If this is a
> production database for which there is a requirement of being able
> to a point-in-time recovery in case of a disaster, then this is not
> an option. Then again, if this is your development database, and you are
> content with restoring from the most recent backup, you are probably
> better of with simple recovery any way.
>
> When loading data, there are several techniques of optimization, but
> without further knowledge of your situation it's difficult to say
> what may be better for you.

You write that there is several technique of optimization, I know a
few that I always apply. But is there any place where I could find
those ?

> > - Is it possible to limit the size of the database tempdb ? I have an
> > another script inserting data using a select joinning 2 tables of
> > about 20 Go with group by. If I execute that script sql server seems
> > to freeze and I must kill the process. What can I do ? Is the only
> > solution is that I must make more avaible space on my hard drive ?
>
> Yes, it's possible to define a max limit fot the size of tempdb, but
> I'm not really sure that I see the point with this.
>
> If you have a wild query, you should rather see what you can do to
> improve it, maybe add an index. Or run in it first on a smaller data
> set, to verify that the query is logically correct.
The query is correct, tested with a small amount of datas.
The index are set. I don't really know what I can do more?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
Thanks.

Re: Transaction log and tempdb

Kurt (nicolas.agrapart@gmail.com) writes:
> You write that there is several technique of optimization, I know a
> few that I always apply. But is there any place where I could find
> those ?

A good book on SQL programming beyond the introductory stuff should
probably cover this. No, I don't have any direct suggestions for titles.

If you give more specifics about your problem, we might able to give some
suggestions.

>> If you have a wild query, you should rather see what you can do to
>> improve it, maybe add an index. Or run in it first on a smaller data
>> set, to verify that the query is logically correct.
>
> The query is correct, tested with a small amount of datas.
> The index are set. I don't really know what I can do more?

Make sure that statistics are up to date, and that tables aren't fragmented
are start. Also investigate the query plan, and verify that this is the best
you can get (which is unlikely given the performance). Query tuning can
sometimes be a difficult matter. You could post the query here together
with table and index definitions. Which version of SQL Server are you
using?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx