August 18, 2010 at 9:47 am
Please please some one help me find out the following columns on database level-
Database name,database size, logfile size,allocated space (without log), allocated space (with log),
Free space in MB, Free space in GB, percent used.
If you can provide the script or the site that has this kind of information to pull the data I would be thankfull. Please help me!
August 18, 2010 at 10:04 am
Get Server Database File Information
August 18, 2010 at 11:27 am
Querying sys.database_files will also give you some of that info.
_________________________________
seth delconte
http://sqlkeys.com
August 18, 2010 at 1:58 pm
Thank you so much for the reply. I need to print allocated space (w log) and allocated space (w/o log) If you have some scripts or the site please provide me as soon as possible. Thanks so much in advance!
August 19, 2010 at 2:20 am
Allocated as in used space in files?
sp_spaceused for data. DBCC SQLPERF(LogSpace) for the log.
Why is this urgent?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 6:41 am
This should do it for you, I take no credit for it, it is mainly someone elses code (whose I cannot recall) that has been slightly amended.
The DBPcnt column is percent of allocated space is unused.
The space allocated and used for the database data files and log files are denoted by DBType -- should really be renamed to FileType.. but w-t-h.
-- Gathers the data and log space info for all databases on the system
-- NOTE: This version is for SQL 2005 as it uses sys.sysdatabases
-- instead of master.dbo.sysdatabases (SQL 2000)
set nocount on;
declare @err int, @daydiff smallint, @lastRun datetime, @RunNo int;
SET @err = 0;
/*
Create the temp tables to hold the results of DBCC
commands until the information is entered into
DBSpaceDist.
*/
CREATE TABLE #logspace (
[DBID] int,
DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int
);
CREATE TABLE #dataspace
( FileID int,
FileGrp int,
TotExtint,
UsdExt int,
LFileNmvarchar( 100),
PFileNmvarchar( 100)
);
CREATE TABLE #dbSpaceDist
(ServerName varchar(30),
[dbid] int,
DBNamevarchar( 40),
DBLogicalName varchar(40),
DBLocation varchar(100),
DBTotalnumeric( 10, 2),
DBUsednumeric( 10, 2),
DBPrcntnumeric( 10, 2),
DBGrwth int,
DBMaxSize int,
DBType char( 5),
EntryDate datetime
);
--- Get the log space
INSERT INTO #logspace(DBName, LogSize, PrcntUsed, [Status])
EXEC ('dbcc sqlperf(logspace)');
-- select * from #logspace -- for testing
--- Get the dbid into the temporary tables to prevent failure by using reserved characters in db name
UPDATE ls
SET ls.[dbid] = s.DBID
FROM #logspace ls
INNER JOIN sys.sysdatabases s ON ls.DBName=s.name;
--- Get the dbid into the temporary tables to prevent failure by using reserved characters in db name
INSERT INTO #dbSpaceDist
SELECT @@SERVERNAME,
sys.[dbid],
[dbname], sys.[name], sys.[filename],logsize,
(logsize * (PrcntUsed/100)),
(1 - ( PrcntUsed/ 100))*100,
sys.growth,
sys.[maxsize],
'Log', getdate()
FROM #logspace ls LEFT JOIN master.dbo.sysaltfiles sys
ON DB_ID(ls.[DBName]) = sys.[dbid] AND sys.fileid = 2
/*
Get the data space
Use a while construct to loop through the results from DBCC
since you have to run this command from each database
with a USE command.
*/
--@db becomes dbid instead of db name
declare @db int, @cmd char( 500)
SET @db = 0
while exists (select [dbid] from #logspace where [dbid] > @db)
begin
select @db = min([dbid]) from #logspace where [dbid] > @db;
dbcc updateusage(@db) with no_infomsgs;
select @cmd = 'use [' + DB_NAME(@db) + '] dbcc showfilestats';
insert #dataspace
exec( @cmd);
insert #dbSpaceDist
select @@SERVERNAME,
@db,
DB_NAME(@db),
LFileNm,
PFileNm,
((cast( TotExt as numeric( 10, 2))* 32) / 512),
((cast( UsdExt as numeric( 10, 2))* 32) / 512),
cast( UsdExt*100/TotExt as numeric( 10, 2)),
sys.growth,
sys.[maxsize],
'Data',
getdate()
from #dataspace d LEFT JOIN master.dbo.sysaltfiles sys
ON d.LFileNm = sys.[name] AND sys.fileid = 1
WHERE sys.dbid = @db;
delete #dataspace;
end;
-- Display result
select ServerName, DBLocation, DBTotal, DBUsed, DBPrcnt,
[DBName],DBType, EntryDate, DBGrwth, DBMaxSize, DBLogicalName
from #dbSpaceDist;
--- Drop the temporary tables
drop table #logspace;
drop table #dataspace;
drop table #dbSpaceDist;
August 20, 2010 at 12:51 pm
Here's a similar one I've used for a few years. Note that I have not updated this code since I was just figuring SQL out, so it can definitely be improved..but it works for me at the moment.
create table #db (
dbidINT,
nameNVARCHAR(100),
ownernvarchar(150),
recoveryNVARCHAR(50),
statusNVARCHAR(50),
total_sizeint,
log_sizenumeric(9,0),
log_usednvarchar(10),
log_reusenvarchar(60),
collationnvarchar(128),
auto_shrinknvarchar(3),
publishernvarchar(3),
subscribernvarchar(3),
)
create table #logspace (
name nvarchar(150),
logsize numeric(9,2),
logused numeric(9,0),
status int
)
insert into #logspace
exec('dbcc sqlperf(logspace)')
declare @name nvarchar(100)
declare @sql nvarchar(max)
declare getstuff cursor for
select name from master.dbo.sysdatabases
open getstuff
fetch next from getstuff into @name
while @@fetch_status = 0
BEGIN
select @sql = '
INSERT INTO #db (
[DBID]
,[Name]
,[Owner]
,[RECOVERY]
,[STATUS]
,[LOG_REUSE]
,[COLLATION]
,[AUTO_SHRINK]
,[PUBLISHER]
,[SUBSCRIBER]
)
SELECT database_id
,name
,suser_sname(owner_sid)
,recovery_model_desc
,state_desc
,log_reuse_wait_desc
,collation_name
,cast(is_auto_shrink_on as nvarchar)
,cast(is_published as nvarchar)
,cast(is_subscribed as nvarchar)
FROM master.sys.databases
WHERE name = '''+@name+'''
'
exec(@sql)
select @sql = '
UPDATE #DB
SET [total_size] = (select sum(size)/128 from ['+@name+'].dbo.sysfiles)
WHERE NAME = '''+@name+''''
exec(@sql)
select @sql = '
UPDATE #DB
SET [log_size] = (select logsize from #logspace where name = '''+@name+''')
WHERE NAME = '''+@name+''''
exec(@sql)
select @sql = '
UPDATE #DB
SET [log_used] = (select cast(logused as nvarchar) + ''%'' from #logspace where name = '''+@name+''')
WHERE NAME = '''+@name+''''
exec(@sql)
fetch next from getstuff into @name
END
close getstuff
deallocate getstuff
select [DBID]
,[Name]
,[Owner]
,[Recovery]
,[Status]
,[TOTAL_SIZE] as [Total Size (MB)]
,[LOG_SIZE] as [Log Size (MB)]
,[LOG_USED] as [% of Log Used]
,[LOG_REUSE] as [Log Reuse Wait]
,[Collation]
,case when [AUTO_SHRINK] = 1 THEN 'YES' else 'no' end as [Auto-Shrink]
,case when [PUBLISHER] = 1 THEN 'YES' else 'no' end as [Publisher?]
FROM #db
WHERE name not in ('master','msdb')
ORDER BY [TOTAL_SIZE] desc
SELECT 'All: '+cast(sum(TOTAL_SIZE)/1024 as nvarchar) + ' GB' as [Total] from #db
UNION ALL
SELECT 'Logs: '+cast(sum(cast(LOG_SIZE as int))/1024 as nvarchar) + ' GB' as [Total] from #db
GO
drop table #db
drop table #logspace
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply