40-1 ratio for disk space to DB size?

Question

40-1 ratio for disk space to DB size?

Frank Rueff

SSC Enthusiast

Points: 185
More actions
June 20, 2005 at 8:47 am

#88734

Is this true? We are in a 'discussion' with vendors and their DBA is saying that we need a 40-1 ratio for disk space. Of course this is the reason that their application is not working correctly. The theory is that SQL needs this to be optimal. I have never read anything to this effect. Basically we have a 12GB database that needs a 480GB partition of disk space, according to them.
Thanks
Frank

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 1

The only guideline that I've heard about relating to this sort of thing is to keep your windows volumes with about 20% free space, for performance reasons. That would mean that you would need a minimum of 14.4Gb (leaqving no room for growth). I've never heard anything about a 40-1 ratio, and I don't know of anyone who has implemented that.

Can you imagine a multi-terrabyte datawarehouse environment? The last one I set up was about 600GB of transactional data alone.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

No it's not true. It true that it's best to keep a few gig of spare for backups/restore operation, but 40-1 is jut ridiculous.

There's another claim like this one discussed here (started in the middle of the thread)

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=186664

Frank Rueff SSC Enthusiast Points: 185 More actions · Answer 3

I thought is was very strange. Thank you very much. I think I will have them come up some documentation to support their claims now. We have been trying to resolve this issue all weekend and have a call with them this morning.

Thanks

Frank

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Hi,

What do we know about the application?

- Does it use third-party FullText Search product? Add a lot of space then

- Is it a web app that uses Indexing Service? I was watching once how 3 GB of free space disappeared for 10 min during web page reindexing with a database less then 200 MB big but a lot of web pages for the same application. The space was reclaimed after

- 10 GB disappeared once in TempDB on 700 MB production DB and normally 10 MB TempDB with read-only user connection when this connection was by user description "using something new...in our code"

- How joins are implemented by the application?

- does this app stores files outside the database? I have applications with databases in MBs that store pointers to files in the file system which take GBs

- What are the limits on imagination of the application developer? I can easily imagine a process that will temp. store data on the disc, XML for example.

Yelena

Regards,Yelena Varsha

Frank Rueff SSC Enthusiast Points: 185 More actions · Answer 5

The database is 12GB and the trans log is set at a initial size of 1GB. This is what the vendor requested. We have never had a problem of this server running out of disk ever. Once a week I run a defrag script and shrink the DB.

No third party products.

No web components

I have never had this database grow more than 10 MBs a week if that. What I have noticed is that they have some queries that run a very long time. That is when the users report a slowdown in the system. My hands are tied on this becuase it is vendor supported and they have said that they don't want us making any changes.

Thanks

Frank

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

Could you run Profiler trace to see what exactly takes a lot of time? At least make sure where the delay is: in the database or in the front end

In the database: (1)indexes (2) locking

In the Front End: recordset processing. You may prove it by showing in profiler that everything submitted to SQL Server is processed right away and delays are between submitted statements.

Yelena

Regards,Yelena Varsha

Frank Rueff SSC Enthusiast Points: 185 More actions · Answer 7

Frank Rueff

SSC Enthusiast

Points: 185

June 20, 2005 at 10:56 am

#566806

Thanks, I will give that a try

terry-237585 Grasshopper Points: 16 More actions · Answer 8

You need whatever space the tables and indexes require, as well as space for log(s) for the largest transactions(s) that could be run (potentially this could be as large as the database).

You also need space for maintenance, which could easily be as large as the entire database including indexes.

Also, performance will be much better if the didsks aren't getting full.

I'd go at least 5x the size of the data and indexes. Disks are cheap.

Terry

tzara Say Hey Kid Points: 663 More actions · Answer 9

we keep 3 daily backups, plus translogs on a local raid volume (that goes to tape backup).

i'd say we have a minimum 12:1 ratio for physical storage requirements to actual database size. higher than that to account for some overhead, room for growth.