0

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?

3
  • Adding data will increase the size of your DB if you have used the available space. Are you able to archive older data? Commented Nov 16, 2016 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ᵀᴹ
    Commented Nov 16, 2016 at 8:53
  • @Phil..yup m not aware about application.. and we have retention period of 12 months
    – Rehan
    Commented Nov 16, 2016 at 9:09

1 Answer 1

2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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