|
|
Posted by Kurt on 05/23/07 06:39
Hello,
On 22 mai, 23:44, Erland Sommarskog <esq...@sommarskog.se> 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/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks.
Navigation:
[Reply to this message]
|