0

Problem:

Backups were failing in one of our servers with the below message: (Backups are taken via Netbackup)

Could not allocate space for object 'dbo.backupfile'.'PK__backupfi__57D1800AC4FFEEA3' 
in database 'msdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting 
unneeded files, dropping objects in the filegroup, adding additional files to the 
filegroup, or setting autogrowth on for existing files in the filegroup.>
    DBMS MSG - SQL Message <3009><[Microsoft][ODBC SQL Server Driver][SQL Server]Could not 
insert a backup or restore history/detail record in the msdb database. This may indicate a 
problem with the msdb database.

On checking, I could see that the Mount Drive on which the primary data file of msdb resides, was full (Total size: 99GB, Free space: 0 bytes). The drive didn’t have any unwanted files which I could delete and gain some easy disk space. So I looked around the Drive and found a database log file which was 6GB in size and of which more than 5.5GB was free. I thought shrinking it will free up quite some space in the Drive and solve the problem for the time being. But when I attempted the shrink on the Log file, I got an error message saying ‘It has been marked SUSPECT by recovery’. Fearing the worst, I refreshed the database list in the Object Explorer and Bingo..!! there it was, marked as ‘Suspect’

aok
  • 334
  • 4
  • 7

3 Answers3

2

Solution:

I checked the other drives in the server and found one with ample free space. So my next attempt was to detach the DB (say ‘XYZ’), move its log file to the other drive and then attach it. This would release enough space in the drive and would also let the DB recover from Suspect mode. So I tried detaching the DB, but it didn’t work.

Cannot detach a suspect database. It must be repaired or dropped. Cannot be opened as the DB is in Suspect mode

So I tried to bring it to Single User mode: Execute the script to put the DB to single user mode

USE master;
GO
ALTER DATABASE [XYZ]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Set the database to offline.

-- Take the Database Offline
ALTER DATABASE [XYZ] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

Now I was able to successfully detach the Database. After detaching, I copied the log file to the other drive which had enough free space. Then I tried to attach the DB back. But on attaching, It gave the below error msg:

Error: 5123 CREATE FILE encountered operating system error 5A(Access denied.)

To resolve it, the following are the steps I did:

  1. Right click on the .mdf file -> Properties
  2. Under the ‘Security’ Tad, press Continue
  3. Click ‘Add’ to include you in ‘Groups or user names’
  4. Give you login Id in the Pop -up that came up, ‘Check Names’ and ‘Ok’
  5. After adding your login, click on it, and in the Box below (Permissions for User), check Allow ‘Full Control’ for the User. It is very important that you Check ‘Full control’ because by default the user will be having only ‘Read’ and ‘Read & Execute’ permissions. And if you try attaching with just those default permissions, you’ll again get Access denied errors.

After this, I was able to successfully attach the DB, and its status was found to be ‘Normal’.

Hope this will help somebody :)

aok
  • 334
  • 4
  • 7
0

Never detach a database in this situation. I also suggest not to Run DBCC CHECKDB with repair allow data loss option as you may loss your important data. You can check this reference: https://community.spiceworks.com/topic/1078473-sql-server-database-in-suspect-mode

jerold garcia
  • 1
  • Seems it's not a question for the answer. You can reply the question for the answer. – jacobkim Aug 09 '21 at 13:41
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Tyler2P Aug 22 '21 at 11:51
0

Try to use master database It will reset the state of the database you can work with it.

EXEC sp_resetstatus your_db_name
ALTER DATABASE your_db_name SET EMERGENCY
DBCC checkdb (your_db_name)
ALTER database your_db_name SET SINGLE_USER with ROLLBACK IMMEDIATE
DBCC CHECKDB (your_db_name, REPAIR_ALLOW_DATA_LOSS)
Alter database your_db_name SET MULTI_USER
developernader
  • 105
  • 1
  • 4
  • While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. You can find more information on how to write good answers in the help center: https://stackoverflow.com/help/how-to-answer . Good luck – nima Oct 17 '21 at 22:25