Database Size - Data and Log :: Used/Free
-
I'm currently writing a report on the databases I currently have on our internal SQL Server 2000 box b/c they will soon be deployed to 2 other sites in 2 different cities. In the report, I am suppose to include the size of each database we have.
My question is, when I right-click on the database via EM, towards the top of the screen under the Database section, I see a set Size amount. Also, under the Space Allocated section, I see 2 amounts, one for Data and one for the Log, and these 2 having Free and Used amounts. So in my report, should I include the overall Size amount, or only use the Used Data amount, or include the Data and Log Used amounts or report both the Used and Free amounts for both Data and Log which is the Size amount given towards the top?
Thanks in advance for any advice.
-
Depends on what you want. I've tended to include the used and free amounts and a change from the previous day so I can see if things are growing abnormally.
what's the report being used for?
-
To get the most accurate information (IMO), run the stored procedure sp_updateusage then run sp_spaceused.
sp_spaceused will return the amount of space used by data, unallocated, free, etc.
Refer to the BOL for syntax and more information.
-SQLBill
-
To automate the process use this script:
First put the script into a DTS Package ExecuteSQL Task, create the tables using the scripts at the end of the post. Create linked server connections to the servers you want to report on. Report off the data in the tables. It's fairly accurate.
------SCRIPT------
set xact_abort on
select [dbname]=db_name(), * into #sysfiles from sysfiles where 1=2
select [dbname]=db_name(), * into #sysindexes from sysindexes where 1=2
select ServerName,DatabaseName, UsedSpace,FreeSpace,DataDevice, LogDevice,
DatabasePath, RecordDate
into #stats from [ServerName].[DatabaseName].dbo.DatabaseStats where 1=2
/*
create table #LogStats
(
DatabaseName varchar(50),
LogDevice float,
LogPercentUsed float,
Status bit,
ServerName varchar(30) default @@Servername,
RecordDate datetime default getdate()
)
*/
exec sp_MSforeachdb'
use [?] dbcc updateusage (0) with no_infomsgs
insert #sysfiles select ''?'', * from sysfiles
insert #sysindexes select ''?'', * from sysindexes
declare @servername varchar(30)
declare @pages int
declare @datapage dec(15,2)
declare @datapageMB dec(15,2)
declare @LogpageMB dec(15,2)
declare @logpage dec(15,2)
declare @ReservedPage decimal(15,2)
declare @bytesperpage dec(15,2)
declare @pagesperMB dec(15,2)
DECLARE @DataPlusLogMB decimal(15,2)
DECLARE @Unallocated decimal(15,2)
Declare @DatabasePath Varchar(10)
--get bytes per page
SELECT @bytesperpage = low
FROM master.dbo.spt_values
WHERE number = 1
AND type = ''E''
SET @pagesperMB = 1048576 / @bytesperpage
SELECT @servername = CONVERT(varchar(30), SERVERPROPERTY(''servername''))
--get data file size total
select @datapage = sum(convert(dec(15),size))
from #sysfiles
where (status & 64 = 0)
and dbname like ''?''
--get log file size total
select @logpage = sum(convert(dec(15),size))
from #sysfiles
where (status & 64 <> 0)
and dbname like ''?''
--get reserved
SELECT @ReservedPage = SUM(convert(dec(15),reserved))
FROM #sysindexes
WHERE indid IN (0, 1, 255)
and dbname like ''?''
--get database drive letter
Select @DatabasePath = substring(filename,1,1)
From #sysfiles
where (status & 64 <> 0)
and dbname like ''?''
SET @DataPlusLogMB = (@datapage + @logpage) / @pagesperMB
SET @Unallocated =(@datapage - @ReservedPage) / @pagesperMB
SET @DataPageMB = @datapage / @pagesperMB
SET @LogPageMB = @logpage / @pagesperMB
Insert Into #stats(ServerName,DatabaseName,
UsedSpace,FreeSpace,DataDevice, LogDevice, DatabasePath, RecordDate)
Values(@servername, ''?'',
@DataPlusLogMB,@Unallocated,@DataPageMB,@LogPageMB, @DatabasePath, getdate())
'
declare @servername varchar(30)
SELECT @servername = CONVERT(varchar(30), SERVERPROPERTY('servername'))
select @ServerName
If not Exists (select * from [ServerName].[DatabaseName].dbo.DatabaseStats
where Servername like @Servername
and datediff(day, recorddate, getdate())=0)
Insert Into [ServerName].[DatabaseName].dbo.DatabaseStats(ServerName,DatabaseName,
UsedSpace,FreeSpace,DataDevice, LogDevice, DatabasePath, RecordDate)
select * from #stats
/*
select * from [ServerName].[DatabaseName].dbo.DatabaseStats
where RecordDate
between convert(datetime, '01/22/2004', 102)
and convert(datetime, '01/23/2004', 102)
*/
--delete from #LogStats
--go
Delete From [ServerName].[DatabaseName].dbo.LogStats
create table #LogStats
(
DatabaseName varchar(50),
LogDevice float,
LogPercentUsed float,
Status bit)
insert into #LogStats
exec master.dbo.uspRunPerfLog
-- select * from #LogStats
insert into [ServerName].[DatabaseName].dbo.LogStats (DatabaseName, LogDevice,
LogPercentUsed, Status)
Select * from #LogStats
-- select * from [ServerName].[DatabaseName].dbo.LogStats
Update [ServerName].[DatabaseName].dbo.LogStats
Set Servername = @Servername
-- select * from [ServerName].[DatabaseName].dbo.LogStats
Update [ServerName].[DatabaseName].dbo.DatabaseStats
set LogPercentUsed = l.LogpercentUsed
from [ServerName].[DatabaseName].dbo.DatabaseStats d inner join
[ServerName].[DatabaseName].dbo.LogStats l
on d.ServerName = l.ServerName
and year(d.RecordDate) = year(l.RecordDate)
and month(d.RecordDate) = month(l.RecordDate)
and day(d.RecordDate) = day(l.RecordDate)
and d.DatabaseName = l.DatabaseName
/* select * from [ServerName].[DatabaseName].dbo.DatabaseStats
where RecordDate
between convert(datetime, '01/22/2004', 102)
and convert(datetime, '01/23/2004', 102)
AND ServerName like 'IDBS_7'
*/
drop table #sysfiles
drop table #sysindexes
drop table #stats
drop table #LogStats
------Tables--------
CREATE TABLE [DatabaseStats] (
[StatsID] [int] IDENTITY (1, 1) NOT NULL ,
[ServerName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatabaseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UsedSpace] [decimal](18, 2) NULL ,
[FreeSpace] [decimal](18, 2) NULL ,
[DataDevice] [decimal](18, 2) NULL ,
[LogDevice] [decimal](18, 2) NULL ,
[LogPercentUsed] [decimal](18, 0) NULL ,
[DatabasePath] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordDate] [datetime] NULL ,
CONSTRAINT [PK_DatabaseStats] PRIMARY KEY CLUSTERED
(
[StatsID]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
CREATE TABLE [LogStats] (
[DatabaseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LogDevice] [float] NULL ,
[LogPercentUsed] [float] NULL ,
[Status] [bit] NULL ,
[ServerName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordDate] [datetime] NULL CONSTRAINT [DF__LogStats__Record__7FEAFD3E] DEFAULT (getdate()),
[LogStatsID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_LogStats] PRIMARY KEY CLUSTERED
(
[LogStatsID]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply