May 18, 2004 at 3:23 am
Our sqlserver from our website is running very slow. From time to time we have timeouts in sqlstatements, but I can't find locks on the databases.
The transaction log space of the tempdb is growing and growing up.
Can anyone help me, why the transaction log of the temdb can grow up and will not be reduceded from the sqlserver automatically? Usually we restart the sqlserver, and then the effect disapear. But in then moment,
Thank you
May 18, 2004 at 6:33 am
What version of SQL Server are you running and what is the patch level?
Do you have your transaction log on seperate disk from the database files?
Are you running backups and what kind?
Make sure you are running log backups, depending on the size and transaction volume, you might want to log backups multiple times a day.
Also check the amount of memory on the server, how much is sql server using? On the server properties, do you have min/max memory selected?
You should not have to start and stop sql server or reboot your server. We don't have an large database, the larges is only 20gig, but we have many 24x7 databases that get rebooted 3-5 times a year (usually do to maintence and patches).
It is hard to give a good answer when there could be many things contributing to your problem. Might not be one issue.
Joseph
May 18, 2004 at 6:41 am
Joseph's answers are good for the transaction logs for user databases, but the TEMPDB is designed to grow and not be backed up. Stopping and restarting the services rebuilds the TEMPDB (as you found out) and solves the issue. That's great unless you can't be stopping and starting services.
In that case, you can use DBCC SHRINKFILE. This can even be used on any other database. Find out the name of the actual data or log file (In Enterprise Manager, right click on the database, select Properties and look at the Data and Log tabs. The names will be in the leftmost column). For TEMPDB, the names are usually TEMPDEV and TEMPLOG. You can run the DBCC SHRINKFILE against both of those. Refer to the Books OnLine for more information on DBCC SHRINKFILE and it's syntax.
-SQLBill
May 18, 2004 at 6:48 am
My suggestion for the tempDB would be go back to the application. I know the tempdb is not backed up. If there are too many users and/or the application is using #temp tables, the tempdb would be used. We had an application that was using many #temp tables and little memory (512meg) and by "dropping" them in the application after the processing was done VS waiting for the session to end helped greatly. The same scenerio could occur if the min and max memory are set very small.
Joseph
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply