August 3, 2009 at 6:43 am
i want to make the report of all the database size in sql 2000 and 2005 , can any one send me the script to find all the database size in sql 2000 &2005 servers .
thanks
August 3, 2009 at 7:49 am
What do you mean by database size? The size on disk? The data size? The data size + log used size?
We are happy to help, but we also expect you to do a little work here. This is help, not us working for you. There are plenty of scripts on this site in the Scripts section dealing with sizes. Likely you can use one and you'd need to modify it to meet your needs.
August 5, 2009 at 1:33 am
Built in procedures may give you what you want sp_helpdb can be used with or without DBNAME
exec sp_helpdb DBNAME
or
exec sp_databases
If you are looking at reports in SMS have a look at SQL Server 2005 Performance Dashboard Reports
August 5, 2009 at 7:49 am
Try this. Works good on both 2000 and 2005.
set nocount on
declare @name sysname
declare @SQL nvarchar(600)
/* Use temporary table to sum up database size w/o using group by */
create table #databases (
DATABASE_NAME sysname NOT NULL,
size int NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
where has_dbaccess(name) = 1 and name not in ('master','model','msdb','Northwind','pubs','tempdb')-- Only look at databases to which we have access
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL = 'insert into #databases
select N'''+ @name + ''', sum(size) from '
+ QuoteName(@name) + '.dbo.sysfiles'
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
deallocate c1
select
[DATABASE_NAME],
[DATABASE_SIZE in MB] = size*8/1024 /* Convert from 8192 byte pages to K */
from #databases
--order by DATABASE_SIZE
order by 2 desc
SQL DBA.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply