March 11, 2004 at 1:44 pm
I am a newbie to SQL server, I can do basics.
I have been taked to calculate the space needed for a new SQL Server. How on earth can I do this with 12 database and hundreds of tables? I need a simple calculation method or a tool I can do this with.
Can anyone help me please
March 11, 2004 at 3:00 pm
Unfortunatly I know of no easy way to figure this out. If you go to SQL books online, and search for "Calculating database size", there is a lengthy formula there. That is the only way I know how, still kind of new to this myself.
March 12, 2004 at 2:35 am
If the databases already exist, take a sample of how many records appear in a timeframe of say, a year for each table (get the year with the most records). This will tell you your biggest growth to date.
Then use that figure to project future growth.
Is a tiresome task, but will save headaches in the future.
If you're expected to size databases that don't exist just yet, that is
a question that can only be answered once the business has made up its mind
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
March 12, 2004 at 6:00 am
Would check database and LOG file sizes periodically and use the following proc to learn what individual tables are doing i.e, what size each table is using within the data base. A point of caution it is possible that depending upon additions / updates / deletions made to data to have the log file grow faster than the database so if it is disc space you are worried about - check both
Also Books on Line has some great discussion of database and log file size prediction. What I get from BOL is predicting size of a non existent database and associated log file is not much more than a guessing game
To track table size and space each table uses within the database try this proceedure
(Disclaimer - I owe this procedure to another DBA whose name I have forgotten, and who I probably forgot to thank, for sharing his knowledge with us here on the forums, but I use it frequently)
CREATE procedure space_used
@TablePattern varchar(128) = '%',
@updateusage varchar(5) = false
as
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname nvarchar(776)
create table #spt_space ( id int null,
rows int null, reserved dec(15) null,
data dec(15) null, indexp dec(15) null,
unused dec(15) null )
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin raiserror(15143,-1,-1,@updateusage)
return(1)
End
End
select @dbname = db_name()
declare cur cursor for select TABLE_NAME = convert(sysname,o.name) from sysobjects o where o.type in ('U')
and convert(sysname,o.name) like @TablePattern
order by 1
OPEN cur
FETCH NEXT FROM cur INTO @objname
WHILE @@FETCH_STATUS = 0
begin
select @id = null
select @id = id, @type = xtype
From sysobjects where id = object_id(@objname)
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
End
if @updateusage = 'true'
dbcc updateusage(0,@objname) with no_infomsgs
set nocount on
insert into #spt_space (id, reserved)
select @id, sum(reserved) From sysindexes where indid in (0, 1, 255) and id = @id
select @pages = sum(dpages) From sysindexes Where indid < 2 and id = @id
select @pages = @pages + isnull(sum(used), 0) From sysindexes Where indid = 255 and id = @id
update #spt_space
set data = @pages where id = @id
update #spt_space
set indexp = (select sum(used) From sysindexes where indid in (0, 1, 255) and id = @id) - data
where id = @id
update #spt_space
Set unused = reserved
- (select sum(used) From sysindexes where indid in (0, 1, 255) and id = @id)
where id = @id
update #spt_space
Set Rows = I.Rows from #spt_space s join sysindexes i on i.id = s.id Where I.indid < 2 and i.id = @id
FETCH NEXT FROM cur INTO @objname
End
Close cur
DEALLOCATE cur
select name = substring(object_name(id), 1, 30), rows = convert(char(11), rows), reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space s, master.dbo.spt_values d Where d.Number = 1 and d.type = 'E'
order by s.reserved * d.low desc
select name = cast('TOTAL' as char(30)),
rows = convert(char(11), SUM(rows)),
reserved = ltrim(str(SUM(reserved * d.low) / 1024.,15,0) + ' ' + 'KB'),
data = ltrim(str(SUM(data * d.low) / 1024.,15,0) + ' ' + 'KB'),
index_size = ltrim(str(SUM(indexp * d.low) / 1024.,15,0) + ' ' + 'KB'),
unused = ltrim(str(SUM(unused * d.low) / 1024.,15,0) + ' ' + 'KB')
from #spt_space s, master.dbo.spt_values d Where d.Number = 1
and d.type = 'E'
GO
March 12, 2004 at 6:46 am
sp_spaceused is another option...
Mike
March 12, 2004 at 7:07 am
Assuming these databases already exist, you can use sp_spaceused as someone already mentioned. Fairly accurate.
If these databases exist on another platform or are not yet implemented, it is better to take an educated guess as opposed to using sizing formulas out of BOL (would take too long). Better choice - if you have time and the software, use a data modeling tool such as CA ERwin to reverse engineer the data model (good exercise anyway) and from there you can use its volumetrics feature to estimate the size of a database for any of its supported platforms. This will give you a more accurate picture, and you only need to estimate your LARGEST tables to get in the ballpark.
Whatever you do, don't be afraid to add extra disk space to your estimate. Disk space is relatively cheap. You will need extra space to do most DBA tasks. Good luck.
March 12, 2004 at 7:39 am
You can use the following to get at each db size on the server and then sum for a total. This will give you a record for the db (fileID=1) and a record for the log (fileID=2).
insert into TableName (db, fileID, filesize)
exec sp_MSforeachdb @command1 =
'select db_name(dbid), sf.fileid, sf.size
from sysdatabases sd, ?..sysfiles sf
where db_name(dbid) = ''?'''
March 12, 2004 at 8:42 am
Thanks to all for your responses. I am very greatful
xydon
March 13, 2004 at 5:43 pm
I am a newbie to T-SQL so please bear with me. I am trying to understand and get your query to work for me. I created a 'tablename' with (db,fileID, filesize) and nothing happen. Please tell me what I am doing wrong. thanks
March 13, 2004 at 9:39 pm
greene
try this
--insert into TableName (db, fileID, filesize)
Use Master
exec sp_MSforeachdb @command1 =
"select db_name(dbid), sf.fileid, sf.size
from sysdatabases sd, ?..sysfiles sf
where db_name(dbid) = '?' "
Note the differences ...
Removed need to create a table in the master database. Used Query Analyzer and just displayed results in a grid format.
Added Use Master
subsituted " (quotation mark) for ' (single quote before word select)
removed extra ' (single quotation mark) before and after last ? (question mark)
Added " double quote after ' (single quote) as the last character of the TSQL statement ... then it worked.
Remember to use BOL - to understand what size is being reported - it is number of 8K pages ..
March 14, 2004 at 7:51 am
ok, thanks I will try it.
March 14, 2004 at 7:25 pm
Use Books on Line (BOL) - it is the help file when clicking help for Query Analyzer - look up "Setting Data Base Options" then scroll down to "Quoted Identifier" - might help you understand why I did what I did
April 9, 2006 at 8:38 pm
--what DBs not to include in searched criteria
select name
into #db
from master..sysdatabases
where name not in ('master','tempdb','model','msdb')
--#tB base table to store results of one database
create table #tB(name varchar(200), rows int, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
--#tR result table to store all output results (all DBs)
create table #tR(db varchar(200), name varchar(200), rows int, KBreserved int, KBdata int, KBindex_size int, KBunused int)
declare @dbName varchar(100), @sql varchar(3000)
DECLARE cs CURSOR FOR
SELECT name FROM #db order by name
OPEN cs
FETCH NEXT FROM cs into @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
print '> > '+@dbName
truncate table #tB
--store table sizes into #tB
select @sql='use '+@dbName+' exec sp_MSforeachtable "insert into #tB exec sp_spaceused ''?''"'
exec(@sql)
--store table sizes of 1 DB into #tR
insert into #tR select @dbName,name,rows,convert(int,replace(reserved,' KB','')),convert(int,replace(data,' KB','')),convert(int,replace(index_size,' KB','')),convert(int,replace(unused,' KB','')) from #tB
FETCH NEXT FROM cs into @dbName
END
CLOSE cs
DEALLOCATE cs
--> display results
select * from #tR order by db,rows desc
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply