How to truncate SQL database on Microsoft Azure periodically

Question

I have a SQL database running on Microsoft Azure. To preventing it from getting too big, I have to truncate it periodically (e.g., a day or two). truncate table xxx is the SQL that I need to execute.

So what is the easiest way to achieve this? I prefer not writing any C# code unless I have to do so. Can I use a web job which continuously running a truncate SQL statement? Or can I use a built-in functionality of SQL database on the Azure to achieve this? Thanks!

Answer 1

Actually, you could use an Azure Function ("TimeTrigger" type) to periodically purge your tables, here is an example of code to use inside a C# TimeTrigger Azure Function to get a connexion to your Azure Sql Database and execute "delete" sql queries :

#r "System.Data"

using System;
using System.Data.SqlClient;

public static async Task Run(TimerInfo myTimer, TraceWriter log)
{

    string userName = "*******";
    string passWord = "********";
    var connectionString = $"Server=tcp:work-on-  sqlazure.database.windows.net,1433;Data Source=work-on-sqlazure.database.windows.net;Initial Catalog=VideoStore;Persist Security Info=False;User ID={userName};Password={passWord};Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

    using(SqlConnection cns = new SqlConnection(connectionString))
    {
         cns.Open();
         var truncateUserTable = "DELETE FROM Video";

         using(SqlCommand cmd = new SqlCommand(truncateUserTable, cns))
         {
             int rowsDeleted = await cmd.ExecuteNonQueryAsync();
         }
    }
}

Then you can configure the timer logic from the Azure Azure Function "integrate" space with a cron expression.

Answer 2

SQL Azure does not yet have any sort of SQL Agent functionality so you'll have to create a web job (or some JavaScript that executes the SQL you need executed) and then use Azure Scheduler to schedule the job.

You can also use Azure Automation that involves Powershell to do the same thing.

Answer 3

You can create a Elastic database pool and include your database in the pool.once your are done doing this,you can run set of tasks or queries against all databases in the pool or single database..

More on this here..

https://azure.microsoft.com/en-in/documentation/articles/sql-database-elastic-jobs-overview/