I have a SQL Server 2008 R2 Standard Edition warehouse database of around 400GB and face daily hard disk full issues. I've checked table sizes and row counts. Below is an image of what I've got:

Table sizes and row counts

What steps should I take in order to maintain the database size on a daily basis without any data loss?

  • Adding data will increase the size of your DB if you have used the available space. Are you able to archive older data? – James Anderson Nov 16 '16 at 8:47
  • Capacity planning. If you don't know the application, then you'll probably have to consult with the business/developers to work out how to proceed. Do you archive older data after a certain retention period? Do you acquire more disk space? We can't help you with that – Philᵀᴹ Nov 16 '16 at 8:53
  • @Phil..yup m not aware about application.. and we have retention period of 12 months – Rehan Nov 16 '16 at 9:09

You have a few options. However, if your database is this size, normally it would only grow.

First option, make sure you need all the data. Ask to the business if it is necessary to keep all data, if not you could write some kind of archiving process to clean up older data, making place for new data to come in.

If your SQL Server version is enterprise you could benefit greatly by implementing row compression or even page compression, this will definitely compress your tables. However before you implement this always test, because this can have a negative effect on your performance. Especially when using Page compression. If your bottleneck is CPU I would not implement it. Here is a blog on what benefits you can get. https://www.mssqltips.com/sqlservertip/3187/demonstrating-the-effects-of-using-data-compression-in-sql-server/

Another thing you can do is add another disk to your database and add an additional filegroup & files on that disk. Then rebuild your indexes to that filegroup, in that way you open up space inside your current files, and you can use 2 disks instead of 1 for your data storage. This will also in most cases improve your IO operations. https://www.mssqltips.com/sqlservertip/2601/using-multiple-filegroups-for-a-database-and-changing-the-default-filegroup/

If you have partitioning in place, you could store parts of tables on other disks as well. This will also make the maintenance of your indexes easier since you can rebuild a partition instead of a whole index.

Also if this is a datawarehouse database, I've seen epic datacompression using Clustered Columnstore Index on your Fact Tables.

That are the thing I would look into.

| improve this answer | |
  • Thanks @Stijn... yup its a data warehouse.. can you tell me more about data compression using Clustered Columnstore Index on your Fact Tables – Rehan Nov 16 '16 at 9:13
  • What SQL Server version are you working with? – Stijn Wynants Nov 16 '16 at 9:13
  • SQL Server 2008 R2 – Rehan Nov 16 '16 at 9:15
  • Ok :) no Clustered Columnstore on that version. This is only available since 2014. Is it Enterprise? – Stijn Wynants Nov 16 '16 at 9:16
  • okay..nop its Standard.. – Rehan Nov 16 '16 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.