locked
how do find all database slog size and mdf file size ? RRS feed

  • Question

  • hi experts,


    could you share query to find all databases log file size and mdf file (includes ndf files ) and total db size ? in MB and GB

    I have a task to kae the dbs size  around 300 dbs

    ========               ============     =============        = ===        =====

    DB_Name    Log_file_size           mdf_file_size         Total_db_size           MB               GB

    =========              ===========               ============       ============     

    Thanks,

    Vijay


    • Edited by vijay_1234 Monday, January 6, 2014 5:48 PM
    Monday, January 6, 2014 5:46 PM

Answers

  • Use this ViJay

    set nocount on
    Declare @Counter int
    Declare @Sql nvarchar(1000)
    Declare @DB varchar(100)
    Declare @Status varchar(25)
    Declare @CaptureDate datetime
    Set @Status = ''
    Set @Counter = 1
    Set @CaptureDate = getdate()
    Create Table #Size
    	(
    	SizeId int identity,
    	Name varchar(100),
    	Size int,
    	FileName varchar(1000),
    	FileSizeMB numeric(14,4),
    	UsedSpaceMB numeric(14,4),
    	UnusedSpaceMB numeric(14,4)
    	)
    Create Table #DB
    	(
    	Dbid int identity,
    	Name varchar(100)
    	)
    Create Table #Status
    	(status sql_Variant)
    Insert	Into #DB
    Select	Name
    From	Sys.Databases
    While @Counter <=(Select Max(dbid) From #Db)
    	Begin
    		Set @DB =
    			(
    			Select	Name
    			From	#Db
    			Where	@Counter = DbId
    			)
    			
    			Set @Sql = 'SELECT DATABASEPROPERTYEX('''+@DB+''', ''Status'')'
    			Insert Into #Status
    			Exec (@sql)
    		
    			Set @Status = (Select convert(varchar(25),status) From #Status)
    			If (@Status)= 'ONLINE'
    				Begin
    					Set @Sql = 
    						'Use ['+@DB+']
    						Insert Into #Size
    						Select '''+@DB+''',size, FileName ,
    						convert(numeric(10,2),round(size/128.,2)),
    						convert(numeric(10,2),round(fileproperty( name,''SpaceUsed'')/128.,2)),
    						convert(numeric(10,2),round((size-fileproperty( name,''SpaceUsed''))/128.,2))
    						From sysfiles'
    					Exec (@Sql)
    				End
    			Else
    				Begin
    					Set @SQL = 
    						'Insert Into #Size (Name, FileName)
    						select '''+@DB+''','+''''+@Status+''''
    					Exec(@SQL)
    				End
    			Delete From #Status
    	Set @Counter = @Counter +1
    	Continue
    End
    Select	Name, Size, FileName, FileSizeMB, UsedSpaceMB, UnUsedSpaceMB,right(rtrim(filename),3) as type,  @CaptureDate as Capturedate
    From	#Size
    drop table #db
    drop table #status
    drop table #size
    set nocount off


    Andre Porter

    • Marked as answer by vijay_1234 Tuesday, January 7, 2014 5:14 AM
    Monday, January 6, 2014 6:16 PM

All replies

  • Monday, January 6, 2014 5:51 PM
  • it is not executing... pls could you check
    Monday, January 6, 2014 6:12 PM
  • Use this ViJay

    set nocount on
    Declare @Counter int
    Declare @Sql nvarchar(1000)
    Declare @DB varchar(100)
    Declare @Status varchar(25)
    Declare @CaptureDate datetime
    Set @Status = ''
    Set @Counter = 1
    Set @CaptureDate = getdate()
    Create Table #Size
    	(
    	SizeId int identity,
    	Name varchar(100),
    	Size int,
    	FileName varchar(1000),
    	FileSizeMB numeric(14,4),
    	UsedSpaceMB numeric(14,4),
    	UnusedSpaceMB numeric(14,4)
    	)
    Create Table #DB
    	(
    	Dbid int identity,
    	Name varchar(100)
    	)
    Create Table #Status
    	(status sql_Variant)
    Insert	Into #DB
    Select	Name
    From	Sys.Databases
    While @Counter <=(Select Max(dbid) From #Db)
    	Begin
    		Set @DB =
    			(
    			Select	Name
    			From	#Db
    			Where	@Counter = DbId
    			)
    			
    			Set @Sql = 'SELECT DATABASEPROPERTYEX('''+@DB+''', ''Status'')'
    			Insert Into #Status
    			Exec (@sql)
    		
    			Set @Status = (Select convert(varchar(25),status) From #Status)
    			If (@Status)= 'ONLINE'
    				Begin
    					Set @Sql = 
    						'Use ['+@DB+']
    						Insert Into #Size
    						Select '''+@DB+''',size, FileName ,
    						convert(numeric(10,2),round(size/128.,2)),
    						convert(numeric(10,2),round(fileproperty( name,''SpaceUsed'')/128.,2)),
    						convert(numeric(10,2),round((size-fileproperty( name,''SpaceUsed''))/128.,2))
    						From sysfiles'
    					Exec (@Sql)
    				End
    			Else
    				Begin
    					Set @SQL = 
    						'Insert Into #Size (Name, FileName)
    						select '''+@DB+''','+''''+@Status+''''
    					Exec(@SQL)
    				End
    			Delete From #Status
    	Set @Counter = @Counter +1
    	Continue
    End
    Select	Name, Size, FileName, FileSizeMB, UsedSpaceMB, UnUsedSpaceMB,right(rtrim(filename),3) as type,  @CaptureDate as Capturedate
    From	#Size
    drop table #db
    drop table #status
    drop table #size
    set nocount off


    Andre Porter

    • Marked as answer by vijay_1234 Tuesday, January 7, 2014 5:14 AM
    Monday, January 6, 2014 6:16 PM
  • Hi Vijay,

    please run the below given script to get the result.

    SELECT

    DB_NAME(dbid) 'Database Name',

    name 'File Name',

    size 'Size IN KB',

    (size*8)/(1024) 'Size IN MB',

    (size*8)/(1024*1024) 'Size IN GB',

    filename

    FROM SYS.sysaltfiles

    order by DB_NAME(dbid)

    Thanks,

    Karthikeyan Jothi

    Monday, January 6, 2014 6:34 PM