|
|
Posted by Erland Sommarskog on 05/22/07 21:44
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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|