• 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;