Maximum Access app database storage size in SQL Azure = 1GB - How can I check the current size?

Hi, I posted this thread on Office365(&SharePoint Online) forum and advised to create a new thread here. (http://community.office365.com/en-us/f/154/t/276871.aspx)

I was wondering if I should post this on "Access for Developers" forum, but thought would like to try this forum first.

This is to do with the Access WebApp, introduced with Acess2013, which uses Azure SQL database as back-end.

According to http://technet.microsoft.com/en-us/library/cc262787%28v=office.15%29.aspx#SpApps, regarding "Maximum Access app database storage size in SQL Azure", it says "Each Access app created on SharePoint Online creates a database on SQL Azure. 1 GB is the limit for the database storage on SQL Azure".

How would I be able to check the size of the current SQL Azure database?  I am looking after two Access Web Apps hosted on SharePoint Online.  I need to monitor the size of the databases so that they will not exceed 1GB.  The current snapshot backup files are just over 10MB, including all the data.  But SQL database size is a separate thing.  

I have access to the backend SQL Azure using SQL Management Studio, but the command such as "sp_helpdb" does not work.  I get "Could not find stored procedure 'sp_helpdb'." message.

I also tried the sql statement given in the thread in this forum "How to get the maximum size of Windows Azure SQL Database", but "VIEW DATABASE STATE permission denied in database" error message.

Would you please help me?

Many thanks in advance.

Keiko


November 12th, 2014 9:32pm

Keiko,

            You can use the system SP - sp_spaceused to get the database size information. See http://msdn.microsoft.com/en-us/library/ms188776.aspx

Srini

Free Windows Admin Tool Kit Click here and download it now
November 12th, 2014 10:35pm

Hi, Srini,

Thank you for your reply.

For the back-end SQL Azure database for my Access Web App, I can access the database on the SQL Management Studio, but the permission level is not enough to execute "sp_spaceused".

I get "Could not find stored procedure 'sp_spaceused'." error.

The SQL Azure database is automatically created by creating an Access Web App.  

Creating tables and views are done on Access application. There are only two types of users given to access this database outside SharePoint, Read-Only "xxxxx_ExternalReader" and Read-Write "xxxxx_ExternalWriter".

I use "xxxxx_ExternalWriter" to access the database on SQL Management Studio.  I do not have access to master database of the server.

I made an Access WebApp following the instructions such as What's new in Access 2013

Product info Access.

Is this something I should ask on Access for Developer Forum???  Or would you be able to help???

....I have just found yet another forum Access 2013 Web App Db size exceed issue  asking a similar question, but I am unable to find a clear answer as to how to check the database size ....

Keiko

November 12th, 2014 11:40pm

I also tried the sql statement given in the thread in this forum "How to get the maximum size of Windows Azure SQL Database", but "VIEW DATABASE STATE permission denied in database" error message.

Hi Keiko,

In Microsoft Azure SQL Database, querying a dynamic management view requires VIEW DATABASE STATE permissions. Please run the following query to grant the VIEW DATABASE STATE permission to your database user firstly.

GRANT VIEW DATABASE STATE TO database_user;

Then you can run the following query to get the size of your database (in megabytes). Refer to: Monitoring Azure SQL Database Using Dynamic Management Views.

-- Calculates the size of the database. 

SELECT SUM(reserved_page_count)*8.0/1024
FROM sys.dm_db_partition_stats; 

GO

Also, you can check your database size and its usage through Windows Azure Management Portal. For more details, please review the following similar thread.
http://stackoverflow.com/questions/13296150/calculate-size-of-sql-azure-database



Thanks,
Lydia Zhang


Free Windows Admin Tool Kit Click here and download it now
November 14th, 2014 3:10am

Hi, Lydia,
Thank you for taking time for me.

The backend Azure Database is created automatically when creating an Access Web App from Access 2013.  Then user is given the connection info such as server name, database name, user name and password.  They are all auto created, so the database is not on my Azure account.

Therefore, with the login I am given by Access 2013, I am not able to execute "GRANT VIEW DATABASE STATE TO database_user;"


Now I found another forum, dedicated to Access 2013, and posted the question there too.
http://answers.microsoft.com/en-us/office/forum/office_2013_release-access/maximum-access-app-database-storage-size-in-sql/fca233c5-87a0-4a74-8979-f76ab6eb7f26


I found here what to do if I get the error message of "exceeding 1gb", but I would rather prevent getting the error. https://support.microsoft.com/kb/2849710?wa=wsignin1.0

Access 2013 Web App hosted on SharePoint Online, which is a part of Office 365 subscription, and the back-end database is hosted in Azure.  All Microsoft technology.  It is very interesting, but can be very confusing, but I hope someone knows the answer I am looking for.

Best regards

Keiko
November 14th, 2014 4:37pm

I also tried the sql statement given in the thread in this forum "How to get the maximum size of Windows Azure SQL Database", but "VIEW DATABASE STATE permission denied in database" error message.

Hi Keiko,

In Microsoft Azure SQL Database, querying a dynamic management view requires VIEW DATABASE STATE permissions. Please run the following query to grant the VIEW DATABASE STATE permission to your database user firstly.

GRANT VIEW DATABASE STATE TO database_user;

Then you can run the following query to get the size of your database (in megabytes). Refer to: Monitoring Azure SQL Database Using Dynamic Management Views.

-- Calculates the size of the database. 

SELECT SUM(reserved_page_count)*8.0/1024
FROM sys.dm_db_partition_stats; 

GO

Also, you can check your database size and its usage through Windows Azure Management Portal. For more details, please review the following similar thread.
http://stackoverflow.com/questions/13296150/calculate-size-of-sql-azure-database



Thanks,
Lydia Zhang


There seems to be a bit of confusion amongst Microsoft support staff. The Office 365 team think this part of Access web apps, as hosted via Office 365, is not their baby since the data is stored in SQL Azure, yet the SQL Azure team are unaware of the limitations imposed on the SQL Azure databases by them having been created in Office 365, i.e. a lack of permissions required to use stored procedures.

It would be good if someone at Microsoft would join things up and provide the answer to the question posed. Currently the only way we seem to have to find out when we hit the 1GB limit is when the thing stops working (been there, done that, got the tee-shirt). It is not a satisfactory method.

Free Windows Admin Tool Kit Click here and download it now
November 21st, 2014 11:54am

I welcome Alan's support!  
Thank you Alan to visiting both of my threads.  I am posting this reply on both threads.  Actually I posted the question to yet another forum, but no reply there yet.


While looking for an answer, I did my research.

1. Save my web app as an "App Package" including all data and check the size.

2. Create a SQL database and import the data from the main tables of the web app back-end Azure SQL and check the size of the database, where I have full permission to check the size. I am using SQL Express 2014 installed on my laptop.

Results:

AppA: App Package about 2mb  >>>>> SQL DB 32mb
AppB: App Package about 3mb >>>> SQL DB 37mb
AppC: App Package about 11mb >>> SQL DB 200mb
Here I am quite impressed how compact the app packages are.

So I am a kind of guessing that if I keep my package under 30mb or so, perhaps I will be OK....., way below 1GB.......but some guidance from Microsoft would be nice..... or does anyone know?


Many thanks
Keiko

November 21st, 2014 5:40pm

Hello Keiko,

The following query should give you the database size - 

SELECT SUM(reserved_page_count)*8.0/1024 FROM sys.dm_db_partition_stats;

We will have to try to connect to the database with the admin account, if the above query fails for permissions problems.

Would you have the Azure SQL Databse admin credentials, or access to the Azure subscription hosting this database?

Regards,

Kumar Bijayanta

Free Windows Admin Tool Kit Click here and download it now
December 23rd, 2014 9:15am

Hello Keiko,

The following query should give you the database size - 

SELECT SUM(reserved_page_count)*8.0/1024 FROM sys.dm_db_partition_stats;

We will have to try to connect to the database with the admin account, if the above query fails for permissions problems.

Would you have the Azure SQL Databse admin credentials, or access to the Azure subscription hosting this database?

Regards,

Kumar Bijayanta

This doesn't seem to work. With an Azure SQL Database created by Office 365 there is no access to such admin credentials. That's the point. Access web apps do a wonderful job in many ways, but the moment you want to step outside the tightly controlled design experience, you are stuck. All design stuff in the Azure SQL Database is read-only.

However, Jeff Conrad from the Access development team kindly responded to a similar request for information on the UK Access User Group listserver thus:

There is currently no way to determine the size of an Access 2013 web app database when it is less than 700 MB.

We inform customers that their database is approaching the size limit when their SQL database hits 700 MB. At that point, we provide a message bar in yellow at the top of the app (in the web browser), which tells the customer that they are approaching the limit. The message displays the current size. Heres a sample message from a big test app:

"Your current database size of 958.88 is 93.64% of the 1024 MB size limit. When your app reaches the size limit, you cannot insert or update any data without freeing up some space. The app might remain in a delete-only state for up to a day."


December 24th, 2014 7:20am

Hi Alan,

Thank you for sharing the above.

Regards,

Mekh.

Free Windows Admin Tool Kit Click here and download it now
December 24th, 2014 8:26am

Hi, Alan,

Thank you for finding the answer I was looking for!

At least there will be a warning before the size limit.  I am please to know that.

I am going to post this solution to the two other forums I raised the same question (Office 365 forum, and Access forum)

a Merry Christmas & a Happy New Year!

Keiko

December 24th, 2014 2:12pm

Has anything about the above changed in the last quarter?  It's a bit surprising that more than a year on we're still dealing with an artificially low 1GB cap, for instance.
Free Windows Admin Tool Kit Click here and download it now
March 29th, 2015 3:32am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics