LARGE DATABASES (AND FEW FREE DISK SPACE) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

LARGE DATABASES (AND FEW FREE DISK SPACE)

I have used the W2K´s file attribute: compress. It saved more than 50% of my disk space! The users say me that the performance is not altered (the performance not changed, are the same).
It’s not recommended to compress the actual database files. Backups should be no problem. Using compression will require more cpu from your server.
Databases on compressed volumes may cause significant performance overhead. The amount will vary depending on volume of I/O and ratio of reads to writes. However, over 500 percent degradation has been observed under some conditions. Reliable transactional recovery of the database requires sector-aligned writes, which compressed volumes do not support. If backups to compressed volumes are performed, a complete DBCC should be run before each backup and after each restore. The time required to run the DBCC should be factored into the decision to use this backup technique.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
This db is about billing-like application: a single table have more than 90% of amount data. I think that is this case is possible economize disk space and have a few performance degradation. I´ll test the backup/recovery conditions. Thks a lot.
Ensure to perform DBCC checks alongwith the backup/recovery schedule to ensure the core area is not affected. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
let us know what you find out – generally we try to avoid compressing (or encrypting) the file system because it is bad for performance of the database.
Check out six performance tuning articles Brad McGehee published on this site- they are very good ; one of the steps is making sure that file system is not compressed (or encrypted).
What is your index allocation like- how far away are you from your optimal fillfactors set ion your indexes? Can any space be recovered there? check out DBCC SHOWCONTIG/INDEXDEFRAG in BOL simas
]]>