Azure Database Size Same Before and After Deleting Most of the Rows

azure-sql-database

I have a database hosted on Azure which is showing as nearly full, it is at 1017MB/1024MB. The table contained around 27000 rows, I have deleted 20000 of those rows to try and free up space, however the amount of available space is the same as before I deleted the rows.

The Database only has one table the create script is below as you will see it only has text stored in the database and should not be as high as this.

CREATE TABLE [dbo].[VersionLog](
[Id] [varchar](255) NOT NULL,
[Date] [datetime] NOT NULL,
[WanIP] [varchar](255) NOT NULL,
[LocalIP] [varchar](255) NOT NULL,
[ShopName] [varchar](255) NOT NULL,
[MachineName] [varchar](255) NOT NULL,
[ApplicationName] [varchar](255) NOT NULL,
[VersionNumber] [varchar](255) NOT NULL,
[ServerName] [varchar](255) NOT NULL,
CONSTRAINT [Id] PRIMARY KEY CLUSTERED 
 (
    [Date] ASC,
    [LocalIP] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

EDIT:

I re-indexed the table and have waited over the weekend to see if the database would shrink as per the suggestion below, although it has remained at the same size.

I have also attached a picture of how much each table in the database is using up. (in MBs) Table Sizes in database in mbs

Best Answer

I think this may be answered here: https://stackoverflow.com/questions/19677883/how-to-shrink-azure-sql-server-db-18mb-of-data-charged-for-5gb-of-server-space

To summarize You need to reindex, then wait after you have done the deletes.

I don't think azure counts the size of logs in the space used.

Also see here to check what is using the space (I have summarized below) http://blogs.msdn.com/b/dilkushp/archive/2013/07/28/fragmentation-in-sql-azure.aspx Once you have done the reindex to check out various sizes run the following script.

-- Size of each table in MB
select obj.name, sum(reserved_page_count) * 8.0 / 1024 as "size in MB" from sys.dm_db_partition_stats part, sys.objects obj where part.object_id = obj.object_id group by obj.name
Order By sum(reserved_page_count) desc

-- Total Database size in Meg
select sum(reserved_page_count) * 8.0 / 1024 as "size in MB" from sys.dm_db_partition_stats

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

Create user on Azure elastic database and deny view to other databases i same setting

Reading you comment I see now what you are looking for.

Here is how you can do this.

Create 2 empty database:

USE [master]
GO
CREATE DATABASE [TestPerm1]
GO
ALTER DATABASE [TestPerm1] SET COMPATIBILITY_LEVEL = 130
GO


USE [master]
GO    
CREATE DATABASE [TestPerm2]
GO    
ALTER DATABASE [TestPerm2] SET COMPATIBILITY_LEVEL = 130
GO

Create 2 login

USE [master]
GO    
CREATE LOGIN [testPerm1] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [master]
GO    
CREATE LOGIN [testPerm2] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Create a user in master using one of the login

USE [master]
GO
CREATE USER [testPerm2] FOR LOGIN [testPerm2] WITH DEFAULT_SCHEMA=[dbo]
GO

Using SSMS connect with user testPerm2 using default setting. You will be able to see all database ( I know you do not want this, but hang on)

Create a user with testPerm1 in testPerm1 database that we created earlier.

USE [TestPerm1]
GO
CREATE USER [testPerm1] FOR LOGIN [testPerm1] WITH DEFAULT_SCHEMA=[dbo]
GO

Now using SSMS connect to the server by changing default database, this user will only see testPerm1 but not testPerm2.

Best Answer

Related Solutions

SQL Azure Fragmentation / Database Size

Create user on Azure elastic database and deny view to other databases i same setting

Related Question