SQL Server Error 8942 – Know The Reasons and Solution of This Problem

Summary: In this article, I am going to tell you the reasons behind the problem SQL server error 8942 also we will discuss the step by step procedure to resolve this issue. But for this, the user has to read this article carefully. So without consuming much time lets start.

Microsoft SQL Server is a highly featured rich and result oriented application. Most of the organization use this software to store and handle important business data in the form of tables. This application stores the data in MDF ( primary database file), PDF ( Secondary database file) and LDF log file. But sometimes due to lack of knowledge and improper handling of SQL Server the SQL users have to face an unknown error SQL Server Error 8942.

Server: Msg 8942, Level 16, State 1 Table error: Object ID O_ID, index ID I_ID, page P_ID. Test (TEST) failed. Slot S_ID, offset 0xADDRESS overlaps with the prior row.

So in the forthcoming article, We are going to discuss the possible reasons for this problem and also we will discuss the procedure to resolve this issue easily.

Possible Reason Behind SQL Server Error 8942

Here the common causes of this error. Some of which are described below

1. In case of Overlapping of Slots – The offset of the next slot must be greater than the previous slot. If the offset slot S_ID is not greater or equal than the previous slot then the user has to face this error.

2. Hardware Malfunction– Sometimes due to hardware malfunction can also lead to SQL Server Error 8942 error.

3. Improper Shutdown of SQL Server – Improperly or forceful closure of the SQL Server will also lead to this problem.

As we have seen the reason for the above problem the user can try the DBCC CHECKDB command without using the repair clause. But in case if you won’t be able to resolve this error then the user can take the help of an Expert solution to resolve this problem. 

Note: The user can also read another post to Restore SQL database

Automated Method to Recover SQL Database If You Are Facing SQL Server Error 8942

The user can try the manual solution to tackle this problem. But this includes the high possibility of data loss situation. So, in that case, the user can try the SQL database recovery software to resolve this issue. This can easily repair the corrupted and damaged SQL recovery software. This is an efficient recovery tool that can easily recover SQL tables, stored procedures, indexes, functions, triggers, etc. For complete details and Working of this software, the user can try the free version of SQL database recovery software to recover SQL database

download

 

Steps to Recover Crucial SQL Database

1. Launch the SQL Database Recovery Software from your system

2. Now select the Corrupted MDF file from your system.

3. Choose the Scan mode from Quick Scan and Advanced Scan. The user can click on preview deleted SQL Records in red color.

4. Click on the OK button. The tool will show the complete preview of SQL database objects.

5. Now Click on the Export button and select the Server name and authentication type.

6. The user can select the database or the user can export to the Existing database.

7. Select the database items you want to recover.

8. Select the option from With schema and data.

9. Now click on the Export button to start the process.

Final Words

In this article, we have discussed the step by step process to SQL Server Error 8942. We have given the reasons for the above error. The manual process has so many limitations so in that case, the user can try the SQL Recovery Software to tackle this problem.

Posted in Sql Server, T-SQL | Tagged , | Leave a comment

Perform SQL Server Deleted Data Recovery in a Simplified Way

Many times SQL server database users have to face disastrous situations. Sometimes users perform delete operation in the database without having any backup of the data. Due to this misoperation, the database administrators have to face lots of issues.
If you have also deleted the data accidentally and you do not have the recent backup available or your backup file is corrupted or unreadable. Then in this blog, we will discuss how to perform SQL server deleted data recovery easily.

As we know that the user may have to face data loss situation at any time due to several known and unknown reasons. And also sometimes many tables and other components of the database turns into the unreadable state. Let us understand the problem with the help of query asked by the SQL users in the forum site.

Hi to all! One of My Colleague has accidentally deleted 1000 records from a table named Y (id int, name varchar (20), salary int). Now is there have any way to perform SQL server deleted data recovery easily. I do not have the recent backup of the database. Please help me to get back my data.”

Here are The Requirements to Perform SQL Server Deleted Data Recovery

1. Make sure SQL Server is installed on your machine
2. SQL Server Management Studio is installed.
3. SysTools SQL Database Recovery tool is Required to recover deleted records from SQL database.

Perform SQL Server Deleted Data Recovery by Using Trusted Solution 

To recover deleted records from SQL database the user can take the help of trusted and reliable solution SysTools SQL Database Recovery tool. This is advanced software which can easily recover all the database objects such as tables, stored procedure, functions, etc. This software can easily repair the corrupt MDF and NDF file data. Moreover, this utility comes with easy to use user interface so that everyone can easily use this software with ease. Also this software is compatible with SQL server 2017, 2016, 2014, 2012, 2008 / 2008R2, 2005, 2000 database files.

Follow the Steps to Perform SQL Server Deleted Data Recovery Process

1. Download and Install the Software on your machine.
2. Now click on open and browse the MDF file from its location.
3. After that select the Scan Mode from the Quick Scan and Advanced Scan.
4. Select the SQL server version and if you don’t know the version then choose auto-detect SQL server file.
5. In the database, authentication selects the server name and the authentication mode.
6. Select the destination process by creating a new database or exporting to the existing database.
7. Now you have to click on the checkbox to export the desired components of the database.
8. choose the export option from with only schema and schema and data.
9. Finally, click on Export option to start the export process.

Note: Please make sure to check the checkbox export deleted records option.

Also: The user can another similar post how to recover deleted records in SQL server

Summing Up

In this article, we have discussed how to perform SQL server deleted data recovery. We have resolved the problem faced by the users how to recover deleted records from SQL table by using SQL recovery software. This software has easy to use, simple user interface so that the user can easily use this software to recover deleted database objects.

 

 

Posted in Sql Server, T-SQL | Tagged | Leave a comment

SQL DBA Interview Q & A on Stretch Database

Introduction to Stretch Database in Azure

There are different databases and services are available in Microsoft Azure.  Here, I am going to focus on stretch database/feature which is available from SQL Server 2016 onward.

Different types of databases & services in Azure

  1. Azure Cache for Redshift
  2. Azure Cosmos DB
  3. Azure Data Explorer
  4. Azure Database for Maria DB
  5. Azure Database for MySQL
  6. Azure SQL Database
  7. Azure database for PostgreSQL
  8. Data Factory
  9. SQL Data Warehouse
  10. SQL Server Stretch Database
  11. Table Storage

SQL Server Stretch Database:

This feature allows us to stretch our database to Azure for warm and cold transactional data. We can provide longer retention times. It saves our cost around 40% less expensive than adding more enterprise storage.

Stretch database bills compute and storage separately. Compute usage is represented with Database Stretch Unit (DSU), customer can scale up and down the level of performance/DSUs they need at any time.

Here, Database sizes are limited to 240 TB.

Stretch Database

This picture shows how to enable the Stretch feature and use it to save our cold data to Azure.

Interview Q &A

1.What editions of SQL Server are compatible with Stretch Database?

Stretch Database is a feature of SQL Server 2016. It is available on all editions of SQL Server 2016.

2.Can I change the performance level of my Stretch Database?

You can adjust the performance level of your Stretch Database. Changing performance levels takes a matter of minutes and your databases are online and available during the change.

3.What is a DSU (Database Stretch Unit)?

A SQL Stretch Database Stretch Unit (DSU) represents the power of the query and is quantified by workload objectives: how fast rows are written, read and computed against. This measure helps customers assess the relative amount of performance needed for their workload.

4.How much data can I stretch to Azure?

You can stretch up to 240 TB of data with Stretch Database.

5.How can I un-stretch my database?

You can move your data back to the on-premises environment at any time. If you decide to unsubscribe from Stretch Database, you must move data back on premises via un-stretching or export to a storage service in order to retain access to the data.

Note: Egress charges apply for data moving out of Azure. For more information on un-stretch or exporting data, please see the SQL Server 2016 Stretch Database documentation.

6.How much storage will my databases consume?

You will consume approximately the same amount of storage in Stretch Database as you currently do in SQL Server as the data originally stored in SQL Server is moved to Stretch Database.

7.Can I stretch to another SQL Server instead of Stretch Database?

Stretch Database currently does not support stretching to another SQL Server. You cannot stretch a SQL Server database to another SQL Server database.

8.If I have an existing Stretch database from SQL Server 2016 RC0 or earlier, can I just upgrade RC1 or later and run on the new Stretch Database service?

Direct upgrade is not supported. If you need to retain data already stretched to Azure, you will need to first un-migrate the data from Azure to your SQL Server database. Upon completion, you can upgrade and re-stretch to the new Stretch Database service. Please note that un-migrating data from Azure will incur data egress charges. If you are on RC1 or later, you may upgrade to a newer version using any existing upgrade methods supported by SQL Server.

9.How can I stretch to a SQL Stretch database?

You will need to first un-migrate the data from Azure back to your SQL Server database. Upon completion, you can upgrade and re-stretch to the new Stretch Database service. Please note that un-migrating data from Azure will incur data egress charges.

Posted in Azure, Database Administration, Q & A, Sql Server New Feature, windows azure | Tagged , , , , , , , , , | Leave a comment

Microsoft Azure Sentinel

Microsoft Azure Sentinel

Today’s demand is how to protect the organization and its data from the cyber threat because data is most important things for any organization. As companies are moving to Cloud therefore some cloud based technology is needed to work on that. To keep it as an important area, Microsoft has announced two cloud-based technologies named as:

  1. Microsoft Azure Sentinel
  2. Microsoft Threat Experts

Azure Sentinel enables us to protect our entire organization by letting us see and stop threats before they cause harm. As Sentinel is built on Azure so we can take advantage of limitless cloud speed and scale and invest our time in security.

Azure sentinel supports open standards such as common event format and broad partner connections, including Microsoft Intelligent Security Association partners such as Check Point, Cisco, F5, Fortinet, Palo Alto Networks and Symantec etc. Azure Sentinel is available in preview today from the Azure portal.

Microsoft Sentinel

As this is a new technology and it is in preview in Azure therefore to learn more please explore to Microsoft website.

https://news.microsoft.com/en-in/

Posted in Azure, Microsoft News, Other, windows azure | Tagged , , , , , , , , , | Leave a comment

Restore SQL Server Database from Multiple Backup Files: Learn the Best Practices

SQL Server can come across errors at any time. To tackle any severe situation, it is always standard practice to backup SQL database. The backup of primary and secondary database files, as well as Transaction log files, are saved as .bak files. In case the database encounters some irreparable errors or there are some hardware issues, users have no other option but to restore SQL database backup files to the Server.

Backup restoration is not a tough task to perform if you have proper knowledge about the process. But the thing gets critical when one has to restore multiple backup files. Even SQL DBAs and other experts cannot restore multiple backup files to SQL Server database easily. To help them, we will describe some of the most used methods of restore SQL Server database from multiple backup files. Depending on the requirement, they need to pick the right solution. But first, we will check out related user queries.

User Query 1: “SQL Server is not permitting me to restore multiple .bak files in a new server when I attempt to restore all at once. When I try to add multiple backup files to the backup software, it accepts only the first one from the list. Before I try any manual technique, looking for your opinion whether this is feasible or not.”

User Query 2: “Hello all. I am looking for an approach to restore all the SQL backup databases on the SQL server. The main issue here is that I have a large number of backup files and I want to restore those all in one go. Please tell me is it even possible or not? If Yes, then tell me an easy solution.”

How to Restore SQL Server Database from Multiple Backup Files

When it comes to restoring multiple BAK files to a SQL Server, users can take the help of manual as well as automated method. In this section, we will check out two manual techniques for multiple backup file restoration and then we will learn about a tool that can also conduct the restoration process.

Restore Multiple SQL BAK Files SQL Server Management Studio Manually

With the help of SSMS, it is possible to restore multiple backup files. But first, split backup files need to get created. Here is how to do this:

  • Open Object Explorer and expand Databases
  • Select the database you need to backup and right-click on it. From the list of options, choose Tasks >&ft; Back up…
  • On the next window that appears, select the General tab. Choose the source database and type of backup. Click on Add button to determine multiple backup paths.
  • To add each path, either type the path manually, or Browse to the folder where the backup file will be saved. When done, click on OK. Repeat the process for all the destinations.
  • When all the paths get added and displayed on the window, click OK to begin backup process. You will get a message when it is done.

Now comes the process of restoring these split backup files:

  • Go to the Object Explorer of SQL Server and right click on Databases on the left panel. Choose Restore Database…. Option.
  • In the General tab of the next window, enable the Device radio button. Click on the Browse button to select all the backup files.
  • Click on Add button from the Select backup devices window to add all the backup file locations.
  • Either type the full path address or navigate to the backup file through the folder tree structure. Repeat this for all the locations. When the listing is done, click OK.
  • All the paths will get loaded and then destination database name along with restoration type will be displayed. Finally, click OK to finish the restoration process.

Manually Restore Multiple SQL Server Database BAK File Using T-SQL

These TSQL commands can be used to restore multiple backup files to SQL Server database:

Restore One Full Backup File and One Transaction Log Backup

Here, the first command will restore the database and leave it in a restoring state. The second command, however, restores the log file backup and put the database in useable condition.

restore

Restore Full Backup and Two Transaction Log Backups

These scripts will restore the first two backups (one full backup and one log backup) with NORECOVERY and then for the last log file with recovery.

restore bak

Restore Full Backup, Latest Differential and Two Transaction Log Backups

In order to restore three initial backups with NORECOVERY and the last one RECOVERY, use the script.

restore bak file

Restore SQL Server Database from Multiple Backup Files Using Application

If you are looking for an easy technique to restore more that one SQL BAK file at once, you can choose SQL Backup Recovery. This application supports restoration of any number of backup files to all the latest SQL Server versions including 2017, 2016, and 2014. This tool also has to file size limitation for BAK files. Users can export any number of MDF, NDF and LDF file backup using this tool.

Conclusion

If you are facing the need to restore SQL Server database from multiple backup files, you can follow the methods provided here. To perform the manual methods, you can take the help of step-by-step instruction shared here. Else, you can use the cited software for smooth and hassle-free restoration.

Posted in Sql Server, T-SQL | Leave a comment

Check Out How to Open LDF File in SQL Server

The data of SQL database can be broadly categorized into two primary file types. One of MDF file and another is LDF file. MDF file is the master database file that contains the basic data and LDF file (log database file) stores all the transaction logs that take place in the database. Sometimes, users need to know how the transactions are being recorded in the log file. At that point, they need to know how to open LDF file in SQL Server. If you are also interested in opening SQL log files, you can do that using the native technique. Here, we will tell you how to open SQL LDF files easily.

How to Open LDF file in SQL Server

In order to read LDF file by opening it, users need to use a function named “fn_dblog.” This function offers data that shows what is happening in your log file when you perform a transaction. This must be tested in the lab-environment at first. To run this (fn_dblog) function, a beginning log sequence number (LSN) and ending LSN of a transaction is essential. For this function, NULL is the default that will return all LDF file records.

Creating a Sample Database
Here, we will create a sample database and a table to check the functionality of LDF file by running this SQL code.

The recently created sample database is named “ReadingDBLog” and its Table location contains three columns. Now is the time to check how the processes of database and table creation performed by SQL Server have been registered in LOG file. For this, we need to run this command:

The result shows that 339 rows have been made for creating an empty database and a blank table. Try this function to learn about the processes used for database and table creation.

Open LDF File to Check How DML Commands are Captured
Now, we will check how DML commands like insertion, updating or deletion get logged in LDF file. The process of page allocation or de-allocation can also be tracked by this operation.

We will check the LDF file again and this time, we will filter it because the database log file contains a huge amount of data.

After running this script, you will get output similar to this image that shows INSERT.

Similar kind of info will get displayed in case of UPDATE and DELETE.

How to Open Log File to Find Internal Operations of SQL Server

Certain internal functions of SQL including page splitting can be checked by this function. All the relevant information about page splits including number of occurrence of page splitting, on which operation and page did it occur. Run this SQL scripts to view all page splits related operations for the above INSERT operation.

We can see here that Page Split happened three time for the INSERT activity as the first script allows us to know. The second script explains all the internal SQL processes done during the first Split Page. If users perform any other tasks, it will get logged in the LDF file and this function will display it.

In Case of Backup

As the final task, we performed a backup and opened the log file to see what is written there:

After opening the log file we can see that the number of rows has been decreased at an alarming rate after the backup. The reduction of rows from 528 to 9 means the section of log file that recorded those transactions have been moved to the backup file. At the same time, original log file entries have been removed. If users want they can shrink LDF file if needed.

Conclusion

In this post, we have elaborated the manual approach check the LDF file data in SQL Server. If you want to know how to open LDF file in SQL Server, you can follow the guideline provided in this post. Users can also try SQL Log Analyzer for a simpler and easier solution to open and view LDF file.

Posted in Sql Server | Leave a comment

Top Free SQL Server Tools & Performance Tuning options from Microsoft

Top Free SQL Server Tools & Performance Tuning options from Microsoft

(A Benefit to the company)

microsoft-tools-training

I am writing this blog to share the importance of the tools provided by Microsoft along with SQL Server to use to monitor the health of the databases, performance related issues and troubleshoot the same. Microsoft has provided several tools and other options for such scenarios related to SQL Server Databases and these are absolutely free therefore it is better to use these tools instead of procuring third party tools. By using these free tools we can reduce the cost which cast to the company. However there are several free third party tools available in the market but these are not secure and not recommended as per the company policy as well.

Therefore how to prevent these issues and run the SQL environment secure and smooth, it is recommended to use free tools provided by Microsoft.

Free Tool from Microsoft

  1. Profiler
  2. Perfmon (Performance Monitor)
  3. Activity Monitor
  4. Extended Events
  5. DTA (Database Tuning Advisor)

Other Options

  1. DMV (Dynamic Management Views)
  2. Trace Flags
  3. DBCC (Database Consistency Check)

Profiler:

Profiler is a user interface tool which can be used to monitor overall health of your database and instance by tracing inside the SQL Server. It gather the complete information about the event which we have selected to monitor e.g. deadlock info etc. and give us the result in form of a trace file which we can use and check thoroughly and find the cause of the issue.

Path: Run — type Profiler — Profiler window will open

Perfmon:

Performance monitor or Perfmon is another great tool which can be used to set the counters and see the result graphically. This tool provides the overall picture of the server health which includes the counters information as Processor, Memory,Buffer etc.

Path:  Run — type Perfmon — Perfmon windows will open

Activity Monitor:

This tool is available in the SQL Server Management Studio and can be used to monitor the processes, recent expensive queries, resource wait etc. It displays the information graphically as well. It is easy and very much handy tool to find the quick information about the system along with the processes running.

activity monitor

Path: Open SSMS (SQL Server Management Studio) —connect to the instance —Object Explorer — Activity Monitor

activity monitor -ssms

Extended Events:

Extended events is a more useful tool which can be used to monitor server in below cases:

  1. Application Tuning
  2. Troubleshooting Performance
  3. T-SQL Query Performance Analysis

There are predefined template available in SQL Server Management Studio which can be used while configuring the extended events while we can also design a custom event. Mainly available template focus on Locks & Blocks, Query Execution & System Monitoring. Always On monitoring can also be done through it.

We can configure the Extended event, captures the event which we want and troubleshoot the problems.

extended events

DTA (Database Tuning Advisor)

It is another tool which is used to tune the query and advise us about the specific requirement of the indexes. We can find this tool in Tools menu options in SQL Server Management Studio and we can open it in search programs in window.

DTA can be useful in such cases:

  1. Tuning Queries
  2. Adding indexes (clustered, non-clustered and indexed views)
  3. Adding partitioning
  4. Adding statistics

Don’t Do:

Do not start DTA when SQL Server is running in single user mode.

Trace Flags

Trace flags plays an important roles in database administration. These are frequently used to diagnose performance issues or to debug stored procedures. As I have already written a blog on this so please go through the link

https://mirzahusain.wordpress.com/2017/12/31/trace-flags-in-sql-server/

DMV (Dynamic Management Views)

DMVs are used to fetch the server state information and which can be used to monitor the health of the instance, diagnose the problem and tune the performance.

There are several types of DMVs available.

E.g. OS Level, Index Level etc.

DBCC (Database Consistency Checker)

DBCC is used to check the consistency of the databases. These commands are used for maintenance of databases, tables, file group and indexes.

E.g.

DBCC sqlperf (logspace)

DBCC checkdb

DBCC loginfo ()

Note: Hope you have got the valuable information, I have just provided a brief about the tools however if you want detailed description please go through Microsoft blogs.

Please Share it

Posted in Database Administration, Other, Sql Server | Tagged , , , , , , , , , , , | Leave a comment