The database ‘jbswiki’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

-> Application fails with below error when trying to connect to Azure SQL database,

Msg 40544, Level 17, State 2, Line 10
The database ‘jbswiki’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

-> Executing below query on “jbswiki” database to check the used and free space on the database,

if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%' 
SELECT [name], fileid, filename, [size]/128.0 AS 'Total Size in MB', 
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', 
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' 
FROM sysfiles 
else
SELECT @@servername as 'ServerName',db_name() as DBName,[name], file_id, physical_name, [size]/128 AS 
'Total Size in MB', 
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', 
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' 
FROM sys.database_files
go

-> From the screenshot above, it is clear that the database “jbswiki” is full.

-> Lets try a simple insert statement and check the behaviour,

-> Login to Azure portal and select the database jbswiki. On “Overview” tab you will see that the database is full,

-> Click on “Compute + storage” under Settings. Change the “Data Max Size” to an appropriate value. In my case I have changed from 100 to 500 MB. Click Apply,

-> Application connections to database jbswiki started working fine after above change.

-> Below is the view of “Overview” tab after the change,

-> Lets try an insert and check if it is working,

->It worked fine this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Creating a Linked Server in Azure SQL Managed Instance to Azure SQL Database

Requirement

-> Create a linked server to Azure SQL Database jbsqldbserver in Azure SQL Managed Instance jbmi-sql01.

-> Azure SQL Managed instance and Azure SQL Database are on different Resource group.

-> Azure SQL Database will have “Deny public network access” set to Yes.


-> Before creating a linked server in Azure SQL Managed Instance. I tried connecting to Azure SQL database using SQL Server management studio and got below error,

===================================
Cannot connect to jbmi-sqldb.database.windows.net.
===================================

Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database). (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=47073&LinkId=20476

Server Name: jbmi-sqldb.database.windows.net
Error Number: 47073
Severity: 14
State: 1
Line Number: 65536


-> It seems like the private endpoint is not configured correctly. I followed below procedure to get it created properly,

-> Opened the required Azure SQL Database from Azure portal and clicked on “Firewalls and virtual networks”, Clicked on “Create Private Endpoint”.

-> Complete the “Basics” tab as below,

-> Complete the “Resource” tab,

-> Complete the “Configuration” tab. Select the Vnet related to Azure SQL Managed instance,

-> It is to be noted that you cannot select the subnet used by Azure SQL managed instance, you will receive below error when you do that.

The selected subnet ‘MI’ has a delegation and cannot be used with a private endpoint.

-> Select a different subnet for MI and proceed further,

-> Complete the “Tags” tab and create the private endpoint,

-> Now I am able to connect to the Azure SQL Database,

-> Try creating Linked server now,

-> Right click created linked server and click on “test connection” and it connects just fine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Azure SQL Managed Instance, Azure database Health Check using Azure SQL Analytics

-> Azure SQL Analytics uses Azure log Analytics and provides several reports with regards to Azure SQL tasks and performance. It supports below Azure SQL resources,

  1. Azure SQL Elastic Pools
  2. Azure SQL Databases
  3. Azure SQL Managed Instance and databases

-> Let’s setup Azure SQL Analytics and check using below steps.

-> Click “Create a resource”, type “Azure SQL Analytics” and select it.

-> Once you click “Create”. You will be advised to select an existing Log Analytics Workspace or create a new one. In our case, I will be creating a new Log Analytics Workspace,

-> Azure SQL Analytics is created. Now data should be sent to Log Analytics workspace.

-> All Azure SQL resources like Azure SQL Managed Instance, Azure SQL Database or Elastic pool can be configured to send its data to Log Analytics workspace by configuring “Diagnostic settings” under “Monitoring” section.

  1. Azure SQL Managed Instance

-> Open appropriate Azure SQL Managed Instance from Azure portal. Click “Add diagnostic setting”,

1.1 Azure SQL Managed Instance Database

-> Open Azure SQL Managed Instance database from Azure portal. Click “Add diagnostic setting”,

2. SQL Elastic Pool

-> Open SQL elastic pool from Azure portal. Click “Add diagnostic setting”,

3. Azure SQL Databases

-> Open required Azure SQL database from Azure portal. Click “Add diagnostic setting”,

-> Let us try opening Azure SQL Analytics and check what details we have.

-> Open “Log Analytics Workspaces”,

-> Click on “Workspace Summary” and you will see below screen,

-> Click on “Azure SQL databases” and you will get details collected so far,

-> Click on “Resources per utilization Bucket” and you will see more details,

-> You can modify queries to get more details,

-> Now details for “Azure SQL Managed Instance”,

-> You can get more details from this solution that would give you some good information to complete a health check of Azure SQL database or managed instance

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.