SQL Query to delete while database size is larger than 200GB

azure-sql-database

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.

Best Answer

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

Related Solutions

SQL Azure Fragmentation / Database Size

Matthew,

I don't have direct experience with SQL Azure but I think same rules apply here as a normal SQL Server instance.

280 mb is a very very small database and the cost of fragmentation is almost 0. Coming to the size of this small database, I don't think you can control that nor should you be worried.

The above is because when SQL Server creates a new table, it doesn't know how big will that table be. Data is stored in pages of 8 kb and an extent is a collection of 8 contiguous pages. There are 2 types of extents, mixed and uniform extents. Uniform extent means all the 8 pages will be used by the same object (say TableA) and mixed extent means that this extent can be used by as many as 8 different tables. Since SQL Server doesn't know how big the table will be, it will start with single page allocations until the table reaches 3 extents and there after it uses uniform extents. This is the reason, fragmentation is high in small tables and but you shouldn't be worried abt it.

Instead of looking how big is the database, you should look at how much of it is empty. I am NOT sure how this affects the billing though. HTH

Error while creating index in SQL Azure

The catalog view sys.all_objects won't show nonclustered indexes. Here's the query you should be using:

select
    object_name(object_id) as table_name, 
    name as index_name
from sys.indexes
where name = 'IX_Children_ParentID'

Or to get all of the indexes for that table:

select
    object_name(object_id) as table_name, 
    name as index_name
from sys.indexes
where object_name(object_id) = 'Children'

Best Answer

Related Solutions

SQL Azure Fragmentation / Database Size

Error while creating index in SQL Azure

Related Question