| Author |
Topic |
|
uberman
Posting Yak Master
159 Posts |
 Posted - 2006-05-04 : 10:07:04
|
| I was looking at one of my servers today and noticed that the .ldf for the tempdb was looking a bit large compared to the .mdf, in the sense that the mdf is c.200 Mb and the .ldf is c.17.5GBI have google'd my little heart out and searched this site cant really find any information regarding this apart from truncating the log/database and exhortations not to do so...tempdb is in simple recovery mode and is not included in any backup schedule...should I be performing regular maintenance of tempdb and if so what (all the other databases are backed up on a daily basis and user dbs also have their transaction logs backup regularly as well...) |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-04 : 10:23:34
|
| i don't think so, this just tells you that there are a lot of transactions being handled and waiting to be committedinstead you may want to 'investigate' what's bloating the tempdbHTH--------------------keeping it simple... |
 |
|
|
uberman
Posting Yak Master
159 Posts |
Posted - 2006-05-04 : 10:29:21
|
| cheersfyi , DBCC OPENTRAN is report no open tansactions in tempdb |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-04 : 13:43:31
|
| After you next restart SQL Server you might want to check that tempdb.LDF has dropped back to a sensible size, if not you may want to increase its startup-size, and also check the extension-size, so that it doesn't extend in 1MB chunks (or whatever the default is!) if you actually have a working-need for 17GB (or even something 10% of that)Kristen |
|
|
|
kyadao
Starting Member
1 Post |
Posted - 2006-08-15 : 15:38:29
|
quote:
Originally posted by Kristen
After you next restart SQL Server you might want to check that tempdb.LDF has dropped back to a sensible size, if not you may want to increase its startup-size, and also check the extension-size, so that it doesn't extend in 1MB chunks (or whatever the default is!) if you actually have a working-need for 17GB (or even something 10% of that)Kristen
Hi Kristen,What do you mean by 'increase its startup-size'? where is the startup size?I am having the same issue with one of our tempdbs being 6gb. The tempdb data file allocated size is 3 mb and is set to grow 10% as needed. The tran log allocated size is 6634 and is also set to grow 10% as needed. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-08-16 : 06:56:12
|
| tran log is set to 6634 MB?The Tempdb is newly created everytime SQL Server starts, and as such, it will create the file per the specified sizes. So, if you've specified the default allocation of the tempdb log file to 6.6 GB (6634 MB), then your tempdb is always going to be a 6 GB database. I must say, those values sound bizarre - 3MB data and 6 GB log? That looks like it could (should!) be reviewed - you don't want tempdb to grow if you can help it (i.e. you want it large enough by default, since grows will cause poor performance). Having said that, I am NOT advocating removal of Autogrowth - I am advocating a review of the initial allocation size, maybe to something like 500MB data, 500 MB Log, and see if those are sufficient.The same should be true for you, uberman. When was the SQL server last restarted, andd what is the log file default size?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-08-17 : 07:29:42
|
quote:
Originally posted by uberman
I was looking at one of my servers today and noticed that the .ldf for the tempdb was looking a bit large compared to the .mdf, in the sense that the mdf is c.200 Mb and the .ldf is c.17.5GBI have google'd my little heart out and searched this site cant really find any information regarding this apart from truncating the log/database and exhortations not to do so...tempdb is in simple recovery mode and is not included in any backup schedule...should I be performing regular maintenance of tempdb and if so what (all the other databases are backed up on a daily basis and user dbs also have their transaction logs backup regularly as well...)
You may need to look at your user database? Is there unusual database size? |
|
|
|
jennifer.slack
Starting Member
1 Post |
Posted - 2006-09-14 : 10:14:57
|
| I am having a very similar issue. My .mdf is 277 MB and my .ldf is 40511 MB!! When I try to analyze the issue, my queries hang. Also when I click on properties of tempdb that hangs. I have requested some downtime in order to restart the db, but they have not gotten back to me yet. I do have some errors in my sql server logs that state "Autogrow of file 'templog' in database 'tempdb' took 98829 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file." Is there anything I can do that won't effect users and doesn't require downtime? Help - Thanks in AdvanceJen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-14 : 10:30:32
|
| How long does it take to stop/start SQL server? I would suspect less than 2 minutesHow much time is everyone losing a day? I would suspect more than 2 minutes per person.You do NOT want to set a smaller filegrowth, but you may very well want to set a fixed-size file growth, rather than a percentage - if the percentage is 10% that is 4,000 MB each time it extends which, in my experience, if the server is reasonably busy will take a month-of-Sundays (98,829 of your milliseconds! although I'll bet its longer than that for 10% of 40GB).You should also set a sensible initial size. If you server's TEMPDB routinely grows to 40,000 MB then setting that as the starting size is not such a bad idea (but it will increase the startup time a lot).After the next reboot I would suggest you monitor the TEMPDB size and see what the real growth is, and if it spikes suddenly what is causing it to do that. Changing it to be 40,000 MB big by default is one thing, fixing the thing that it making it that big would be better!Kristen |
|
|
|
|
tempdb .ldf = 17.5GB, .mdf = 200MB, do I care?