DB File Overview

  • Comments posted to this topic are about the item DB File Overview

  • Great script. Thanks a million.

  • i've change it a bit , so the curent size on the db to be more acurate ... se tempdb...

    thanks 🙂

    /****************************************************************************************************

    **DB Size&Vlf

    ****************************************************************************************************/

    SET NOCOUNT ON

    declare @dbcnt int,

    @DBName varchar(256),

    @FileName varchar(256),

    @FileType varchar(256),

    @FreeSpaceInMB decimal(38,2),

    @CurrentSizeMB decimal(38,2),

    @NumOfVLF int,

    @Query varchar(max),

    @vlfcnt int

    declare @Tmp table

    (DBName varchar(256),

    [FileName] varchar(256),

    FileLoc varchar(256),

    FileType varchar(256),

    SizeInMB decimal(38,2),

    FreeSpaceInMB decimal(38,2),

    GrowthRate varchar(256),

    MaxSize varchar(256),

    NumOfVLF int default 0,

    CurrentState varchar(256))

    declare @tmpdb table

    (DBName varchar(256),

    [FileName] varchar(256),

    FileType varchar(256))

    declare @vlf table

    (FileID int,

    FileSize bigint,

    StartOffset bigint,

    FSeqNo int,

    [Status] int,

    Parity int,

    CreateLSN numeric(25,0))

    declare @tmpspace table

    (CurrentSizeMB decimal(38,2), FreeSpaceInMB decimal(38,2))

    --**Get Main Data

    insert into @Tmp

    (DBName,[FileName],FileLoc,FileType,SizeInMB,FreeSpaceInMB,GrowthRate,MaxSize)

    select '[' + db_name(m.database_id) + ']' as DBName,

    m.name as [FileName],m.physical_name,m.type_desc,

    cast(m.size/128.0 as decimal(38,2)) as SizeInMB,

    m.size/128.0 - cast(FILEPROPERTY(m.name,'Spaceused')as int)/128.0 as FreeSpaceInMB,

    case m.is_percent_growth

    when 1 then cast(m.growth as varchar) + '%'

    else cast(cast(m.growth/128.0 as decimal(38,2))as varchar) + ' MB'

    end as GrowthRate,

    case

    when m.max_size = -1 or m.max_size > 2000000 then 'Unrestricted'

    else cast(cast(m.max_size/128.0 as decimal(38,2)) as varchar(256))

    end as max_size

    from sys.master_files m

    inner join sys.databases db on

    m.database_id = db.database_id

    where db.state_desc = 'ONLINE'

    --**Gather VLFs & Free Space

    insert into @tmpdb

    (DBName,[FileName],FileType)

    select distinct DBName,[FileName],FileType from @Tmp

    select @dbcnt = count(DBName)from @tmpdb

    while @dbcnt > 0

    BEGIN

    select top 1 @DBName = DBName,@FileName = [FileName],@FileType = FileType from @tmpdb

    set @Query = 'use ' + @DBName + char(10)

    set @Query = @Query + 'select size/128.0,size/128.0 - cast(FILEPROPERTY(name,' + char(39) + 'Spaceused' + char(39) + ')'

    set @Query = @Query + 'as int)/128.0 from ' + @DBName + '.sys.database_files' + char(10)

    set @Query = @Query + 'where name = ' + char(39) + @FileName + char(39)

    insert into @tmpspace exec(@query)

    select @CurrentSizeMB = CurrentSizeMB,@FreeSpaceInMB = FreeSpaceInMBfrom @tmpspace

    update t set t.FreeSpaceInMB = @FreeSpaceInMB, t.SizeInMB=@CurrentSizeMB from @Tmp t

    where DBName = @DBName and [FileName] = @FileName

    IF @FileType = 'LOG'

    BEGIN

    set @query = 'use ' + @DBName + char(10)

    set @query = @query + 'DBCC loginfo'

    insert into @vlf

    exec(@query)

    select @vlfcnt = count(*)

    from @vlf

    update t

    set t.NumOfVLF = @vlfcnt

    from @Tmp t

    where DBName = @DBName and

    [FileName] = @FileName

    delete from @vlf

    END

    delete t from @tmpdb twhere DBName = @DBName and [FileName] = @FileName

    delete from @tmpspace

    select @dbcnt = count(DBName)from @tmpdb

    END

    select replace(replace(DBName,'[',''),']','')as DBName,[FileName],FileType,SizeInMB as SizeMB,

    cast(isnull(FreeSpaceInMB,0) as varchar(256)) as FreeSpMB,

    cast(100-round(100*FreeSpaceInMB/SizeInMB,2) as decimal(6,2)) as '%full' ,NumOfVLF as VlfNr, GrowthRate,MaxSize,FileLoc

    from @Tmp order by FileType desc ,SizeInMB desc

    --===============================================================

  • Even better. Thanks.

  • i get an error with this script:

    Column name or number of supplied values does not match table definition.

  • I do not get the error. Could you re-post the exact syntax you are trying to run? Also, could you send version of the db server?

  • I use the exact same script as you have posted. I tried to find the exact line that cause the error. It's this one:

    delete t from @tmpdb t where DBName = @DBName and [FileName] = @FileName

    if i replace the last condition with an empty string, it work (it will loop forever but the error is gone)

    delete t from @tmpdb t where DBName = @DBName and [FileName] = ''

    it seems that the @FileName variable is the problem but i can't find why

    I'm using SQL Server 2012

  • I doubt that is the problem. Are you referring to my original script or the re-write posted in forum? The re-write posted in the forum is different than my script. If you notice that the table variable @tmpspace has different columns than the one in my original script. I'm willing to guess that would be the cause of the problem. Run the original script below and let me know if this works.

    SET NOCOUNT ON

    /****************************************************************************************************

    **declare table & variables

    ****************************************************************************************************/

    declare @dbcnt int,

    @DBName varchar(256),

    @FileName varchar(256),

    @FileType varchar(256),

    @FreeSpaceInMB decimal(38,2),

    @NumOfVLF int,

    @Query varchar(max),

    @vlfcnt int

    declare @Tmp table

    (DBName varchar(256),

    [FileName] varchar(256),

    FileLoc varchar(256),

    FileType varchar(256),

    SizeInMB decimal(38,2),

    FreeSpaceInMB decimal(38,2),

    GrowthRate varchar(256),

    MaxSize varchar(256),

    NumOfVLF int default 0,

    CurrentState varchar(256))

    declare @tmpdb table

    (DBName varchar(256),

    [FileName] varchar(256),

    FileType varchar(256))

    declare @vlf table

    (FileID int,

    FileSize bigint,

    StartOffset bigint,

    FSeqNo int,

    [Status] int,

    Parity int,

    CreateLSN numeric(25,0))

    declare @tmpspace table

    (FreeSpaceInMB decimal(38,2))

    /****************************************************************************************************

    **Get Main Data

    ****************************************************************************************************/

    insert into @Tmp

    (DBName,[FileName],FileLoc,FileType,SizeInMB,FreeSpaceInMB,GrowthRate,MaxSize)

    select '[' + db_name(m.database_id) + ']' as DBName,

    m.name as [FileName],m.physical_name,m.type_desc,

    cast(m.size/128.0 as decimal(38,2)) as SizeInMB,

    m.size/128.0 - cast(FILEPROPERTY(m.name,'Spaceused')as int)/128.0 as FreeSpaceInMB,

    case m.is_percent_growth

    when 1 then cast(m.growth as varchar) + '%'

    else cast(cast(m.growth/128.0 as decimal(38,2))as varchar) + ' MB'

    end as GrowthRate,

    case

    when m.max_size = -1 then 'Unrestricted'

    else cast(cast(m.max_size/128.0 as decimal(38,2)) as varchar(256))

    end as max_size

    from sys.master_files m

    inner join sys.databases db on

    m.database_id = db.database_id

    where db.state_desc = 'ONLINE'

    /****************************************************************************************************

    **Gather VLFs & Free Space

    ****************************************************************************************************/

    insert into @tmpdb

    (DBName,[FileName],FileType)

    select distinct DBName,[FileName],FileType

    from @Tmp

    select @dbcnt = count(DBName)

    from @tmpdb

    while @dbcnt > 0

    BEGIN

    select top 1 @DBName = DBName,@FileName = [FileName],@FileType = FileType

    from @tmpdb

    set @Query = 'use ' + @DBName + char(10)

    set @Query = @Query + 'select size/128.0 - cast(FILEPROPERTY(name,' + char(39) + 'Spaceused' + char(39) + ')'

    set @Query = @Query + 'as int)/128.0 from ' + @DBName + '.sys.database_files' + char(10)

    set @Query = @Query + 'where name = ' + char(39) + @FileName + char(39)

    insert into @tmpspace

    exec(@query)

    select @FreeSpaceInMB = FreeSpaceInMB

    from @tmpspace

    update t

    set t.FreeSpaceInMB = @FreeSpaceInMB

    from @Tmp t

    where DBName = @DBName and

    [FileName] = @FileName

    IF @FileType = 'LOG'

    BEGIN

    set @query = 'use ' + @DBName + char(10)

    set @query = @query + 'DBCC loginfo'

    insert into @vlf

    exec(@query)

    select @vlfcnt = count(*)

    from @vlf

    update t

    set t.NumOfVLF = @vlfcnt

    from @Tmp t

    where DBName = @DBName and

    [FileName] = @FileName

    delete from @vlf

    END

    delete t

    from @tmpdb t

    where DBName = @DBName and

    [FileName] = @FileName

    delete

    from @tmpspace

    select @dbcnt = count(DBName)

    from @tmpdb

    END

    /****************************************************************************************************

    **Final Results

    ****************************************************************************************************/

    select replace(replace(DBName,'[',''),']','')as DBName,[FileName],FileLoc,FileType,cast(SizeInMB as varchar(256))as SizeInMB,

    cast(isnull(FreeSpaceInMB,0) as varchar(256)) as FreeSpaceInMB,GrowthRate,MaxSize,NumOfVLF

    from @Tmp

  • the one i used is the one on this forum. The last one you've just posted have the error too. The same line. And if i remove the @FileName condition, the script run.

    very weird!

  • i believe you hve your data file on the c: drive in 'program files' or other dir with spaces in it... or the name itself... you must enclose the filname in the brackets tooo as for dbnamw...

  • ok... i have configured the debugger and found something new. The real line that is causing the error is this one on the second pass in the 'while loop':

    insert into @vlf

    exec(@query)

    in the @query variable, i have this:

    use [Corporatif]

    DBCC loginfo

    and the result of this query is this:

    RecoveryUnitIdFileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN

    0 22539528192500640

    0 2253952262144510640

    0 22539525160965201280

    0 22785287700485321280

    0 2262144104857654212846000000031000436

    0 22621441310720006447000000001600495

    0 226214415728644906448000000001600187

    0 2262144183500800048000000044300011

  • double post.. sorry!

  • ok... i now see the problem. The DBCC loginfo is returning one addition column that the script doesnt know about. It's the column RecoveryUnitId. This is why i have the error.

    The correction is just to add the RecoveryUnitId field to the @vlf table:

    declare @vlf table

    (RecoveryUnitId int,

    FileID int,

    FileSize bigint,

    StartOffset bigint,

    FSeqNo int,

    [Status] int,

    Parity int,

    CreateLSN numeric(25,0))

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply