Manage max table storage space in case of excess data (size in GB)
My scenario is that I am using sql server 2008 r2 on my end. I have created a database named testDB. I have a lot of tables with some log tables in this. some tables have contain lakhs of records in log table. So my purpose is that I want to fix the table size of those tables(log tables) and want to move records in other database table placed on another location. So my database has no problem. Please tell me, is there any way to make such above steps which I want for my database? Is there already built any such functionality in sql server? May be this question repeated but still I have no solution for my issue. Fill free to ask any query. Thanks
Yes, you can definately use SSIS to counter your problem. If you are not familiar with SSIS, use the Import Export Wizard in SQL Server 2008 to move your data. Then, you may set the limit of data you want (a week's data, a month's data) depending on your need and then delete the rest. Since, you have lakhs of records, an actual DELETE stament will be difficult. In such a case, an alternative is that, after you push all your data into the secondary database table, opt to TRUNCATE your original table and then import only the required data depending on your need, from the secondary database table into your original table. Later, set up a job to periodically move the data into your secondary database table, keeping only what is required in the primary table.