“Hello all. I am writing this post because I am in big trouble. I recently joined an organization and unfortunately, I forgot the password of SQL database. Not a great situation for a new employee, so can you help me out? Can anyone tell me how do I reset SQL sa password? I will be really grateful if you could suggest any easy solution.”

Are you also suffering from a similar problem as mentioned in the query? Do you also want to know how to reset SQL sa password ? Then this is the right blog for you. Read on.

SQL Server database administrators often find themselves in an awkward position when they forget or lose the database password. This problem can happen to anyone at any given time. If you find yourself in the same situation, do not worry. This post will elaborately discuss various methods that can be implemented to fix this problem.

3 Quick Methods to Reset SQL sa Password

If you have lost your SQL database password, do not jump into the decision of reinstalling the SQL Server. Keep patience and read the solutions stated in this section. Here we will learn two different methods to reset SQL SA password for SQL database.

Method 1: Use Management Studio to Reset SQL SA Password

If you have lost the SA password, you can easily reset it using the management studio. After that, you will regain the access to SA account by Windows Authentication mode.

• Login to SQL Server. For this, use Windows Authentication.
• Navigate to Object Explorer to expand Security folder. Now, expand Logins folder and right-click on SA account. From the options, click on Properties.
• When Properties window opens, add new password and confirm it. Click on OK to set this as your new SA password.

Method 2: Use SQL Script to Reset SQL SA Password
In case you reset SQL database password, users can also try using SQL scripts to add a new password to the database.

• First of all, you have to launch SQL Server Management Studio.
• Open a new query in it.
• Enter the scripts mentioned below for execution:

  GO
  ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
  GO
  USE [master]
  GO
  ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE
  GO

Note: Here, NewPassword will be the password you want to use for your SA account in place of the lost password.

Method 3: Use SQL Password Recovery Tool

If you find these above-mentioned methods complex, or if you are not willing to perform those processes, we have a better option for you. Presenting SysTools SQL sa Password Reset Tool. This affordable yet effective tool will help you set a new password for SQL database whose password you have lost.

It is really simple to get back the access to your SA account using this utility. Launch the tool and add the MDF file in it. All of its users will get displayed on the screen. You will the password value of SA account is Unknown. Select SA and click on Reset Password. Add a new password and confirm it. Now you are all set to access the SA account using this password.

Attention: We strongly recommend having the backup of MDF file before proceeding with this method.

Conclusion

Forgetting the SQL database SA account password can cause immense trouble for the admins and many people in real life do suffer from this problem. That is why we see a lot of people asking the same question in forums, how to reset SQL database password. For them, we have described three simple solutions to change their password. If the manual methods are not working or seems lengthy, users can go for the tool mentioned here. This software is one of the most popular software

For every Database admin, ensuring the smooth performance of the SQL Server is a headache. They need to perform various tasks and tricks for a productive and fast SQL database. One such common task is to keep the Index fragmentation in check. While this is a challenge for the DBAs, Index Fragmentation can be controlled by reorganizing and rebuilding.

Rebuilding Index in SQL Server is the method often used when the fragmentation level goes higher. If you want to improve the performance of SQL database, you have to rebuild the index. But the question is when and how to perform this task. This write-up will focus on the best practices in rebuilding index of SQL Server. We will also discuss when is the right time to conduct this task easily.

Rebuilding Index in SQL Database – Know Why and When to Perform?

It is known to the SQL Server users that database performance gets significantly hampered if the SQL database becomes full of fragmented indexes. As Index Fragmentation keeps increasing along with the database usage, admins should be careful about the fragmentation rate. Depending on the database size, DBAs should fix a schedule when the index fragmentation will be checked using “sys.dm_db_index_physical_stats” command. When this command is run, users can learn about the percentage of index fragmentation in SQL database.

If the percentage is as low as 10%, no additional action is needed. If the level is between 10%- 30%, you have to rebuild the index to enhance its performance. Only when it crosses the 30% bar, the question of rebuilding index in SQL Server comes to the scene. However, some SQL Server experts recommend performing Index rebuilding only when the fragmentation rate reaches 80% or 90%. Since rebuilding index is a resource-consuming task, database admins should consider how much the fragmentation affects the database performance before rebuilding the index.

Best Practices in Rebuilding Index in SQL Server

If you are interested in rebuilding indexes, it is better to follow certain basic rules, known as the best practices in Index rebuilding. For example, if you are using any SQL Server edition other than the Enterprise Edition, then this task should be done offline. Since the feature of Online Index Rebuilding got introduced in SQL Server 2005 Enterprise Edition, any earlier version users need to perform it offline. With Online index rebuilding, the index never goes offline and table also remains available for use during the process.

In case of SQL Enterprise Editions that support online index rebuild, the online process takes more time than offline rebuilding. That is why, offline index rebuilding is highly recommended if the company can afford downtime. To minimize the downtime, it should be done when minimum people are using the database or it should be done along with scheduled maintenance tasks. Therefore, nighttime is perfect to schedule index rebuilding. It is also suggested to conduct this task at least once a week. If you do not have any maintenance window for your database, you can try doing Online rebuilding.

Rebuilding Index in SQL Server consumes a lot of resources. So performing it too regularly will create inconvenience to the database with scarce resources. Database admins should consider their database capacity and resources before scheduling the index rebuilding.

Note:  Get to know How to Deal with Index Corruption in SQL Server

Concluding Thoughts

Index fragmentation is a common situation in all SQL databases. The productivity of the SQL Server depends on the level of Index fragmentation. Only a controlled Index fragmentation rate is desirable for smooth functioning of SQL Server. Among many approaches that keep the fragmentation level in control, rebuilding Index is a popular one.
In this process, logical index fragmentation is removed, statistics get updated and database page space is emptied. Therefore, users should include this in their maintenance scheduling window. They can also learn about the best practices in rebuilding index in SQL Server from this post. Also, consider the situation of your own SQL database to customize the rebuilding process.

The SQL Transaction Log reader is a standalone utility, which helps to view SQL server transaction log files without any difficulty. It is capable enough to preview LDF activity, including Transaction, Time, Name, Query and Table Name.

Moreover, it allows a user to fetch and display all records from the live database. This application offers an option to filter and export as SQL database, as SQL script or CSV file format. It supports to analyze one or more NDF data files without losing a single bit of data. Also, it is compatible with all latest Windows versions, including Windows 10 and its below versions.

SQL Transaction Log reader Tool: Demo vs Licensed

The SQL Log viewer is available in two different versions i.e., Demo and Licensed Version. Let us have a look:

• Demo Version: The demo version of the software is freely available for download and supports to preview of SQL Server Transactions (INSERT, UPDATE, DELETE), preview Login users that has made changes on database tables, transaction Query, Time. But, doesn’t allow to export Transaction details for recovery purpose.
• Licensed Version: The licensed version of SQL Transaction Log reader tool permit users to preview all the records within the database. Moreover, it can open and read the complete log activity in SQL server database. Also, it allow you to export all tables data along with deleted records direct in SQL Server Database or SQL Server Compatible script or CSV format.

The Demo version of this SQL Log Recovery available for download in SysTools Group Official Website

What is New Added in SQL Log Reader Tool to Explore LDF

1. Allows to View Multiple LDF Files: SQL LDF File Viewer permits you to open and read multiple transaction log files activity in single go. For this, you just need to add parent .ldf file of your database, if you are scanning with offline option. The software will scan and preview transaction activities of all other .ldf database files of that database.
2. Analyze Same record Update
   Multiple Times and then Delete: SQL log file reader allow you to preview the transactional activity of same record (Updated multiple times and then Delete). This will allow users to track all the database changes for a specific record.
3. Auto Fetch SQL Server Name: While scanning the transaction logs of a specific database with Online DB option,The SQL transaction log reader tool will allow you to click on drop-down button to auto fetch the Server Name. In case, the software doesn’t shows the desired Server Name, then you can enter it manually.

Existing Features of SQL Log Viewer

1. Preview All Transaction Records: SQL Server log file viewer scans and load all available tables transactional activities, present in SQL database and creates a preview. It will help to view SQL transaction log file with fields such as Transaction, transaction time, Table name and query. Users can view all records with Login Name Authentication for Insert, Delete, Update etc.
2. Option to Sort LDF File Elements: The sorting option is available within SQL Log viewer to re-arrange the order of the listed items. Moreover, users can sort each element according to their properties such as transaction, transaction name, transaction time, table name, and query.
3. Scan with Online or Offline Options: The SQL log file viewer offers two different scanning options to read transactional activities of your database. The Online option asks for SQL Server information like: Server Name, Authentication Type. In Offline option, the software will allow you to browse .ldf file along with its associated .mdf file and preview the transaction log activities.
4. Export Selective Tables: While moving the SQL LDF file queries, you can check or uncheck all tables for saving only selected data. If you want to restore SQL Log file data from the desired table, then, select and export the desired tables direct to SQL Server Database or SQL Server compatible script or CSV format.

Technical Description of SQL Server LDF Reader Tool

Final Verdict

After considering the overall functionality of the software, we can summarize that SQL Transaction Log Reader is a reliable tool that allows a user to read and view multiple .ldf files. The software is capable enough to view SQL transaction log with fields such as Transaction, transaction time, Table name and query. Regardless, we can say that the SQL log Viewer is a robust software due to its great features.

SQL database is a popular and useful relational database, but it is not free from technical flaws. SQL users often face various corruption related issues in SQL Server database. These issues occur due to the damage or corruption in the database of SQL Server. As these are common issues, SQL users must have clear concept regarding these issues. We will be discussing some major corruption related issues of SQL database and their symptoms. Users will also learn the remedial methods of these issues from this post. Let’s begin our discussion with the SQL Server errors and their symptoms.

Issues Related to SQL Server Corruption

Due to damaged or corrupt SQL Server database, users have to encounter different issues. We will discuss three major issues here.

SQL Fatal Error 823:

This error is caused by database corruption or some discrepancies of file system. It indicates that underlying hardware or driver situated n the path of I/O request is having some problem. This problem will generate some error messages that will display these information:

• Operating system’s error code and error description
• The file name that was executed against I/O operation
• If I/O operation request is written or read

SQL Error 5172

This error is caused by unhealthy MDF files. It occurs due to the mismatched header file information, so accessing data becomes really tough. In such cases, recovering damaged SQL database is very important. Here are the major causes of error 5172:

• Sudden shutdown of system
• Hardware malfunctioning
• Improper shut down of SQL Server
• Virus or Malware attack

3. SQL Error 8946: If a particular assigned page does not contain valid page header, error 8946 can appear. If the header gets damaged, the entire page faces corruption. This results in data loss and inaccessible file. Power failure, hardware problems, virus attack, etc. are responsible for this particular error.

How to Resolve Corruption Related Issues in SQL Server Database

If you are facing one of the above corruption based issues in your SQL database, it means the database has been corrupt or damaged. The only way to resolve these issues is to free the SQL database from all types of corruption. To repair the corrupt database of SQL Server, users can adopt any of the two different methods. One is called manual method and another is called one-stop method.

Method 1: Manually Fix SQL Database Corruption

If users want to fix their corrupt SQL database manually, they can try these different methods. First of all, they can restore updated backup of SQL Server. They can also try database console commands to repair database. DBCC Repair commands that can fix minor corruption of SQL database.

Limitations of Manual Technique

These manual methods have got some limitations too. The biggest limitation is that none of these methods guarantee to recover SQL Server database. Moreover, all these methods take a lot of time to perform. Users need to be technically expert to execute these steps. Inexperienced people needs to be careful about their database, when performing these methods. Also, major corruptions cannot be fixed by these methods.

Method 2: Fix Corrupt SQL Server Database using Sure-Shot Solution

It is clear from above discussion that manual methods cannot fix damaged database for sure. To repair corrupt database, users have to apply the one-stop solution. Using third-party tools is called sure-shot solution as they can fix all types of database corruption. SysTools SQL Database Repair is such a tool that can remove both major and minor SQL Server database corruption. It can fix corruption of SQL Server 2016 & all previous versions. This program is also capable of recovering data from MDF files affected by Wallet Ransomware.

Conclusion

If you are facing any of these corruption related issues in SQL Server database, be sure that SQL Server has been damaged. Users need to fix the SQL Server in order to get rid of these issues. we have mentioned some methods to make your SQL database corruption free. Users can try manual methods, but there is no guarantee that the damaged database will be fixed for sure. One can also try the third party tool to recover corrupted database. It can easily fix the damage of SQL Server and all related issues will get solved.

Whenever a user adds, delete, or edit any record in the SQL Server all the changes are maintained in the transaction log. In addition, the background process keeps on writing the each and every transaction in the log and to the database. After that, the transaction log is marked as written. There are two types of recovery model i.e. Simple and Full Recovery Model is used by the database in the SQL Server. However, if the database uses full recovery model all the written transaction logs are maintained in the log. Moreover, it becomes mandatory for the user to manage the transaction log file and take backup on the daily basis. In addition, the transaction log files are big enough if the full recovery model is used for the database. Apart from this, the Transaction logs in the SQL server are the always auto-growth and it is also advised not to turn off the auto-growth feature. Because it is helpful in the case of emergencies.

More About the SQL Server Transaction Log Size

The Transaction Log files in the SQL Server database is made up of one or more physical files and SQL Server writes to one physical transaction log file at a time. The internal structure of the physical files that is used by the SQL Server for transaction log files is known as Virtual Log Files (VLFs). However, the number and size of VLFs files inside the transaction log files directly depend on the number of factors. The size of VLFs files is determined at the time when the transaction log file is created or extended.

Effect of Large Transaction Log in SQL Server Performance

SQL Server transaction log file is comprised of small parts known as virtual log files whose size is not fixed. However, the main motive is to keep the small number of the virtual log files for the transaction log file. It is because SQL Server manages the smaller number of files more easily.

• If there are a huge number of virtual log files then, there can be two possible reasons behind this. One is the small transaction log that has grown up (manually or automatically) in very small segments and another is a problematic situation where the large growth segments were configured but accidentally small VLFs were configured in the Transaction log. However, if the Virtual Log Files grows unnecessarily large due to auto growth, the logs become fragmented and may results in delay also. Moreover, it also slows down the recovery process that is why having so many or very little virtual log files results in bad performance.
• Apart from all this, the auto-growth option is also offered, which is turned on by default. If the auto-growth settings are not handled in an appropriate manner, a SQL Server database will be forced to auto-grow that may lead to the serious performance issues. It is because the SQL Server will halt all processing until the auto-grow event is completed. However, the auto-growth event will take lots of space due to the physical organization of the hard drive that is not close physically to the previous one occupied by the transaction log file. This results in the physical fragmentation of the files that also causes slower response.
• It is always suggested to backup the transaction logs regularly. However, if the backup process fails, then the log files will grow largely and left with over-sized transaction file. It is because old transaction logs are not removed which makes transaction logs increase at a rapid rate. If the SQL Server database is having a large transactions logs then, it has a bad effect on the performance of the SQL Server such as:

1. If the transaction log file is full in the SQL Server database, it degrades the performance of the SQL Server.
2. It also slows down the speed of the transactional log backup process.
3. In addition, the over-sized transaction logs decrease the disk space also because old transaction logs are not removed yet.

How to Resolve Over-sized Transnational Log Problem?

The number of VLFs files is increased by an auto-grow event, that has a common process but requires strict rules to prevent the unplanned issues with space or unresponsiveness. Therefore, to reduce the bad effect of over-sized transaction log files on the performance of the SQL Server, it is necessary to resolve the issue.

The most common solution is that reduce the number of virtual log files in the transaction logs files. Now, to do the same follow the three simple steps discussed below:

1. Backup the Transaction Log files.
2. After that, shrink the transaction log files.

It is because the number of virtual log files in the transaction logs is reduced by shrinking the SQL Server transaction log file that needs strict rules also to avoid deleting the data that has not been backed up till now.

Conclusion

Transaction Log files are the most important log files in any SQL Server database, which grows always because the auto-growth option is turn on by default. The internal structure of the transaction log file has many virtual log files. However, if there are an excessive number of VLFs then, it has some bad effects on the performance of SQL Server. Therefore, it is necessary to properly control the auto-growth feature of the transaction log files.