July 7, 2009 at 4:36 am
Hi all,
Is there any way to have information about Initial file size of log files.
sys.database_files, sys.master_files all hold current size, not initial size at the time DB creation.
In more details, if you run dbcc shrinkfile , in output you see MinimumSize, From where this MinimumSize is coming ??
Thanks for you help
Muhammad
July 7, 2009 at 5:46 am
The minimum size is just that due to having data in there. Using Data files as an example, if you have data in that file equating to 10gb, then that file can never be shrunk to smaller than 10gb.
July 7, 2009 at 5:56 am
Hi Mohammad,
Good question, realy a good question. If we right click and open properties it will show us Initial File size but there is no DMV which will tell it. So what did i do is run Profiler and checked the properties of the database and captured below queries.
These queries will let u know the original size of the data/log file.
I will try to tweak these queries so that it can do it for all the databases in oneshot.
Data File Size
==================
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
insert #tmpspc EXEC ('dbcc showfilestats')
DECLARE @is_policy_automation_enabled bit
SET @is_policy_automation_enabled = (SELECT CONVERT(bit, current_value)
FROM msdb.dbo.syspolicy_configuration
WHERE name = 'Enabled')
SELECT
CAST(cast(g.name as varbinary(256)) AS sysname) AS [FileGroup_Name],
s.name AS [Name],
CASE s.type WHEN 2 THEN 0 ELSE (tspc.TotalExtents - tspc.UsedExtents)*convert(float,64) END AS [AvailableSpace],
s.physical_name AS [FileName],
CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],
CAST(CASE when s.growth=0 THEN (CASE WHEN s.type = 2 THEN 0 ELSE 99 END) ELSE s.is_percent_growth END AS int) AS [GrowthType],
s.file_id AS [ID],
CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],
CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],
s.size * CONVERT(float,8) AS [Size],
CAST(CASE s.type WHEN 2 THEN 0 ELSE tspc.UsedExtents*convert(float,64) END AS float) AS [UsedSpace],
CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
s.is_read_only AS [IsReadOnly],
s.is_media_read_only AS [IsReadOnlyMedia],
s.is_sparse AS [IsSparse],
case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like 'Server' + '/Database\[@ID=' + convert(nvarchar(20),dtb.database_id) + '\]' + '/FileGroup\[@ID=' + convert(nvarchar(20),g.data_space_id) + '\]'+ '/File\[@ID=' + convert(nvarchar(20),s.file_id) + '\]%' ESCAPE '\') then 1 else 0 end AS [PolicyHealthState]
FROM
master.sys.databases AS dtb,
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(dtb.name=db_name())
ORDER BY
[FileGroup_Name] ASC,[Name] ASC
drop table #tmpspc
Log File Size
==================
DECLARE @is_policy_automation_enabled bit
SET @is_policy_automation_enabled = (SELECT CONVERT(bit, current_value)
FROM msdb.dbo.syspolicy_configuration
WHERE name = 'Enabled')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],
CAST(CASE when s.growth=0 THEN (CASE WHEN s.type = 2 THEN 0 ELSE 99 END) ELSE s.is_percent_growth END AS int) AS [GrowthType],
s.file_id AS [ID],
CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],
s.size * CONVERT(float,8) AS [Size],
CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) AS [UsedSpace],
CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
s.is_read_only AS [IsReadOnly],
s.is_media_read_only AS [IsReadOnlyMedia],
s.is_sparse AS [IsSparse],
case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like 'Server' + '/Database\[@ID=' + convert(nvarchar(20),dtb.database_id) + '\]'+ '/LogFile\[@ID=' + convert(nvarchar(20),s.file_id) + '\]%' ESCAPE '\') then 1 else 0 end AS [PolicyHealthState]
FROM
master.sys.databases AS dtb,
sys.master_files AS s
WHERE
(s.type = 1 and s.database_id = db_id())and((dtb.name=db_name()))
ORDER BY
[Name] ASC
Regards
GURSEThi
July 7, 2009 at 6:45 am
HI GURSEThi,
Thank you very much for your reply. The interesting thing is that if you right click and go to prpperties-----Files, Even if it tells InitialSize (MB), but in actual it is the current size, as the log file will grow this initial size will change to current size.:w00t: ......... breaking the concept of INITIAL:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply