February 3, 2012 at 7:24 am
Hello Gurus:
Is there a command that we can use to reduce the size of the transaction log in the TEMPDB database.
The tempdb database is used by temp tables which are not useful. But it ends up taking space.
February 3, 2012 at 7:31 am
You can use the command DBCC shrinkfile (more details about it can be found in Books On Line), but the question is why? If during normal activity the log got to its current size, then most chances are that after you'll shrink it, the log will get to its current size again during the normal activity. If you know that you've done something that is a one time operation that caused it to get to its size, then shrinking it, can be benefitail.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 3, 2012 at 7:39 am
The tempdb database is used by all other databases very heavily ? Why ? Bacause each time you make a temp table ( Those tables that start with # ) it lands in the tempdb database.
So when we run a stored proc, we get error messages saying the "Transaction Log is full"
So there has to be a way to reduce the size of this log frequently.
February 3, 2012 at 8:13 am
Actually it means that your transaction log is not big enough and that it needs to grow more, but it can't because there is no disk space left (or it is configured to grow up in to big chunk that it can get from the disk). If you'll run the statement DBCC SQLPERF('logspace'), you'll be able to see the log's size and amount of free space. If you'll see that most of its space is used and it doesn't get free, check if there is an open transaction that wasn't closed. You can do that with DBCC OPENTRAN statement. This will show you the oldest transaction running in the database. If you'll see something very old, there is a very good chance that killing this process will free the log space (you can check it out by running DBCC SQLPERF ('logspace') again). If however there is no old transaction, check the code and see what kind of temporary objects you are using. If you are inserting many millions of rows into temporary objects, this can also explain the problem that you
have.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 3, 2012 at 8:15 am
If you run this
EXEC sp_helpdb 'tempdb'
What values are returned for maxsize and growth of templog ?
February 3, 2012 at 8:18 am
yes we do insert many millions of rows.
So what is the command that can be used to reduce the size ?
The next question if we issue this command while some other transaction is writing to the
log, will it stop the transaction ? You know what i mean ?
February 3, 2012 at 8:24 am
Please see attachment. That will show the numbers
February 3, 2012 at 8:36 am
mw112009 (2/3/2012)
So when we run a stored proc, we get error messages saying the "Transaction Log is full"So there has to be a way to reduce the size of this log frequently.
Let's take a couple of steps back here.
The log is full, meaning that the entire log file has been used and there is no more free space.
Why would you want to make it smaller, able to hold less log records and prone to filling up faster?
Surely if the log is full, you need to make it larger, not smaller?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2012 at 8:42 am
Sorry I meant
How can I empty the transaction log ?
February 3, 2012 at 8:54 am
Now that is a massively different question.
Start with this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Also, you probably want to work on optimising the queries that use lots of TempDB space, look into removing unnecessary temp tables, adding indexes or optimising queries so that they don't have unnecessarily expensive sorts or hashs that could spill to TempDB
http://technet.microsoft.com/en-us/library/cc966545.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply