— exec SpatiuUtilizatFisiereBazaDate_prc ‘NumeBaza’

create PROCEDURE SpatiuUtilizatFisiereBazaDate_prc
@dbname varchar(50)

AS

if(@dbname not in(select name from master..sysdatabases))
print ‘Baza de date ”’+@dbname+”’ nu exista pe acest server! ”’+@@servername+””

else
begin
SET NOCOUNT ON

create table #DBUsage
(
FileID varchar(5),
[FileGroup] varchar(5),
TotalExtents int,
UsedExtents int,
[FileName] varchar(50),
PhysicalFileName varchar(250)
)

create table #LogUsage
(
DBName varchar(50),
LogSizeMB numeric(7,2),
[LogUsage%] numeric(5,3),
Status bit
)

declare @cmd1 nvarchar(500)
set @cmd1=’use ‘+@dbname+’; dbcc showfilestats with no_infomsgs’

declare @cmd2 nvarchar(500)
set @cmd2=’dbcc sqlperf(logspace) with no_infomsgs’

insert into #DBUsage
exec sp_executesql @cmd1

insert into #LogUsage
exec sp_executesql @cmd2

declare @a int, @b int
select @a=sum(TotalExtents) from #DBUsage
select @b=sum(UsedExtents) from #DBUsage
insert into #DBUsage(TotalExtents,UsedExtents,FileID)
values(@a,@b,’TOTAL’)

select
isnull(FileID,’ ‘) as FileID,
isnull(FileGroup,’ ‘) as FileGroup,
isnull(FileName,’ ‘) as FileName,
isnull(PhysicalFileName,”) as PhysicalFileName,
cast(TotalExtents*64.0/1024 as numeric(7,3)) as FileSizeMB,
cast(UsedExtents*64.0/1024 as numeric(7,3)) as UsageMB,
cast(UsedExtents*100.0/TotalExtents as numeric(5,3)) as [Usage%]
from #DBUsage

select
DBName,
LogSizeMB,
cast(LogSizeMB*[LogUsage%]/100.0 as numeric(7,3)) as LogUsageMB,
[LogUsage%]
from #LogUsage
where DBName=@dbname

drop table #DBUsage
drop table #LogUsage

end