0

I have a SQL table which is used to store a large number of logs.

I want to have a stored procedure or something along the lines of that. That runs every minute and deletes items from the end of the log table as long as the database size is above 200GB.

so something like:

While(databaseSize > 200GB)
{
Batch delete 100 entires From [dbo].[logs] OrderBy DateTime
}

How would I go about doing this?

[Edit] SQL Azure limits my database size to 250GB. Hence why I need to limit and still keep a small buffer for other tables.

6
  • cannot use a maintenance plan or a job?
    – McNets
    Commented Nov 15, 2016 at 19:18
  • 2
    Remember that deleting records does not release space back to the OS. So I dont understand the rationale behind checking database size and then deleting the unwanted records. Instead the logic should be based on the no of days that you want to keep the records and delete records that are X days older. e.g. delete all records that are older than 180 days.
    – Kin Shah
    Commented Nov 15, 2016 at 19:33
  • Every minute you are going to delete 100 at a time? That is silly.
    – paparazzo
    Commented Nov 15, 2016 at 20:08
  • 1
    @Kin In Azure you are charge by database size.
    – paparazzo
    Commented Nov 15, 2016 at 20:15
  • @Kin using number of days is also tricky because some days vary a whole lot more than others. And there could be the odd case, say December when the days get larger and my database fills up. Ps im using sql azure. Commented Nov 16, 2016 at 4:45

2 Answers 2

1

In a maintenance plan run something like this like hourly

While((select count(*) from From [dbo].[logs]) > 1000000)
begin
    delete top (10000) From [dbo].[logs] OrderBy DateTime
end
9
  • It is not that simple in Azure SQL.
    – user507
    Commented Nov 16, 2016 at 4:16
  • So this is essentially keeping the first million, vs delete the last thousand approach. Commented Nov 16, 2016 at 4:44
  • @Zapnologica It is an approach with valid syntax.
    – paparazzo
    Commented Nov 16, 2016 at 7:18
  • This still does not directly solve my issue with relating to size. This approach assumes a standard size per db row? Commented Dec 29, 2016 at 18:07
  • No kidding. Database size is the size allocated not the actual size of the data. It allocates more space when it runs out of space. You are just going to need to figure out to calculate data size.
    – paparazzo
    Commented Dec 29, 2016 at 18:12
0

To run your script on a scheduled basis you will need to build a PowerShell workflow using the Azure Automation service. There is a good walk-through of this on Azure's documentation site here.

I will also note that there is a better version of this offering coming as "Elastic Jobs". It will offer much more control and features that are closer to what SQL Agent does for on-premise. They are opening up invotes for private previous now but you can read more about this from Mark Vaillancourt here.

Just remember that Azure SQL databases still have a log just like any other database in on-premise SQL Servers, so make sure you your delete query is optimized for log usage. It can ensure the delete runs efficiently and does not lock up the table for an extended period.

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.