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