Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

I think this may be answered here: http://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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.