October 22, 2008 at 1:57 pm
hi
I want to exe sp_spaceused for every 5 hrs and store the result in table.
I created sql job but can u tell me how to store the results in table?
October 22, 2008 at 2:06 pm
sp_spacesused executes two transactions.
You won't be able to just pump it into a table with
INSERT table
exec sp_spaceused
You'll need to script out master..sp_spaceused and figure it out from there.
There are lots of existing scripts out there too, maybe if you told us what you were looking for someone may have an existing script they can post.
~BOT
Craig Outcalt
October 22, 2008 at 2:16 pm
I want to find database growth by every 4 hrs
October 22, 2008 at 2:44 pm
here's one I whipped up that works on 2000 and 2005.
set nocount on
if object_id('tempdb..#DBSpace') is not null
drop table #DBSpace
go
create table #DBSpace
(
ServerName sysname
,InstanceName sysname
,dbname sysname
,DB_OR_LOG_NAME sysname
,AllocatedMb integer
,UsedMb integer
,AutoGrowMaxSize integer
)
exec master.dbo.sp_MSForEachDb @command1 =
'
use [?]
insert into #dbspace
select
CAST( SERVERPROPERTY (''MachineName'') as nvarchar(128) ) AS MachineName
, COALESCE ( CAST( SERVERPROPERTY (''InstanceName'') as nvarchar(128) ) , ''Default'') AS InstanceName
, db_name()
,name
,CAST(size /128 as decimal(12,2)) as ''Alloccated Size (MB)''
,CAST(fileproperty(name,''SpaceUsed'')/ 128.0 as decimal(12,2) ) as ''Space Used (MB)''
-- Increase in Percentage else Growth -- Increase in Pages
,case maxsize when -1 then
case groupid when 0 then 2097152
else 2147483647 end else maxsize end as ''Max Size''
from sysfiles
'
Select ServerName
,InstanceName
,dbname as DatabaseName
,DB_OR_LOG_NAME as filename
--,AllocatedMb
,UsedMb
,(AutoGrowMaxSize /128) - UsedMB as 'FreeMB'
, ((usedmb)*100 / (AutoGrowMaxSize /128)) as 'percent Used Mb'
, (usedMB * 5 / 4) as 'ideal size (80% full)'
,AutoGrowMaxSize /128 as 'MaxSizeMB'
from #dbspace
where allocatedMB > 0
order by db_or_log_name
Craig Outcalt
October 22, 2008 at 3:18 pm
If your system is 2005 you can also use this:
select
num = row_number() over (order by sum(f.size)*8/1024 desc),
database_name = left(d.name,30),
size_MB = sum(f.size)*8/1024
from
sys.databases d join sys.master_files f on
d.database_id = f.database_id
group by
d.name
order by
num
--compute sum(sum(f.size)*8/1024)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply