Summary: This post discusses why a database goes into SUSPECT mode. Also, it explains the methods to remove database from suspect mode in SQL Server 2012.

You may find a SQL Server database marked as SUSPECT if the transaction log file is damaged, preventing database recovery or a transaction from rolling back. The transaction log can get corrupted due to any of these reasons:

• When the database was shut down abruptly and trying to access the database puts it in recovery. But, the recovery process fails due to corruption in data page or log record.

• The database was running some operations when the SQL Server crashed leading to corruption that prevents a transaction from rolling back.

Besides the above-mentioned reasons, a database can be in SUSPECT state due to insufficient disk space, faulty hardware, or corruption of data files.

Whatever be the reason behind SUSPECT database, you will need to bring it online

How to Remove SQL Server Database from Suspect Mode?

Method 1 – Restore from Backup

Restoring database from the last good backup is the best approach to recover the suspect database without data loss. But if the backup is not updated or is damaged, skip to the next method.

Method 2 – Use EMERGENCY Mode to Fix the SUSPECT Database

Once the database is set to EMERGENCY mode, bring it to SINGLE-USER mode for running the repair process. Next, repair the database by using the “DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS”. Let’s discuss the complete process step by step in detail:

Step 1: Set Database into EMERGENCY Mode

Note: If you can’t set the database to EMERGENCY state, make the database OFFLINE and then bring it ONLINE again.

Step 2: Set Database into SINGLE_USER Mode

The EMERGENCY mode allows multiple users who are members of the sysadmin role to access the database. However, to run the repair process, you will need to put the database in SINGLE_USER mode and use ‘WITH ROLLBACK IMMEDIATE’ to roll back any uncommitted transactions. To do so, execute this query:

Step 3: Back up the Database

Now before running the repair process, back up your database with all the rolled back transactions. If anything goes wrong during db repair, you’ll have a backup you can restore to. 

Step 4: Repair the Database

Caution! Using the ‘REPAIR_ALLOW_DATA_LOSS’ involves risk of data loss. If the repair option find a corrupt record on a data page, it may delete that entire data page.

To repair the database, run this query:

Note: If running the DBCC CHECKDB with the repair option fails, chances are that your database is severely corrupted. Skip to the next solution to repair corrupt SQL database.

Step 5: Set the Database in MULTI_USER Mode

For this, execute this command:

Here’s the complete query you need to execute to remove database from SUSPECT mode and bring it to normal mode in SQL Server 2012:

Step 6: Refresh the Server

Once you’ve completed all of the above steps, make sure to refresh the database server.

Method 3 – Use a SQL Repair Tool

If the repair method can’t fix the damaged database or you can’t risk losing any data, try using a third-party SQL repair tool.

Use Stellar Repair for MS SQL software to bring your suspect database to its normal form without any data loss. The software provides an enhanced preview of the recoverable database objects. You can get back all the data you can see in the preview.

Conclusion

A database goes into suspect mode because of corruption that prevents transactions from rolling back. Or, when database recovery won’t complete due to corrupt log records or data pages. You can change the database from SUSPECT mode to NORMAL mode by restoring it from the last known good backup or set the database in EMERGENCY state and run DBCC CHECKDB with repair option – but use it as a last resort as it can result in data loss. A better alternative is to use a third-party SQL repair tool to regain access to your DB.

Problem

My SQL Server 2008 R2 database was stuck in suspect mode. The database was no longer accessible. I tried restoring it by setting it to ‘EMERGENCY’ mode and then executed the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option. Executing the query returned SQL error 8921. The complete error message reads:

Msg 8921, level 16, state 1, line 1
“CHECKTABLE terminated. A failure was detected while collecting facts. Possibly temp DB out of space or a system table is inconsistent. Check previous Errors.”

After some research, I found that the error occurs when the tempdb database runs out of space or the system tables are corrupted. The only solution I could find was to restore the database from backup. But, I didn’t have an updated backup available.

Solution

I decided to use a professional SQL database repair tool to fix database corruption. That’s when I came across Stellar Repair for MS SQL software.

What made me choose Stellar Repair for MS SQL?

The software offers remarkable features making it the most preferred SQL database repair software among users. Some of them are listed as below:

• Repairs database files (MDF or NDF) on Windows and Linux-based systems
• Recovers complete database objects from the corrupted file, including tables, keys, indexes, triggers, stored procedures, etc.
• Multiple file saving formats to save repaired file, including CSV, HTML, and XLS
• Allows selecting specific database components that you wish to restore
• Can restore database stuck in ‘suspect mode’, ‘recovery pending state’, and ‘restoring state’, etc.

How Stellar Repair for MS SQL Works?

Before you proceed with repairing a corrupt SQL database with Stellar Repair for MS SQL software, your PC must meet the following system requirements:

• MS SQL Server: MS SQL Server 2019, MS SQL Server 2017, MS SQL Server 2016, MS SQL Server 2014, MS SQL Server 2012, MS SQL Server 2008 R2, 2008, 2008 (64 bit), 2008 Express, MS SQL Server 2005, 2005 (64 bit), 2005 Express, MS SQL Server 2000, 2000 (64 bit), 7.0 and mixed formats.
• OS: Windows Server 2012/2008/2003 and Windows 10/8.1/8 /7
• Processor: Intel-compatible (x86, x64)
• Memory: 8 GB (recommended) 4 GB (minimum)
• Hard Disk: 250 MB for installation files

Step 1: Launch the software. This opens a ‘Select Database’ window with options to ‘Browse’ or ‘Search’ the corrupt database.

Note: Use the ‘Search’ to find the databases whose location is unknown.

Step 2: Once the SQL database file is selected, click ‘Repair’ to begin repairing the file.

Step 3: The software displayed two scan mode options: Standard Scan and Advanced Scan. It is recommended that you run the ‘Standard Scan’ for repairing the SQL database file quickly. While the ‘Advanced Scan’ option is for the users who can invest time in thoroughly scanning the corrupted SQL db file. After selecting the scan mode, click OK.

Note: The repair process can take hours to days, depending on your database file’s size.

Step 4: Once the software completes repairing the .MDF file, a ‘Repair Complete’ message box appears.

Step 5: Clicking OK opens a preview window displaying all the repairable database objects. The objects are listed on the left-pane, while the contents of an individual object is displayed on the right-pane.

Step 6: For saving the repairable SQL objects, click Save on the File menu.

Step 7: Select MDF file format for saving the repaired SQL database file, choose the location (default or new) for saving the file, and then enter details to connect to SQL Server. Click OK.

Step 8: When prompted with ‘Save Complete’ message box, click OK.

Executing these steps will repair your SQL database at the specified location.

Verdict

Despite taking precautionary measures to keep the SQL databases from turning corrupt, the database can still become corrupt at any time. Troubleshooting the corrupt SQL database manually can lead to increased database downtime and business loss if not handled properly. Stellar Repair for MS SQL is an effective and must-have tool that can easily repair a corrupt MDF file in a few simple steps. All you need to do is select the corrupted file, scan and repair it for errors, and then save the repaired file.

Introduction:

If your company operates constantly (24/7) your servers are always on. Sometimes you may face power failure due to the maintenance of some power stations. In order to avoid electric power failure, my initial suggestion is you can setup backup power generators, battery backups also called UPS (Uninterruptible Power Source). Let me explore my experience with you. I’ve had a power failure. Suddenly, the power went out. A few seconds later when the power came on, I turned on, the server then I saw that my main database files were corrupted. Sometimes due to power failure, the hardware is expired or the database files will be removed automatically.

These database records are very important for us and we need to recover these records as soon as possible. If we don’t know the exact solution then what we will do? We will restart the server multiple time and it will take too much time. At some point, our database files are recovered but mostly the database files are not recovered with this method. When we try to open the database, we continuously receive a message from the system with corruption.

Summary:

We don’t need to worry about it. In this article, I will show you multiple solutions that how we can handle all of these situations.

Solution 1:

In this solution, if we have the recent backup file of our database then we don’t need to worry. we can solve this problem using installed MS SQL Management Studio on the computer or server.

Follow the below steps to repair your corrupt SQL database:

Step 1: We need to run the MS SQL Management Studio and select the corrupted database.

Step 2: Do the right click on the selected database and you will see multiple options.

Step 3: We see that there are many options but I choose the options that are needed for the solution. We see that there are many options but I choose the options that are needed for the solution. After the right selection, we will see multiple options. Here we need to select the device because we have the backup file in our system (Computer).

Step 4: Here we can see that the right backup file is selected successfully.

Step 5: In step five or in the last step we just need to press the OK button then we will see that our database is restored successfully.

Solution 2:

In some cases, a CHECKDB T-SQL Command can be enough to repair the damaged database:

–You need to set the database to single-user mode first

–Then repair your database

–Once repaired, run this to return to multi-user mode:

Solution 3:

In this solution, we will use a software called Stellar Repair for MS SQL. Using this software, we can solve multiple problems in MS SQL Server. First, we need to download and install the SQL Database Repair Software. You need to follow few simple steps to repair the corrupt SQL database.

Step 1: This is the main interface of the software

Step 2: Select the corrupt MDF file

Step 3: Check the preview of all repairable database objects

Step 4: Stellar Repair for MS SQL allows the user to save the repaired file in the desired form. Here I’m storing my database file as an MDF file. For storing the database as an MDF file we need to select these options in the last small window. Then press the Save button. Our database file is stored successfully now we can open this database file in MS SQL Management Studio without any error message. You can also save your output file in more formats here you can see CSV, HTML, and XLS by selecting the option new database. If you find any error on this page during the selection you can refresh the window by pressing the refresh button.

Conclusion:

In this article, we discussed how we can handle corruption in our database. There are multiple reasons but power failure is most of the common reason for database corruption. If we use the above solutions, I’m sure that we can solve these types of corruption in some minutes. If the problem is not solved with solution one, we need to try with the second solution.

Introduction:

Since SQL Server preserves data in a version, those facts need to move somewhere, but where does it shift? In which Filegroups come into play in SQL Server. These are the substantial files on your disc volumes that hold data and can be used for backup and administrative control purposes. An indispensable thing that comes along the way of the database is its security and protection that includes the restoration of the database too. Piecemeal restore process comprises a set of restore step sequences, initiating with the primary and, at least, one secondary read-write filegroups followed by read-only filegroups.

Summary:

The first component to recognize is what types of files SQL Server makes use of:

The Primary Data File is the startup data file holding data in SQL Server and signals to other files present in your database. They have a .mdf extension on them.

Secondary Data Files hold all data that do not relate to the Primary Data File. They only hold user data, and so are user-defined Secondary Files are optional. Moreover, a secondary data file can be used to continue growing the size of the database if it rises above the limited capacity of a single windows file. The recommended extension for these files is a .ndf extension.

Transaction Log Files are the data files containing all logging actions available in the SQL server. Each database must contain at least one logging file. The recommended extension for these files is a .ldf extension.

All data files are preserved in the filegroups cataloged in the following table:

Filegroup	Description
Primary	This is the default filegroup. This filegroup accommodates the primary files. All system tables are designated to the primary filegroup.
Memory-Optimized Data	A memory-optimized filegroup is positioned on filestream filegroup
User-defined	Any filegroup that is pointedly built by the user when the user first creates or later modifies the database.

Every database possesses a primary filegroup. This filegroup has the primary data file and any secondary files that are not part of any other filegroups. User-defined filegroups can be created to categorize data files together for administrative, data allocation, and placement means. For instance, let there are three files named file1.ndf, file2.ndf, and file3.ndf, built on three disk drives, respectively, and appointed to the filegroup named fgroup1. A table can then be created explicitly on the filegroup i.e. fgroup1. The three disks will advance queries for data from the table; this will enhance performance. The same performance enhancement can be achieved by using only one file created on a RAID (redundant array of independent disks) stripe set. However, files and filegroups enable you to add new data to new discs easily.

Rules to be considered while designing Filegroups:

• A filegroup can be used by only one database. For example, file data.mdf and data.ndf, which has data and objects from the sales database, can be used by only one database.
• A file can be associated with only one filegroup.
• Transaction log files can never be a part of any filegroups. Transaction log files can never be a part of any filegroups.

Sometimes, a database restore from the backup is desired. As we know, we do have the choice to restore needed file groups, but not all of the filegroups are needed to make the database online at a specific time. It is always enforced to recover the primary filegroup, but any secondary user-defined file groups are optional, at that instance, while doing the restoration. We can obtain partial data as it is available online after restoring it. We have to wait for the rest of the data to recover other filegroups.

The command RESTORE DATABASE with PARTIAL clause initiates a new piecemeal restore operation. The keyword PARTIAL points that the restore process includes a partial recovery. The partial keyword indicates and starts the partial-restore sequence. This will be validated and authorized during the restoration stages. As the database recovery has been delayed or postponed, so the position of the database remains pending.

To test the concept of piecemeal process, do follow the following steps:

1. Add another filegroup to preserve the static data. Add a table to the filegroup and insert a few records.
2. Query the table to validate or authorize the data
3. To change the filegroup state to read-only, use the “alter” database command and backup the database.
4. The database has three filegroups, one is read-only, and the other two are in read-write mode. Verify the filegroup status by executing T-SQL and backup the read-only database.
5. Drop the database to mimic the piecemeal recovery process of the database.
6. Perform the database restore operation before you start changing the session context to the master database. Now, we are going to do the read-write filegroups restore using the READ_WRITE_FILEGROUPS clause.
7. Next, restore the read-only filegroups and verify the output by querying the read-only table data.

Conclusion:

Preserving data file groups and database-recovery and healing processes to the requirements of the organization is crucial. For instance, let our SQL Server crashes then we are unable to bring this server up, now we need to recover databases on a separate server and bring them online. The database is enormous, and so recovery might take a few hours to finish. Here the Piecemeal database works. In this technique, we can recover the database on a filegroup level and can minimize the downtime outstandingly.

Introduction:

Sometimes due to the power outage or power shortage, we have to face some hardware or software issues regarding our system (computer). And when you log in again on the system afterward, you see that your most important SQL Server database data is not available. This database is so important to you that your all business accounting and financial records saved in it. One and only one solution remains when he/she has to face this kind of problem. And this solution is to reboot the system many times, sometimes it works, but the working probability of this solution is too low. And system always shows the disputable message that the database not found or corrupted. And sometimes you contact the service provider, and they said this software version is no longer supported. We will show some scenarios through which we can solve this corruption issue. After these scenarios and at the end of this article I will suggest you a software which will solve all your problems with only one to two clicks.

Power out problems

Depending on your production environment, you may have some Power out problems due to this, the server may be shut down, and the database can be corrupt. For this scenario, try to have a UPS for your server or a battery backup system.

Backups

While searching for a solution, the first solution is to have a recent backup of your most important database if you have this one then don’t worry about it. Your SQL server is smart enough to tackle this of problems. It automatically recovers your corrupted database with the help of SSIS or some scripts and saves your entire database but, in case if you have no recent backups now this time you should worry about that you are going to face a huge loss because your database has all kind of transaction and financial records of your business.

Torn Page detected

What is Torn page?

It is a setting to detect database corruption in SQL Server.

It detects failures related to I/O headers that try to access a page that has miswritten to the disk. It reports an I/O message an error (torn page) detected throughout the reading.

Afterward, while searching for a solution when you have no recent backups for your database. You can take a copy of MDF and LDF

Hex editor:

What is the hex editor?

A hex editor allows checking the manipulation of the elemental binary information that constitutes a data file. There are several hex editors on the web that are free like UltraEdit, HxD, BVI, etc.

You find that a table sys.allocation_units which was the part of your database. You can use the hex editors to verify the data corrupted and using the page number edit the data and fix it:

Proposed Solution:

Detach the repaired database from the failing environment. Copy the MDF and LDF files back to the customer’s environment. Recommended scripting the entire database with data and recreating to get it back to a safer position.

Another way to repair is by using the T-SQL commands. You can use the DBCC CHECKDB to check consistency errors in the database and also you can repair your database.

For more information about the DBCC CHECKDB commands, please refer to this link: DBCC CHECKDB (Transaction-SQL)

Alternative Solution:

As I told you at the start of this article that I will suggest you a software which will solve all these kind of problems with only one to two clicks. This software name is Stellar Repair for MS SQL. It repairs corrupt MDF and NDF files and maintains the database integrity.

This SQL Database Recovery Software provides powerful and distinctive options that will address and repair all types of corruption from the SQL database.

The software has excellent features to repair the corrupt MDF and LDF files. It also recovers the modules of databases such as the table’s keys, stored procedures, etc. The beauty of this software is that even you can recover the deleted records from your SQL database.  This software allows a user to save the repaired SQL database in different formats like MS SQL, HTML, CSV, and XLS formats. It supports more than ten versions of MS SQL.

• While launching this software, the window will look like this.

• Press Browse to select the corrupted MDF file:

• Click Repair to start the repairing process

• After completion, this message popups. Press OK:

• Now save the file.

The software will repair the database and all the information will be restored.

Conclusion:

In this article, we talked about the most common scenarios to recover a database corrupted due to a power outage. These scenarios are faced by SQL DBAs which is related to database corruption. This type of SQL recovery software can play an important role to fix database corruption. Just try it and you will have a pretty nice experience.

Working in teams is an essential part of today’s modern workplace. An increasing number of workers are remote, mobile, or work from different time zones and geographic locations. In this blog post I will share details about Microsoft 365 tools that facilitate teamwork for all workers, no matter where they are located or how they connect to your organization’s resources.

Teamwork tools in Microsoft 365

The nature of teamwork continues to evolve as teams of various shapes and sizes come together for short-term and long-term projects.  Microsoft 365 give your organization the right tools at the right time for the right task, along with common services to help you work fluidly across applications.

The teamwork products benefit from the same enterprise-level security, compliance, and manageability as the rest of Microsoft 365.

Outlook:

You must be familiar Outlookemail-based experience which help you stay in touch with colleagues, and share calendars, files, and tasks, to make sure important deliverables get attention.

SharePoint & OneDrive for Business:

You can store your content in the cloud with SharePoint and OneDrive for Business. This lets you access your files on any device and share them with others inside and outside your organization. Because the files are in the cloud, team members can collaborate on them in real time using familiar Office applications like Word, Excel, and PowerPoint.

Microsoft Teams

Microsoft Teamsis the digital hub for teamwork in Microsoft 365. It brings together team conversations and content so your users can stay up-to-date on critical projects. It includes everything teams need to stay connected like chat, phone calls, content, and meetings. It can also be customized with applications and bots that support a given project. With guest access in Teams, you can invite people from both inside and outside your organization to work on projects.

Yammer

Yammeris a community conversation tool designed to help encourage open dialogue, idea generation, and connections across the company. Yammer lets you create communities of interest and forums that bring people together, improve transparency, and give everyone a voice. You can even grant external access to partners and customers as needed.

Microsoft Graph

Microsoft 365 is built on an intelligent fabric that keeps it all connected and secured no matter what app or service you are working in as a team. Microsoft Graphprovides a seamless connection between people and relevant content.

Office 365 Groups

Office 365 Groupsenable a single team identity across apps and services and centralized policy management enhances security and compliance.