karthik.catchme (4/9/2013)
Hi,Please let me know how to get the initial size of the database file using T-SQL.
sys.master_files, sys.database_files, sysfiles, sysaltfiles --> gives only the current size and not the initial size.
2½ year old thread, but I couldn't find the answer anywhere. This is what I came up with:
declare @Page0Dump table ([ParentObject] [nvarchar](4000), [Object] [nvarchar](4000), [Field] [nvarchar](4000), [VALUE] [nvarchar](4000));
insert into @Page0Dump execute sp_executesql N'dbcc page (N''msdb'', 1, 0, 3) with tableresults;';
select pd.[VALUE] * 8 [Initial Size (MB)] from @Page0Dump pd where pd.[Field] = N'MinSize';
That returns the initial size of the "msdb" database data file. Change the first two parameters ("msdb" and "1" above) to the database name and a file_id of a file in that database, respectively. The file_ids for a database can be found with:
select db_name(mf.[database_id]) [DatabaseName], mf.[file_id], mf.[name]
from [sys].[master_files] mf where mf.[database_id] = db_id(N'msdb') order by mf.[name] asc;
Or use variables with those values:
declare @FS [int],
@databaseName [sysname],
@fileName [sysname],
@fileId [int],
@initialSize [int];
declare @Page0Dump table ([ParentObject] [nvarchar](4000), [Object] [nvarchar](4000), [Field] [nvarchar](4000), [VALUE] [nvarchar](4000));
declare @FileInitialSizes table ([DatabaseName] [sysname], [FileId] [int], [FileName] [sysname], [InitialSizeMB] [int]);
declare mfs cursor for
select db_name(mf.[database_id]), mf.[file_id], mf.[name]
from [sys].[master_files] mf
order by mf.[name] asc;
open mfs;
while 1 = 1 begin
fetch next from mfs into @databaseName, @fileId, @fileName;
set @FS = @@fetch_status;
if @@fetch_status != 0 break;
delete from @Page0Dump;
insert into @Page0Dump execute sp_executesql N'dbcc page (@databaseName, @fileId, 0, 3) with tableresults;', N'@databaseName [sysname], @fileId [int]', @databaseName = @databaseName, @fileId = @fileId;
select @initialSize = pd.[VALUE] * 8 from @Page0Dump pd where pd.[Field] = N'MinSize';
insert into @FileInitialSizes values(@databaseName, @fileId, @fileName, @initialSize);
end;
close mfs;
deallocate mfs;
select * from @FileInitialSizes fis order by fis.[DatabaseName] asc, fis.[FileId] asc;