database size and raid configuration

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

db - 10 Mar 2008 14:59 GMT

Hi

I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.

Thanks

Signature

ontario, canada

Reply to this Message

db - 10 Mar 2008 18:50 GMT

When i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as

File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408

When I see the size of files in the disk using windows explorer I get
different size

File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB

Why is that difference in file sizes?

Signature

ontario, canada

> Hi
>
[quoted text clipped - 11 lines]
>
> Thanks

Reply to this Message

Tibor Karaszi - 10 Mar 2008 19:12 GMT

The unit for sysaltfiles is in pages (one page is 8KB).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> When i select size of files using sql server using
> "select name,filename,size from sysaltfiles" I get size of files as
[quoted text clipped - 29 lines]
>>
>> Thanks

Reply to this Message

db - 10 Mar 2008 19:38 GMT

Thanks Tibor.

1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.

2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications??
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?

4. Test database space requirements (I know which databases require test
database). What is the ideal size??
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?

Signature

ontario, canada

> The unit for sysaltfiles is in pages (one page is 8KB).
>
[quoted text clipped - 31 lines]
> >>
> >> Thanks

Reply to this Message

TheSQLGuru - 10 Mar 2008 21:07 GMT

2) I would plan on 50-75% growth per year based on your very limited
information.

3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.

4) We cannot guide you in this area without a good deal more information.

5) Again, need much more information.

6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!

BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Thanks Tibor.
>
[quoted text clipped - 54 lines]
>> >>
>> >> Thanks

Reply to this Message