July 27, 2015 at 7:00 am
Hi experts,
my prod server(only default instance) is configured TempDB 1024 MB data and Log 200MB. when I run 'sqlperf logspace' it shows most of time around 45% 'log space used'. There nothing going on the instance when I ran 'whoisactive' and select * from sys.sysprocesses where dbid = 2!!!
So my questions are is this normal to see log space around 45%, how to find what what CAUSED the tempdb log space to grow 45%? Is there something to do about it?
Thanks
July 27, 2015 at 7:51 am
Tac11 (7/27/2015)
Hi experts,my prod server(only default instance) is configured TempDB 1024 MB data and Log 200MB. when I run 'sqlperf logspace' it shows most of time around 45% 'log space used'. There nothing going on the instance when I ran 'whoisactive' and select * from sys.sysprocesses where dbid = 2!!!
So my questions are is this normal to see log space around 45%, how to find what what CAUSED the tempdb log space to grow 45%? Is there something to do about it?
Thanks
TempDb is dropped and re-created every time your SQL Server Service is started, so it might be worth checking it again immediately following the next opportunity to recycle the service comes along ... perhaps in a maintenance window. I don't know what the default settings for logging are on tempdb, but if the recovery mode is full, it's possible for production database work that uses temp tables, such as ETL jobs, to create quite a bit of work going on in tempdb. Without a lot more details on what kind of workload your server has, along with configuration data, OLTP and ETL volumes, I/O statistics, and such (much of which may be impractical for you to provide), it might be rather difficult to determine. TempDb can see usage just from a CTE that requires more memory than is available at the time. It might be an indication of a one-time heavy duty temp table that was created with RBAR code (a Jeff Moden term referring to problematic performance code that does things Row By Agonizing Row), or an indication that you are starting to have RAM constraints.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 7:51 am
45% of 200MB = 90MB, is it really worth worrying about?
Most processes that use tempdb won't be running in the context of tempdb so querying sysprocesses where dbid = 2 wouldn't show them anyway.
July 27, 2015 at 9:41 am
Thank you guys for your reply. One thing to mention here my Prod database has Log shipping configured for every hour but when I run sys.databases, it shows 'Log_backup' on 'log_reuse_wait_desc'!!! Also I have 'Report server' Database on same instance which is 'Full' recovery model and also shows 'log_reuse_wait_desc.
Questions:
1. Since log backup happening every hour and nothing happening in the database, why it shows 'Log_backup' on 'log_reuse_wait_desc'?
2. Should It is OK to change 'ReportServer' db to Simple? what's the best practice
And regarding my question earlier only 'database mail' running once a while (nothing else) when I run 'whoisactive', but Temp DB keep going up to almost 60% !!!!
July 27, 2015 at 11:41 am
Some things to look for:
1.) Regularly scheduled SSRS reports where the query creates temp tables of some size.
2.) Power users with query ability that might be fond of large temp tables.
3.) Check the ReportServerDb and see what it's up to. SQL Server Enterprise Edition can do Data Driven Subscriptions, and larger number of those might be able to influence tempdb. Be sure to take subscription frequency into account.
You didn't mention whether or not you checked to see if you're running with constrained RAM or not. You might want to gather some PerfMon stats to look at both RAM usage and I/O stats.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 12:30 pm
With respect to the tempdb log, checkpoints are different for tempdb, and only automatically run when the log hits 70% used (see here: http://www.sqlskills.com/blogs/paul/what-does-checkpoint-do-for-tempdb/).
So, if your tempdb sits around idle most of the time, then it will just occasionally write to the log and that won't be cleared until the checkpoint occurs. Tempdb uses Simple, and that can't be changed (See the Database Options section here: https://msdn.microsoft.com/en-us/library/ms190768.aspx).
As for why LOG_BACKUP might show for the log reuse wait description after backing up the log, see http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/.
That seems to match your situation pretty nicely. It's not anything to worry about.
Cheers!
July 27, 2015 at 12:55 pm
Update:
It 'log space used' went down to below 10% after it reached almost 70%. didn't find any open or log running transactions!!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply