Re: Transaction log and tempdb

Hello,

On 22 mai, 23:44, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Kurt (nicolas.agrap...@xxxxxxxxx) 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...@xxxxxxxxxxxxx

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.

.

Relevant Pages

  • Re: HDD still full after shrinking transaction log
    ... So that is the first question: why does the log keep growing? ... In the first case, you should run with full recovery, and backup your ... SQL Server will then truncate the transaction log regularly. ...
    (comp.databases.ms-sqlserver)
  • Re: Transaction log and tempdb
    ... tempdb in SQL Server and I hop you could help me ... - Is it possible to reduce the size of the transaction log fil during ... It sounds as if you are the database is in full recovery. ... If I execute that script sql server seems ...
    (comp.databases.ms-sqlserver)
  • Re: Log-size
    ... don't do transaction log backups, the log will expand to finally fill the disk. ... > response to another respondent's suggestion that this user BACKUP LOG ... ... > your recovery model correctly. ...
    (microsoft.public.sqlserver.server)
  • Re: Tranaction log error- can someone tell me if this error was my fault
    ... transaction log to free up space." ... ALTER DATABASE db SET RECOVERY SIMPLE ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Transaction log with huge proportions
    ... read books-online and learn about SQL Backup and Recovery. ... SQL Server in BOL for a complete discussion of all the concepts involved. ... > My SQL Cluster, has a database with 13Gb, but the transaction log is huge. ...
    (microsoft.public.sqlserver.clustering)