May 12, 2010 at 11:12 pm
Hello every body,
We are also experiencing the same issue. Only the tempdb log file increasing ~700 MB a day (not at a particular time. It is increasing continuously ~30 MB hourly). We also have MOSS 2007 & Project Server 2007 on same SQL Server 2005 EE x64 instance with SP3.
Is this an issue with MOSS or Project server? Because I did NOT get any queries running against tempdb from sql Profiler:hehe:
Is it a bug?
thanks
May 13, 2010 at 12:53 am
Try this http://qa.sqlservercentral.com/articles/Log+growth/69476/ and let it run every 5 min.
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
May 13, 2010 at 12:49 pm
I'm running the below script to create the procedure but getting the below error:
Msg 207, Level 16, State 1, Procedure SpaceUsed_perfmon, Line 35
Invalid column name 'LogUsedMB'.
Msg 207, Level 16, State 1, Procedure SpaceUsed_perfmon, Line 35
Invalid column name 'LogUsedPercent'.
Msg 207, Level 16, State 1, Procedure SpaceUsed_perfmon, Line 35
Invalid column name 'LogGrowths'.
Where I'm going wrong?
Create procedure dbo.SpaceUsed_perfmon as
Set nocount on
Begin
Declare @instanceName Varchar(25)
Set @instanceName = 'TestLogGrowth'
;
WITH LogData (database_name, measure, value) AS
(
SELECT instance_name,
CASE counter_name
WHEN N'Data File(s) Size (KB)' THEN N'DSize'
WHEN N'Log File(s) Size (KB)' THEN N'LSize'
WHEN N'Log File(s) Used Size (KB)' THEN N'Used'
WHEN N'Log Growths' THEN N'Grow'
END,
cntr_value
FROM sys.dm_os_performance_counters PC
WHERE counter_name IN (N'Data File(s) Size (KB)', N'Log File(s) Size (KB)'
, N'Log File(s) Used Size (KB)', N'Log Growths')
AND instance_name = @InstanceName
),
LogUsage AS
(
SELECT database_name,
DataSizeMB = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'DSize' THEN value ELSE 0 END) / 1024.0),
LogSizeMB = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'LSize' THEN value ELSE 0 END) / 1024.0),
LogUsedMB = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'Used' THEN value ELSE 0 END) / 1024.0),
LogGrowths = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'Grow' THEN value ELSE 0 END))
FROM LogData
GROUP BY database_name
)
Insert into DBFileSizeLog (DBName,DBSizeMB,DataSizeMB,LogSizeMB,LogUsedMB,LogUsedPercent,LogGrowths,CheckDate)
SELECT database_name,DataSizeMB+LogSizeMB,DataSizeMB,LogSizeMB,LogUsedMB
,[LogUsedPercent] = CONVERT(DEC(9,2), ((LogUsedMB * 100.0) / LogSizeMB))
,LogGrowths, convert(varchar(19),GETDATE(),120)
FROM LogUsage
ORDER BY database_name ASC;
End
May 13, 2010 at 1:07 pm
The copy and paste that you used appears to have hidden characters. Try copy and paste to notepad++ or something like that and then paste to mgmt studio.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 13, 2010 at 1:08 pm
Feeg (5/13/2010)
Try this http://qa.sqlservercentral.com/articles/Log+growth/69476/ and let it run every 5 min.
This is what I would recommend. 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 13, 2010 at 3:26 pm
Here is the same query copied from SSMS and getting the same error while running this script.
Create procedure dbo.SpaceUsed_perfmon as
Set nocount on
Begin
Declare @instanceName Varchar(25)
Set @instanceName = 'TestLogGrowth'
;
WITH LogData (database_name, measure, value) AS
(
SELECT instance_name,
CASE counter_name
WHEN N'Data File(s) Size (KB)' THEN N'DSize'
WHEN N'Log File(s) Size (KB)' THEN N'LSize'
WHEN N'Log File(s) Used Size (KB)' THEN N'Used'
WHEN N'Log Growths' THEN N'Grow'
END,
cntr_value
FROM sys.dm_os_performance_counters PC
WHERE counter_name IN (N'Data File(s) Size (KB)', N'Log File(s) Size (KB)'
, N'Log File(s) Used Size (KB)', N'Log Growths')
AND instance_name = @InstanceName
),
LogUsage AS
(
SELECT database_name,
DataSizeMB = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'DSize' THEN value ELSE 0 END) / 1024.0),
LogSizeMB = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'LSize' THEN value ELSE 0 END) / 1024.0),
LogUsedMB = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'Used' THEN value ELSE 0 END) / 1024.0),
LogGrowths = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'Grow' THEN value ELSE 0 END))
FROM LogData
GROUP BY database_name
)
Insert into DBFileSizeLog (DBName,DBSizeMB,DataSizeMB,LogSizeMB,LogUsedMB,LogUsedPercent,LogGrowths,CheckDate)
SELECT database_name,DataSizeMB+LogSizeMB,DataSizeMB,LogSizeMB,LogUsedMB
,[LogUsedPercent] = CONVERT(DEC(9,2), ((LogUsedMB * 100.0) / LogSizeMB))
,LogGrowths, convert(varchar(19),GETDATE(),120)
FROM LogUsage
ORDER BY database_name ASC;
End
May 13, 2010 at 4:09 pm
Before copying it to SSMS, copy it to Notepad++ and look for the hidden characters.
Remove those characters and then copy the query from Notepad++ to SSMS.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 13, 2010 at 11:47 pm
Before copying it to SSMS, copy it to Notepad++ and look for the hidden characters.
Remove those characters and then copy the query from Notepad++ to SSMS.
I did the above and I did not find any hidden characters.
It's the same query and getting the same error
Please advice
May 14, 2010 at 3:07 pm
Hi Jason, Madhu,
Actually its not an issue at all. Initally, I have have allocated the data file size as 10 Gb & ldf file size as 20 GB.
The tempdb log file used space is grown to 14 GB slowly in 1 month and once it reached 14 GB i.e 70 % of of total size then it's automatically issued a check point and now the log file used space is 100 MB.
So it's looks like pretty normal for tempdb and it won't give the space back to OS until used space reaches to 70 % of allocated size for ldf file. Thats what happend for me.
thanks for all help
Thanks
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply