As Max mentioned the alert probably fired just prior to the log growing/needing to grow. SCOM collects transaction log free space % although I am not sure at what threshold the alert will fire.
here is a quick example to show you what state tempdb is probably in when you get these alerts but no log file growth.
first create a database, set recovery to full, and back it up
create database tlogspace
on(name=tlogspace_dat,
filename='c:\temp\tlogspace.mdf',
size=4MB)
log on (name=tlogspace_log,
filename='c:\temp\tlogspace.ldf',
size=1MB);
go
alter database tlogspace set recovery full;
go
backup database tlogspace to disk='nul';
go
now switch to that database, create a table, and run DBCC sqlperf(logspace) to check the size oand free space in your log file.
use tlogspace
go
create table data(id int identity(1,1), col varchar(8000))
dbcc sqlperf(logspace)
On my system I have logfile size of 0.9921875 and log space used (%) of 48.4245. Now insert some data into the table and run DBCC sqlperf(logspace) again. On my system 45 rows inserted gave desired results(number of rows inserted may need to be adjusted).
insert into data(col)
select replicate('a',8000)
go 45 --may need to adjust number
dbcc sqlperf(logspace)
This time, the DBCC sqlperf output should show that log size is the same but log space used is just under 100%. In this case, SCOM would probably fire an alert that log space is low. There is no more activity going on to cause the log file to grow and (in this example) no tlog backup to free up used space. tempdb is in simple recovery, so your active transaction probably used up most of the available space and did not release it but there was not enough activity in tempdb to trigger log file growth thus causing the alert fire.
cleanup database when finished
use master
drop database tlogspace
Best Answer
I'd ask the person who told you that, to at least hear why they think it will decrease performance.
One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your transaction log needs to grow, SQL Server has to do work, which you could notice regarding performance.
A primary reason is detailed in the first link below:
What makes more sense is setting a dedicated drive for this file, and setting the LOG file to the size of the drive. Why shrink to 0 when you know it's only going to grow again, leaving you in the same spot with it’s size, cause fragmentation, etc?
tempdb
uses the simple recovery model and clears the log when ever a checkpoint is issued. This automatically happens when the log usages reaches 70% and in the case oftempdb
, it doesn't force dirtytempdb
pages to disk. See Paul Randall's post on this. VLF's are made inactive if possible during the checkpoint as well. When you shrink the log, the VLFs are removed.