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.

SQL Server Database Corruption – File Header Page Corruption

-> I tried attaching a MDF file and got below error,

Msg 5172, Level 16, State 15, Line 1
The header for file ‘F:\data\JBDB_Data.mdf’ is not a valid database file header. The PageAudit property is incorrect.
Msg 1802, Level 16, State 7, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

-> It is clear from the message that the file header page of this database is corrupted.

-> File Header Page is the first page on a file that is used by SQL Server to store information about the attributes of the file. Refer article for more details.

-> If File Header Page is corrupted, the best option will be to restore the database with latest available backup.

-> Lets create a new database with same name as the corrupted MDF file. Stop SQL Services and then replace the MDF file of this newly created database with the corrupted MDF file that we were trying to attach. Start SQL Services and check the status of the database.

-> The database is in “Recovery Pending” State,

-> Lets check the SQL Server Error Log,

2021-04-10 17:16:03.250 spid23s Starting up database ‘JBDB’.
2021-04-10 17:16:03.250 spid11s Starting up database ‘mssqlsystemresource’.
2021-04-10 17:16:03.250 spid23s Error: 5172, Severity: 16, State: 15.
2021-04-10 17:16:03.250 spid23s The header for file ‘F:\data\JBDB_Data.mdf’ is not a valid database file header. The PageAudit property is incorrect.

-> Tried executing DBCC checkdb on the database and got below as expected,

dbcc checkdb ([JBDB])
GO

Msg 945, Level 14, State 2, Line 1
Database ‘JBDB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

-> Lets try putting the database in emergency mode,

ALTER DATABASE [JBDB] SET EMERGENCY;
GO

-> Getting below error which same as previous occurrences,

Msg 5172, Level 16, State 15, Line 1
The header for file ‘F:\data\JBDB_Data.mdf’ is not a valid database file header. The PageAudit property is incorrect.

-> The only option left is to restore the database with a valid backup. But the bad news is that there are no backups.

-> SQL Server Database recovery tools is something we can check as an option. In my case, I tried “Stellar Repair for MS SQL“.

-> Before using Stellar Repair for MS SQL tool, it is better to backup the MDF and LDF file for safety.

-> Once done make another copy of the files to use it with Stellar Repair for MS SQL tool. In my case I have placed the file in location F:\Debug,

-> Open “Stellar Repair for MS SQL” and click on “Select database”,

-> Select the corrupted MDF file and click on Repair,

-> Repair completed,

-> Once repaired, Click on Save,

-> Processing this new database is going to take time depending on the database size. The time spent is definitely worth it, as I was able to recover the whole database.

-> Once the database is recovered. Do thorough testing and make sure the database looks alright.

-> I executed DBCC CHECKDB on the new database recovered by Stellar Repair for MS SQL and it came out clean.

-> There you go. Database is available now.

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.

SQL Server Query Optimization – Introduction

-> This is an Introduction blog post in a series of posts about optimizing SQL Server queries :-

-> SQL Server Parameter Sniffing
-> @@FETCH_STATUS -9
-> What DBCC SHOW_STATISTICS tells me
-> Trace Flag 7471
-> Tempdb contention
-> SQL Server Triggers and Insert performance
-> Optimize for ad hoc workloads
-> Why Bookmark Lookup is BAD?
-> Optimizing Queries using XML in SQL Server
-> Slow query performance with Table Spool operator

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.