SQL tempdb DB Log File Space
-
Monday, December 31, 2012 8:34 PM
Hi
This is not for the Operations Manager DB rather other productions DBs. I am noticing that the numerous SQL servers are reporting this issue. For one instance the DBA has assured me that the DB has plenty of HD space, set to auto-grow without restriction - how should this be addressed?
Any pointers would be greatly appreciated.
All Replies
-
Tuesday, January 01, 2013 8:29 AMModerator
Hi,
Would you please let us know more details about the issue? Do you mean you received a lot of alerts about the “SQL tempdb DB Log File Space”? If so, please refer to the following post:
DB Log File Space for tempdb.log????
SQL MP 6.3.173.0 noise!!!
Thanks.Nicholas Li
TechNet Community Support
- Marked As Answer by Nicholas LiMicrosoft Contingent Staff, Moderator Tuesday, January 08, 2013 2:48 AM
- Unmarked As Answer by kramtam Wednesday, January 16, 2013 4:42 PM
-
Tuesday, January 01, 2013 9:14 AMModerator
Hi
Just change the thresholds via overrides. The database has a data file (or files) and log file(s) and just because there is database free space, it could be that the data file has lots of free space but the log file very little.
USE <<Database Name>>
select a.FILEID,
[FILE_SIZE_MB]=convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB]=convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB]=convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
[GROWTH_MB]=convert(decimal(12,2),round(a.growth/128.000,2)),
NAME=left(a.NAME,15),
FILENAME=left(a.FILENAME,60)
from dbo.sysfiles aIs tempdb also set to autoshrink? Or are there maintenance jobs that are shrinking tempdb?
Cheers
Graham
Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/- Marked As Answer by Nicholas LiMicrosoft Contingent Staff, Moderator Tuesday, January 08, 2013 2:48 AM
- Unmarked As Answer by kramtam Wednesday, January 16, 2013 4:43 PM
-
Wednesday, January 16, 2013 4:56 PM
I was able to meet with the DBA regarding.
SCOM 2012
SQL Server 2008 6.3.173.0 MP
The drive has 28 GB free of 64.9 GB total
templog currently 102,912 KB -> 100.5 MB
Enable Autogrowth is checked
File growth in Megabytes is choosen, set to 100
Unrestricted File Growth is choosen
It would appear that the alert is measuring against the File Growth of 100 MB (being that it is currenlty at 100.5 with % at 0) and the Maximum File Size Unrestricted File Growth being bypassed by monitor.
-
Saturday, January 19, 2013 7:38 AMModerator
templog currently 102,912 KB -> 100.5 MB
How much of this is free space and how much is used?
Can you run this fro tempdb and let us know the answer:
USE <<Database Name>>
select a.FILEID,
[FILE_SIZE_MB]=convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB]=convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB]=convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
[GROWTH_MB]=convert(decimal(12,2),round(a.growth/128.000,2)),
NAME=left(a.NAME,15),
FILENAME=left(a.FILENAME,60)
from dbo.sysfiles aRegards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/ -
Monday, January 21, 2013 6:56 PM
Hi Graham,
Not sure I understand your question but I will try to answer. Actual file size of templog is 102,912KB (or 100.5MB). Space available on disk is 27.7 GB.
In SQL the database initial size is 1 MB
enable autogrowth
File growth In Megabytes 100
Maximum File Size = Unrestricted File Growth
-
Monday, January 21, 2013 7:00 PMModerator
Hi
Can you get your DBAs to run the above script and post back the results.
Thanks
Graham
Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/ -
Monday, January 21, 2013 7:26 PM
Hi, update to 6.3.173.1 version.I was able to meet with the DBA regarding.
SCOM 2012
SQL Server 2008 6.3.173.0 MP
The drive has 28 GB free of 64.9 GB total
templog currently 102,912 KB -> 100.5 MB
Enable Autogrowth is checked
File growth in Megabytes is choosen, set to 100
Unrestricted File Growth is choosen
It would appear that the alert is measuring against the File Growth of 100 MB (being that it is currenlty at 100.5 with % at 0) and the Maximum File Size Unrestricted File Growth being bypassed by monitor.
-
Friday, January 25, 2013 2:16 PM
DBA followed up with me:
-
Friday, January 25, 2013 2:23 PMModeratorFrom that the TempDB log file has 0.22 MB of free space .... that could be why it is alerting ...
Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/ -
Friday, January 25, 2013 4:26 PMWhy would it alert is the file has autogrowth enabled and Unrestricted File Growth is chosen?
- Proposed As Answer by Alexis Yakovlev Friday, January 25, 2013 4:34 PM
- Unproposed As Answer by Alexis Yakovlev Friday, January 25, 2013 4:34 PM
-
Friday, January 25, 2013 4:35 PMModerator
You might want to double check that ... why hasn't it grown when only 0.22 MB free?
Do you also have autoshrink enabled?
The settings for autogrow are not very sensible ... 0.8 MB for a data file is very small.
Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/ -
Friday, January 25, 2013 4:36 PM
Why would it alert is the file has autogrowth enabled and Unrestricted File Growth is chosen?
If you use MP version 6.3.173.0, update to 6.3.173.1, there was a bug with free space monitor
- Edited by Alexis Yakovlev Friday, January 25, 2013 4:36 PM
- Marked As Answer by kramtam Wednesday, April 24, 2013 6:42 PM
.png)
