April 8, 2013 at 9:36 am
Comments posted to this topic are about the item Script to find the total used size of all database inside SQL Server
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
April 19, 2013 at 11:04 am
This one seems better:
create table #dbusedsize ( name nvarchar(50), used_mb int)
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'INSERT INTO #dbusedsize( name, used_mb )
select name, FILEPROPERTY([name], ''SpaceUsed'')/128
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid
order by 1' + CHAR(13)
--Print(@SQL)
Exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
select name, SUM(used_mb) from #dbusedsize
group by name
drop table #dbusedsize
April 24, 2013 at 6:59 am
Here's the one I use day-to-day, it avoids cursors.
/* SQL 2005+ VERSION */
SELECT type_desc'Type',SUM(size)/128'TotalServerSizeMB'
FROM sys.master_files
GROUP BY type_desc
ORDER BY TotalServerSizeMB DESC
SELECT
database_id'dbID',
DB_NAME(database_id)'dbName',
file_id,
name'LogicalName',
type_desc'Type',
Physical_Name,
state_desc'Status',
differential_base_time'LastFullBackup',
CAST(size/128.0+0.5 as int)'Size(MB)',
CAST(max_size/128.0+0.5 as int)'MaxSize(MB)',
CASE is_percent_growth
WHEN 0 THEN CAST(growth/128 as varchar(10))+' MB'
ELSE CAST(growth as varchar(10))+' %'
END AS 'AutoGrowth',
'USE ['+DB_NAME(database_id)+'];
DBCC SHRINKFILE('+CAST(file_id as varchar(2))+',1,TRUNCATEONLY);' AS 'ShrinkCommand',
'ALTER DATABASE ['+DB_NAME(database_id)+'] MODIFY FILE (NAME = ['+[name]+'] , FILEGROWTH = '+
CASE
WHEN size>=128*1000 THEN'100MB' --size >= 1000MB
WHEN size>=128* 500 THEN '50MB' --size >= 500MB
WHEN size>=128* 100 THEN '10MB' --size >= 100MB
WHEN size*growth<=12800 OR size<=128*10 THEN '1MB' --growth < 1MB or size < 10MB
ELSE '5MB' --sizes < the above and > 10MB
END+
')'AS'FixAutoGrowthCommand'
FROM master.sys.master_files
WHERE 1=1
--AND database_id>4 --user DBs only
--AND database_id<=4 --system DBs only
--AND type_desc = 'ROWS' --data only
--AND type_desc = 'LOG' --transaction logs only
ORDER BY --size DESC,
DB_NAME(database_id),
file_id
/* SQL 2000 VERSION
SELECT CASE groupid WHEN 1 THEN'ROWS'ELSE'LOGS'END'Type',SUM(size)/128'TotalServerSizeMB'
FROM master..sysaltfiles
GROUP BY groupid
ORDER BY TotalServerSizeMB DESC
SELECT
dbID,
DB_NAME(dbid)'DB',
fileid,
name,
CASE groupid WHEN 1 THEN'ROWS'ELSE'LOGS'END'Type',
filename,
CAST(size/128.0+0.5 as int)'Size(MB)',
CAST(maxsize/128.0+0.5 as int)'MaxSize(MB)',
CASE
WHEN growth>=128 THEN CAST(growth/128 as varchar(10))+' MB'
ELSE CAST(growth as varchar(10))+' %'
END AS 'AutoGrowth',
'USE ['+DB_NAME(dbid)+'];DBCC SHRINKFILE('+CAST(fileid as varchar(2))+',1,TRUNCATEONLY);' AS 'ShrinkCommand'
FROM master..sysaltfiles
WHERE 1=1
--AND DBid > 4 --user DBs only
--AND DBid <=4 --system DBs only
--AND groupid = 1 --data only
--AND groupid = 0 --transaction logs only
ORDER BY size DESC,
DB_NAME(dbid),
fileid
*/
May 15, 2013 at 10:50 pm
Thanks for the suggestion.
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
May 2, 2016 at 3:45 pm
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply