can any one tell me to find out the list of databases that have “.ldf” size greater than “.mdf”.

Recommended Answers

FIRST RUN THE COMPLETE SQL THEN CHECK THE OUTPUT. THEN GO FOR MODIFICATION.

Create Table ##helpfile
(dbname varchar(100),
name varchar(100),
FileId int,
Filename varchar(100),
Filegroup varchar(50),
Size varchar(20),
maxsize varchar(20),
growth varchar(20),
usage varchar(20))

Create Table ##filestats
(Fileid int,
FileGroup int,
TotalSpace int,
UsedSpace int,
FreeSpace int, …
Jump to Post

All 3 Replies

FIRST RUN THE COMPLETE SQL THEN CHECK THE OUTPUT. THEN GO FOR MODIFICATION.

Create Table ##helpfile
(dbname varchar(100),
name varchar(100),
FileId int,
Filename varchar(100),
Filegroup varchar(50),
Size varchar(20),
maxsize varchar(20),
growth varchar(20),
usage varchar(20))

Create Table ##filestats
(Fileid int,
FileGroup int,
TotalSpace int,
UsedSpace int,
FreeSpace int,
Name varchar(100),
FileName varchar(100))

Exec sp_MSForeachDB

@command1 = 'Use ?; DBCC UPDATEUSAGE(0)',
@command2 = 'Use ?;Insert ##helpfile (name, fileid, filename, filegroup,
size, maxsize, growth, usage) Exec sp_helpfile; update ##helpfile set
dbname = ''?'' where dbname is null',
@command3 = 'Use ?;Insert ##filestats (Fileid, FileGroup, TotalSpace,
UsedSpace, Name, FileName) exec (''DBCC SHOWFILESTATS WITH TABLERESULTS'')'

Update ##filestats set totalspace = totalspace*64/1024, usedspace =
usedspace*64/1024
Update ##filestats set freespace = totalspace - usedspace

--cube
Select dbname, UPPER(Left(filename,1)) as [Drive Letter],
count(Left(filename,1)) AS [Count of DB Files],
sum (Cast(replace(size,' KB', '')as int)) As [Total OS File Size],
Usage from ##helpfile group by dbname, usage, UPPER(Left(filename,1))
with cube
order by dbname, [Drive Letter] , Usage desc

--filestats
Select * from ##filestats

-- add in the usedextents to the helpfile info
select a.dbname,
a.filename,
Cast(replace(a.size,' KB', '')as int)/1024 as DatafilesizeMB,
b.usedspace as UsedSpaceMB,
b.freespace as FreeSpaceMB
from ##helpfile a, ##filestats b where a.filename = b.filename

Drop table ##helpfile
Drop table ##filestats
Declare @dbfiles table ( 
  DBNAME    VARCHAR(50),
  Fileid    int, 
  Name   VARCHAR(50), 
  Size_MB int,
Filename varchar(max)) 

INSERT INTO @dbfiles
exec sp_msforeachdb 'SELECT ''?'' AS DBNAME,fileid, [Name], Size * 8 / 1024 As Size_MB,Filename FROM ?.sys.sysfiles'

select *into #mytablevar1 from @dbfiles where filename like '%mdf' 
select *into #mytablevar2 from @dbfiles where filename like '%ldf' 

select a.dbname,a.size_mb as Data_SizeMB,b.size_mb as Log_SizeMB,a.filename as Filename_Data,b.filename as Filename_log
 into #sp_logfiles from #mytablevar1 as a inner join #mytablevar2 as b on a.dbname=b.dbname and a.size_mb<b.size_mb

select *from #sp_logfiles

--By
--Ashok

Try: Exec sp_MSForeachDB @command1="use [?]; EXEC sp_spaceused"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.