469,344 Members | 6,443 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

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.

May 22 '07 #1
3 6718
Kurt (ni**************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 22 '07 #2
Hello,

On 22 mai, 23:44, Erland Sommarskog <esq...@sommarskog.sewrote:
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/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks.

May 23 '07 #3
Kurt (ni**************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 23 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Tom | last post: by
8 posts views Thread by arijitchatterjee123 | last post: by
2 posts views Thread by New MSSQL DBA | last post: by
reply views Thread by Kurt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.