March 21, 2013 at 3:20 am
Comments posted to this topic are about the item DB File Overview
March 21, 2013 at 5:30 am
Great script. Thanks a million.
March 21, 2013 at 7:49 am
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
--===============================================================
March 21, 2013 at 8:58 am
Even better. Thanks.
May 30, 2013 at 8:14 am
i get an error with this script:
Column name or number of supplied values does not match table definition.
May 30, 2013 at 8:21 am
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?
May 30, 2013 at 11:33 am
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
May 30, 2013 at 12:17 pm
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
May 30, 2013 at 12:34 pm
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!
May 30, 2013 at 1:01 pm
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...
May 30, 2013 at 1:22 pm
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
May 30, 2013 at 1:24 pm
double post.. sorry!
May 30, 2013 at 1:29 pm
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