August 10, 2009 at 9:11 am
can any one send me the script of finding the total ,used and available free space of datafiles of all the databases in sql server( 2000&2005)
replies are valuable
August 10, 2009 at 9:34 am
sp_spaceused will get you the info you need although it will only show you space used of current database.
August 10, 2009 at 11:53 am
SELECT name ,size/128.0 AS Size, CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS InUse, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB, CONVERT( dec(5, 2), 100.0 * ( size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS Float)) / size ) AS [%Free] FROM sys.database_files;
Honor Super Omnia-
Jason Miller
August 10, 2009 at 12:00 pm
August 10, 2009 at 12:15 pm
Robert klimes (8/10/2009)
you can use sys.master_files instead of sys.database_files to get all databases on server.
Wow, nice tip! I wonder why I never noticed it before ...?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 11, 2009 at 1:12 am
appreciated for replies but when i executed the mentioned query showing Invalid object name 'sys.database_files' or . in sql 2000 can u sort it out .
Thanks
August 11, 2009 at 3:29 am
SQL 2000 does not have the previously mentioned catalog views. You posted in SQL 2005 forum and got the answers for SQL 2005.
"Keep Trying"
August 12, 2009 at 11:06 am
Even though this is SQL 2005 we still like to assist. Try this procedure I wrote using the undocumented sp_MSforeachdb procedure. You'll need the table DatabaseGrowthStats for testing. Here is a script for the table and procedure. Good luck.
USE [Reporting]
GO
/****** Object: Table [dbo].[DatabaseGrowthStats] Script Date: 08/12/2009 10:04:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DatabaseGrowthStats](
[Sequence] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](50) NULL,
[DatabaseSizeMB] [decimal](18, 2) NULL,
[UnallocatedSpaceMB] [decimal](18, 2) NULL,
[ReservedSpaceMB] [decimal](18, 2) NULL,
[DataSpaceUsedMB] [decimal](18, 2) NULL,
[IndexSpaceUsed] [decimal](18, 2) NULL,
[UnusedSpaceMB] [decimal](18, 2) NULL,
[UpdateDate] [datetime] NULL CONSTRAINT [DF_DatabaseGrowthStats_UpdateDate] DEFAULT (getdate()),
CONSTRAINT [PK_DatabaseGrowthStats] PRIMARY KEY CLUSTERED
(
[Sequence] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
---------------------------------------------------------------------------------------------
USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[getDatabaseStatistics] Script Date: 08/12/2009 09:59:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 06/29/2009
-- Description: gather and log database statistics
-- =============================================
ALTER PROCEDURE [dbo].[getDatabaseStatistics]
AS
BEGIN TRY
-- verify the current date doesn't exist --
DELETE
FROM
Reporting.dbo.DatabaseGrowthStats
WHERE
CONVERT(CHAR(10),updatedate,101) = CONVERT(CHAR(10),GETDATE(),101);
-- insert statistics --
INSERT INTO Reporting.dbo.DatabaseGrowthStats
(
DatabaseName,
DatabaseSizeMB,
UnallocatedSpaceMB,
ReservedSpaceMB,
DataSpaceUsedMB,
IndexSpaceUsed,
UnusedSpaceMB
)
EXEC sp_MSforeachdb 'use ?;
SET NOCOUNT ON;
DECLARE
@dbsize BIGINT,
@logsize BIGINT,
@reservedpages BIGINT,
@usedpages BIGINT,
@pages BIGINT
SELECT
@dbsize = SUM(CONVERT(BIGINT, CASE WHEN status & 64 = 0 THEN SIZE ELSE 0 END)),
@logsize = SUM(CONVERT(BIGINT, CASE WHEN status & 64 0 THEN SIZE ELSE 0 END))
FROM
dbo.sysfiles
---------------------------------------------------------------------------------------
SELECT
@reservedpages = SUM(a.total_pages), @usedpages = SUM(a.used_pages),
@pages = SUM
(
CASE
WHEN it.internal_type IN (202, 204) THEN 0
WHEN a.TYPE 1 THEN a.used_pages
WHEN p.index_id = @reservedpages THEN CAST(((@dbsize - @reservedpages) * 8192/1048567.) AS DECIMAL(15, 2)) ELSE 0 END) "Unalloc. SPACE(MB)",
CAST((@reservedpages * 8192/1048576.) AS DECIMAL(15, 2)) "Reserved(MB)",
CAST((@pages * 8192/1048576.) AS DECIMAL(15, 2)) "DATA Used(MB)",
CAST(((@usedpages - @pages) * 8192/1048576.) AS DECIMAL(15, 2)) "INDEX Used(MB)",
CAST(((@reservedpages - @usedpages) * 8192/1048576.) AS DECIMAL(15, 2)) "Unused(MB)"
'
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
END CATCH
August 13, 2009 at 7:40 am
Jason Miller's script (using master_files) returns NULLS in the InUse, Avail..,%Free columns for all files that are not current database. ???
Tim White
August 13, 2009 at 7:59 am
I use the given below script to get the details of what you have requested.
set nocount on
DECLARE @sql NVARCHAR(1000),@mindbid smallint,@maxdbid smallint
declare @dbname nvarchar(50),@dbid int
declare @test-2 table(slno int identity,dbid smallint,name varchar(100))
insert into @test-2(dbid,name)
select dbid,name from master..sysdatabases order by dbid asc
if object_id('tempdb..#temp2') is not null
begin
drop table #temp2
end
if object_id('tempdb..#temp3') is not null
begin
drop table #temp3
end
select @mindbid =min(slno) from @test-2
select @maxdbid =max(slno) from @test-2
create table #temp2 (fileid int,filegroup int,totalextents decimal(12,2),AutoGrowthStatus varchar(50),
used_extents decimal(12,2),name nvarchar(100),filename nvarchar(200),dbid int)
while (@mindbid<=@maxdbid)
begin
select @dbname = name,@dbid = dbid from @test-2 where dbid = (select dbid from @test-2 where slno =@mindbid)
if (select databasepropertyex(@dbname,'STATUS')) = 'ONLINE'
begin
if (convert(varchar(30),SERVERPROPERTY('ProductVersion'))) like '2000%'
begin
set @sql ='use '+ @dbname + char(13) + Char(10) +'insert into #temp2
select fileid,groupid,(size /8), case when maxsize = -1 then ''Auto growth enabled, with no restriction''
else ''Auto growth Restricted''
end as AutoGrowthStatus,
(fileproperty(name,''SpaceUsed'')/8),name,filename,dbid ='+ convert(varchar(3),@dbid)+' from
master..sysaltfiles where dbid =db_id('''+@dbname+''') -- and groupid = 0'
end
else
begin
set @sql ='use '+ @dbname + char(13) + Char(10) +'insert into #temp2
select file_id,data_space_id,(size /8), case when max_size = -1 then ''Auto growth enabled, with no restriction''
else ''Auto growth Restricted''
end as AutoGrowthStatus,
(fileproperty(name,''SpaceUsed'')/8),name,physical_name,dbid ='+ convert(varchar(3),@dbid)+' from
sys.master_files where database_id =db_id('''+@dbname+''') -- and groupid = 0'
end
exec (@sql)
end
else
begin
select 'Space details for database '+@dbname +' couldn''t be captured as its state is '+ CONVERT(varchar(30),databasepropertyex(@dbname,'Status'))+'.'
end
set @mindbid = @mindbid +1
end
select substring(db_name(dbid),1,30) DBName,filegroup,fileid,name FileName,filename FileLocation,AutoGrowthStatus,
(convert (decimal(12,2),((totalextents*8*8))/(1024))) TotalSize_In_MB,
(convert(decimal(12,2),((used_extents*8*8))/(1024)))UsedSize_In_MB ,
convert (decimal(12,2),(((totalextents-used_extents)*8*8)/1024)) FreeSpace_In_MB,
convert(decimal(12,2),(used_extents/totalextents)*100) UsedSpaceinPercentage,
convert(decimal(12,2),(((totalextents -used_extents)/totalextents)*100 )) FreeSpaceinPercentage
from #temp2
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply