Advertisements

Archive

Author Archive

Get to Know How to Shrink Transaction Log in SQL Server

February 27, 2019 Leave a comment

Many times the administrators ask about the transaction log size limit and how to shrink it. Even the log records are not managed properly and at last fill up the disk causing no longer changes to the database. The size of transaction log file will increase due to various reasons like long running transactions, lack of log backups and incorrect recovery model configuration. Thus, it is necessary to reduce the size of transaction log in SQL server. Now, in this blog, we are going to discuss a step-by-step procedure to shrink transaction log in SQL Server in an absolute way.

Shrink Transaction Log in SQL Server – Manual Tricks

There are following methods by which you can easily reduce the size of transaction log file. Please have a look:

  • Using SQL Management Studio

a)In the Object Explorer, connect to instance of Microsoft SQL Server Database Engine and expand that instance

b) Expand all Databases and right-click on the database that you need to shrink

c) Choose Tasks, Shrink, and then Files. Then, choose the file type and file name.

d) Alternatively, check the Release unused space check box. If you select this option that causes an unused space in file to be released to operating system and shrink files to the last allocated extent. It will reduces the size of a file without moving any important data.

e) As an alternative, choose this check box Reorganize files before releasing the unused space. If this option is selected, reduce file to value must be identified. Now, the default option is cleared. Select this option causes the unused space in file to be released to operating system and attempt to relocate rows to some unallocated pages

f) Optionally, you need to enter the free space percentage in database file after shrinking the database. A permissible value must be between 0 and 99. This feature is only present when you enabled Reorganize files before releasing unused space

g) Alternatively, choose the Empty file by moving data to other files in similar filegroup check box. Select this option that converts all data from specified file to other files within the filegroup. The empty files can be deleted. This option is same as executing DBCC SHRINKFILE command using EMPTYFILE option.

h) Press OK button

  • Using Transaction SQL (T-SQL)

If the database is available in SIMPLE recovery model, thus you can run the following statement to reduce the size of log files:

You must have to replace TestDB_log with logical name of a transaction log file you want shrunk and change 1 to number of MB you wish the transaction log file shrunk to.

If the SQL database is available in FULL recovery model you can set it to SIMPLE, perform DBCC SHRINKFILE cmdlet, and go back to FULL if you do not care about losing data in the transaction log.

You can search the logical name of transaction log file by using a given query:

Another option to reduce the log file size with FULL recovery model is to backup the transaction log for database using BACKUP LOG statement and then the main issue SHRINKFILE to shrink the transaction log in SQL Server:

Time to Conclude

Nowadays, most of the people are aware about how to shrink transaction log in SQL server e., Office 365 Backup. Sometimes, there is an unexpected delay, the log truncation will occur after the checkpoint or after a log backup automatically. Therefore, in this blog, we have explained the manual solution that will help to reduce the size of transaction logs. Make sure you follow each step very carefully.

Advertisements
Categories: T-SQL Reference

Ways of Managing SQL Server Database Fragmentation

February 5, 2019 Leave a comment

If you are an SQL admin or user, you must be familiar with the term” fragmentation.” In every SQL Server, indexes get fragmented as the database usage is increased. Fragmentation within a limit is harmless, but as the fragmentation percentage increases, it affects the database efficiency. Keeping this situation in mind, this post will discuss methods of managing SQL Server database fragmentation. We will also talk about other matters related to SQL Server database fragmentation.

What is Fragmentation and Its Categories

SQL database indexes help your database to perform properly. Whenever an SQL Server is used for a query, view, or data retrieval, indexes are being used one way or another. Especially in case of lengthy database operations, support from properly managed indexes is mandatory for correct and fast result. Along with the database usage growth, page split keeps occurring and at the same time those page indexes keep fragmenting. That is how fragmentation occurs in SQL Server. There are two types of index fragmentation: internal and external. External fragmentation means the logical page order is not similar to physical page order. Internal fragmentation happens when there is too much white space on the pages.

How Fragmentation Affects SQL Server Performance

Fragmentation forces database to go through multiple index entries to process a query. As a result, it takes more time to use SQL Server to execute any query. The situation gets worse when you need to fetch data from a read-only table quickly, but in case of dynamic table without any page split it does not matter much.

What is the Desired Level of SQL Server Database Fragmentation
So, we can see that the effect of index fragmentation depends on index usage and category of table that is being processed. For this reason, there is no fixed SQL Server database index fragmentation value that can be considered universally standard. Users need to consider their specific requirement and manage database fragmentation.

Learn to Manage SQL Server Database Fragmentation

This process of fragmentation is impossible to avoid, so the only option left is to manage index fragmentation properly. Considering the importance of this process, most of the SQL experts consider it as a regular task. In this segment, we will learn how to manage the fragmentation in SQL Database.

Detecting Fragmentation
For adopting any measure for fragmentation management, the level of fragmentation has to be determined. In order to analyze fragmentation, sys.dm_db_index_physical_stats” DMS is commonly used. This function will get you a result set containing the fragmentation level. Some important columns of the result set are:

  • avg_page_space_used_in_percent: This column shows how much database space is used by the pages. The value is shown in percentage and indicates internal index fragmentation.
  • avg_fragmentation_in_percent: In this column, external index fragmentation percentage can be viewed. For Heap Tables and Tables with Clustered Indexes, this column shows different values.
  • fragment_count: This column displays continuous data fragments in the index.

Managing SQL Server Index Fragmentation

If you want to keep the index fragmentation rate in control, make sure to apply these measures for your SQL Server database.

  • Apply index fill factors settings
  • Avoid adding records using random key values
  • Select cluster key according to the Insert pattern of the table
  • Stop making records longer by updating them
  • Avoid updating key columns of the index

How to Apply Fill Factor Settings

The most useful technique to manage the Index fragmentation is to set Fill Factor settings. In this parameter, users can set the data page fulfillment percentage reaching which only, an index will be created. The value has to be entered between 1 to 100 where 100 means an index will be made when a page is 100% full. Remember that Fill Factor is only used during the creation, rebuilding, or reorganization of an index. If you want to set instance Fill Factor you can use sp_configure, but it is not recommended.

What If My Index Fragment Rate is High

As mentioned earlier, there is no fixed standard for index fragment rate. But there are some common practices among the SQL users if their index fragmentation percentage reaches a certain level. If the SQL database fragmentation rate is 0 to 5-10% you do not have to do anything. If it is between 5-10% to 30%, you have to REORGANIZE your indexes. If the rate crosses 30%, i.e., it is within 30% to 100%, people usually REBUILD the indexes.

How to Reorganize Indexes

Using ALTER INDEX REORGANIZE command, users can reorganize their indexes and keep the index fragmentation in control. All indexes can be reorganized at a go by mentioning All during the command execution. When this command is running, the Tables can be used simultaneously. Irrespective of the Recovery Model, this operation gets completely logged in Transaction Log. Depending on the fragmentation rate, it can take more space in the Log.

How to Rebuild Indexes

When the database is highly fragmented, ALTER INDEX REBUILD command is used. It can be used to rebuild all the database indexes. During its execution, users cannot use the database. Based on the database recovery model, this gets included in the Log. In full recovery model, the operation takes a lot of space to get completed.

Conclusion

The article thoroughly discussed various approaches of managing SQL Server Database fragmentation. By reading the entire write-up, users will get a clear idea about the concept of SQL Server index fragmentation and they can secure their database from its adverse effect.

Categories: SQL 2016

Quick Methods to Restore Database from MDF File Only

January 29, 2019 Leave a comment

“Recently, my system has encountered a severe breakdown. I lost most of my files and applications attached to it. Luckily, the MDF file of the attached SQL Server could have been recovered. Now is the time for a fresh start and I am in need to restore database from MDF file only. I heard there are different methods to attach MDF file to the database. Can anyone tell me which one is the easiest method to restore from MDF file to the SQL database?”

Like this user, many people put forth their query regarding MDF file restoration to the SQL Server Database. MDF files are the primary data file that stores all the database items of a SQL Server. Due to some technical issues of SQL database, users often need to restore database from MDF file only. To help users in such situations, we will share some approaches of MDF file attachment to the database. If you have the similar query about MDF file restoration, read the full blog.

What is MDF File: The full form of MDF is Master Database File. Of all the files created in SQL Server database, it is the most significant one. It contains all the items of the database. It plays a vital role in the smooth functioning of the database and also used for database restoration in case of any SQL Server issue. If the MDF files get corrupt, it will cause great inconvenience to the users. That is why, SQL users are always advised to have a regular backup of MDF file. Besides, they should always perform best practices to protect MDF files from corruption.

How to Restore Database from MDF File Only?

If you are facing the need of database restoration from MDF file, you can choose any of the methods from this segment.

  • Using SQL Server Management Studio
  1. Go to the list of all applications in your system and click on Microsoft SQL Server.
  2. Choose SQL Server Management Studio (SSMS) and a dialog box will appear.
  3. Select the correct Server. Also, choose Windows Authentication as the mode of authentication and click on Connect button.
  4. SSMS will open and you have to right-click on Databases node. Then click on Attach option from context menu.
  5. Attach Database window will open and there, you need to click on Add button.
  6. Navigate to the MDF file location, select the MDF file and click on OK.
  7. MDF file will get added in the main windows and you need to click on OK button on this page too.
  8. The file will get restored in your database. You can check the database to find out.

Note: After MDF file attachment is done, SQL Server will create an LDF file.

  • Using TSQL
  1. Use credentials to access SQL Server database engine.
  2. Go to New Query Window and run the below script.

Remember: Before performing this method, make sure to store the MDF file in the above specified location: “C:\Program Files\Microsoft SQL Server\…”

Looking For A Simpler Solution? Try SQL Recovery

In case you are not willing to restore database from MDF file only using the manual techniques, you can do that with MDF File Repair Tool. This application will let the users restore MDF file to same database or a different database. The biggest benefit of using this software is its ability to fix corruption issues in case the MDF file is damaged in some way. The application is suitable for SQL Server 2000 and all the above versions including SQL Server 2017.

Conclusion

In most cases of corruption and technical issues, users often do not have any other option than to delete the database. After you delete your troubled database, you can also restore database from MDF file only to get back the database as earlier. By following the manual methods mentioned here, users can easily attach their MDF file to the SQL database. For faster and smoother MDF file restoration, users can choose SQL Recovery Tool. The application can perform the database restoration without any data loss and fix MDF file corruption, if any.

Categories: SQL 2014, SQL 2016, SQL Errors

Learn How to Find Who Deleted Records in SQL Server

January 14, 2019 Leave a comment

Query 1: “Hello friends, I am writing for the first time hoping that some of the learned friends can help me sort out the problem. Some of the tables of my SQL database are accessible to other users too. Yesterday, I noticed that certain rows got missing from the database table. How to find who deleted those rows from that table? Please help me with some genuine method.”

Query 2: “I received a query from a database user who wants to know “how to find who deleted records in SQL Server”. Here, I am talking about the Express edition of database. As per my assumption, this data can be found in the LDF file (if it has not been truncated). If I am correct, tell me how to find out this particular information from LDF file.”

If you want to know how to find out who deleted records from SQL Server, you may find these queries familiar. Hundreds of users like these are looking for ways to find out the culprit who deleted SQL data that caused them serious problem. If you are one of them, hold on, as we will tell you the most impressive method to locate who deleted SQL Server records.

How to Find Who Deleted Records in SQL Server

The record of the user who deleted some data from SQL Server can be found in the Transaction Log file. If you want to know, you need to retrieve that information from LDF file of your SQL database. For this, you will require SQL Transaction Log Viewer that can open and read LDF file data easily. Here is how you can read Log file data using this software.

a. Install and start SQL Log Analyzer Tool of your Windows system. Click on Open to add your log file.


b. A dialog box will appear that will showcase two different tabs for Online Database and Offline Database. Here we will show you the procedure of reading Log file form Online database. Select Online DB Options tab and then choose the Server name from the list. Also, select the Authentication mode and Select Database from the list. Click OK when done.


c. The software will scan the LDF file and prompt when it is finished. Click OK.


d. It will display the summary of LDF file including counts of records, inserts, updates, and deletes. Again, click OK.


e. Now, you will see the complete LDF file data on the software screen. Any record can be previewed.
f. Users can sort the LDF file entries based on Transaction, Login Name, Time, table Name, and Transaction Name.


g. Just click on any table entry to select and preview the corresponding log entry of that operation. If you want to know who deleted a record, click on Delete transaction of that record. You will be able to see the Login Name of the person who deleted that data.
h. If you wish to export these data, check the boxes beside the necessary tables and click on Export.


i. Another dialog box will appear where you can apply record type filter, date filter, Export To/As, Database Credentials for Source database, and Destination Database.


j. Click on Export button when all the fields are filled properly.
k. The tool will export the selected data and upon finishing, prompt the users. Click OK.
l. The software will also display a report which you can save as CSV file for reference.

Note: Besides analyzing Log file data, this software can also retrieve deleted data if the database is in simple recovery mode.

Conclusion

SQL database often contains essential information and deletion of such data causes great inconvenience. Hence, we answered the question how to find who deleted records in SQL Server in this post. Using SQL Log Analyzer, users can open LDF file and find out who performed any particular transaction like Insert, Update, and Delete.

Quick Things to Do When DBCC CHECKDB Display Corruption Message

December 3, 2018 1 comment

Have you ever wondered that you are working on Microsoft SQL Server and database shows some abnormal file crash or error issue? And, when you execute the DBCC CHECKDB command, it reports corruption in your SQL Server database.

Definitely, this scenario is a nightmare and the saddest part, it is frequently faced by many SQL database administrators.

Now, the biggest question is ‘What to do when DBCC CHECKDB reports corruption in database?’

So, The long wait is finally over!

Here, we are going to tell you what step should be taken when the SQL database gets corrupted. Thus, one can secure its confidential and important database in any data disaster situation. But, before understanding the method, a user must know about DBCC CHECKDB and which type of corruption it is reported?

Without wasting any minute, Let’s begin!

Take a Quick Look on SQL Server DBCC CHECKDB

DBCC CHECKDB is a type of T-SQL command whose prime function is to monitor the logical as well as physical integrity of all object in a selected database. DBCC CHECKDB command is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, Azure SQL Database, and SQL Server 2019. Furthermore, it comprises three main commands in it for different purposes.

DBCC CHECKALLOC – Its function is to verify the organization of disk space allocation or page usage for a selected SQL database. Besides this, it also checks various internal structures to maintain the record of allocation of all pages in the database as well as relationship between them too.

DBCC CHECKTABLE – This Transact-SQL command is used to check the logical and physical consistency of the pages and structure that create table or system table indexes.

DBCC CHECKCATALOG – This command is used to perform various catalog consistency between system metadata tables. For the execution of this T-SQL command, the database must be in online mode.

The above-described command runs as a part of DBCC CHECKDB. If the user executes the DBCC CHECKDB command, then there is no requirement to use all these commands separately. In case, when the database DBCC check reports any error, just take the help of following solutions in order to save the important database table.

Methods Use to Repair Corrupted Database in SQL Server

In the following section, three different workarounds will be described to restore data objects from a damaged or corrupted SQL database. A user can go through all these methods and select that is appropriate.

Approach #1: Use REPAIR_ALLOW_DATA_LOSS Command
When the DBCC CHECKDB reports corruption error, then you can use Repair command on the specified database. It is devised to fix all the reported error. However, the biggest disadvantage of this method is it leads to data loss. Even, when the user uses this command to extract the database from last clean backup, it results in more data loss. In fact, Microsoft does not recommend this method suitable to recover data from the good backup. It is marked as an emergency option that can be used when the data cannot be recovered from the last backup file.

Approach #2: Restore Database from Backup
If the last good backup of database is available, then it is better to recover the database from it with the help of RESTORE DATABASE command. With the help of Restore statement, one can perform multiple operations.

  • Retrieve only a part of SQL database.
  • Recover specific pages of a database.
  • Extract complete database from full backup.
  • Capable to get back a transaction log onto a database.
  • Provide an option to restore only desired file or filegroups.

In case, when the user does not have a last clean backup of the database, then the Restore command does not work.

Approach #3: Use Expert Solution SQL Database Recovery
The above two solutions have some consequences, so it is better to show your trust in a reliable solution because data loss cannot be ignored. That is why many SQL administrators uses MDF Repair Tool when DBCC CHECKDB shows any data corruption error. This utility has an advanced scanning technique that scans the highly corrupted primary (MDF) or secondary (NDF) file. And, recover the data object such as triggers, tables from it.

The great part of this software is user can export the recovered file in SQL Server Database or SQL Server Compatible SQL Scripts. Moreover, SQL Database Recovery Tool supports to repair corrupted Database of version 2017, 2016, 2014, 2012, 2008, 2008 R2, 2005, 2000.

Final Note

It is a head-scratching situation when the SQL database got corrupted and no relevant solution is not in the hand. So, after considering this painful situation, in this article, we have discussed about DBCC CHECKDB that reports error in the database. Along with this, three different solutions have illustrated to recover data from corrupted SQL database.

Categories: Disaster Recovery

How to Fix Microsoft SQL Restore Database Error 3154

November 23, 2018 Leave a comment

“Yesterday I made backup of my SQL database and tried to restore my database. But, unfortunately my database has been deleted, then, I tried to re-create a new database with common names. I thought this will work, but this message “The backup set holds a backup of a database other than the existing database error 3154” encountered. Now, I am stuck in a very difficult situation and unable to figure out what to do next. I do not even know how to fix SQL database error 3154. Kindly suggest me a feasible approach to resolve it.”

Are you getting SQL Restore database error 3154? Needs an appropriate solution to fix it? Do not worry, you are landed at the right page. In this blog, we are going to explain different methods that will help you to resolve SQL error 3154. Before moving towards the solution, let us understand all possible reasons that are responsible for SQL database error 3154.

SQL Restore Database Error 3154 – Reason

When we analyze the root cause of an error, it becomes easy that the name of database they want to restore the backup set and database of backup is same. Although the difference is CREATE DATABSE statement between two databases. This simply means that the similarity is of name, but the database is completely different from each other. Follow the possible reasons given below:

  • For log shipping, more than one database was configured.
  • Transaction logs for databases were backed up to similar folder.
  • The only difference between both database names is “_tlog”. For example, xyz_test and xyz_test_tlog.

Fix Restore Database is Terminating Abnormally. (Microsoft SQL Server, Error: 3154)

Go through the following methods that will help to resolve SQL Database error 3154:

Approach 1: Using T-SQL command

You can resolve this SQL error with the help of T-SQL command:

a) Use WITH REPLACE while using RESTORE command.

b) Delete old database which is conflicting and again restore with the help of RESTORE command.

c) You just take an Example :

Approach 2: Using SQL Server Management Studio (SSMS)

If you want to do same with SSMS, follow the steps given below:

  • Create the database with your preferred name
  • Then, right click on database and select Tasks option
  • In the next step, navigate to Restore and choose Database option
  • Under the restore screen, select “Overwrite the existing database (WITH REPLACE)” checkbox
  • Click OK button. It should restore the database successfully.

Note: When you restore the database WITH REPLACE it will overwrite an existing database.

Fix SQL Database Error 3154 – Feasible Solution

Most people face challenges while implementing the manual method. Thus, to overcome all the problems faced by non-technical users while executing the manual steps, it is recommended to opt for an instant solution. One such application is SQL Backup Recovery Tool. With this utility, users can repair corrupt SQL Server bak file without any risk or data loss. The tool is capable enough to repair both primary (.mdf) and secondary (.ndf) SQL database. It is having user-friendly interface, with some useful features.

Time to Conclude

SQL Server is used by several users across the globe to manage and store their crucial data. While restoring the SQL database, the user may encounter an error message “The backup set holds a backup of a database other than the existing database Error 3154”. Therefore, in this blog, we have discussed the step-by-step process to fix SQL Restore Database Error 3154. But, at times, it becomes very difficult for a user to implement the manual steps. Thus, users can make use of a commercial software that is SQL backup Recovery to repair a corrupt bak file.

Categories: SQL Errors

How to Recover SQL Database Without Log File: An Ultimate Guide

November 1, 2018 Leave a comment

“Recently I was trying to recover a database that contains MDF and LDF data files. All of a sudden, I got .mdf file from a standard backup tape. However, sp_detach_db was not run on database before the backup of MDF file, so I do not have an LDF file. I know the stored procedure sp_attach_single_file_db can recreate a log file in most cases, and I have tried it to reattach the database, but I receive the following error. If anyone know the solution on how to recover SQL database without log file, please help me out.”

Are you also getting the same error message? Searching for an instant or reliable method to fix it? Do not worry, you are landed on the perfect page. There are multiple users who are facing this problem. Therefore, after understanding the above scenario we have come up with a manual solution. Before that, let us discuss all possible reasons for repairing SQL database without log file.

Reasons Behind Recovery of SQL Database Without LDF file

Go through the following reasons due to which users need to recover database .mdf file without .ldf. Some of them are listed below:

  • Most of the people do not know that log (.ldf) file contains precious transaction data and it is required.
  • There are cases where log file too bit and users have never cared for its size and content. When they want to transfer database to another server they only want the .mdf file to be moved to a new instance.
  • The log file gets corrupted due to hardware failure.

Ways to Recover SQL Database Without Log File

There are different methods through which uses can recover SQL database without using transaction log file. Follow the set of instructions that are listed below:

Way #1: Using T-SQL Method

You can run sp_detach_db on the database to reattach a database with sp_attach_db or sp_attach_single_file_db. Using sp_detach_db ensures the transactional consistency within a database and retains the data integrity. However, if the data integrity is not required or no data has been changed, you will be able to use undocumented Database Consistency Checker (DBCC) REBUILD_LOG cmdlt that Listing 1 shows to attach a database. REBUILD_LOG will re-create the another log file and reattach a particular database even if a valid log file does not exist. But, the data might not eventually consistent because you could have thrown away active and uncommitted transactions. Use this script only for emergency recovery when you need to move data to another database.

Way #2: Using SQL Server Management Studio (SSMS)

Be sure that the following steps will work only if the database has been clean shutdown and primary file (.mdf) is available. Please have a look:

  • Under Object Explorer window, right click on Database and go to Attach option

  • On the Attach Databases, a dialog box appears and click on Add

  • The dialog box Locate Database Files appears, browse the location where MDF file is located, hit on file to select and then press OK button to exit. A new log file is created by SQL Server while repairing the database. Now, the database will appear in Databases

  • Now, back to the Attach Database box. In the database details, you will observe that SQL Server is unable to find log files (.ldf).
  • To attach the MDF file without LDF, choose the transaction log file and after that, click on Remove

Expert Solution to Repair SQL Database File Without LDF File

As we look at the different methods to recover SQL database without log file. It is more clear that all these manual ways have their respective drawbacks. These methods do not provide the satisfactory results. Thus, we have come with a remarkable tool named as SQL Database Repair Tool. It is the best solution that helps to repair both primary (.mdf) and secondary (.ndf) SQL database files. In addition, it is capable enough to restore deleted SQL database table’s without any hassle. It has simple, easy-to-use and user-friendly interface which makes easy for users to accomplish the task effectively.

Final Thoughts

While working with Microsoft SQL Server, the situation arises in which database got corrupted. Now, it is necessary to recover all the corrupt database files in SQL Server. Thus, in this blog, we have discussed both manual and professional solutions to let users understand how to recover SQL database without log file. Also, we have covered the most important features of the tool in an absolute way.

Categories: Disaster Recovery

Get Rid of SQL Server Error 8992 Instantly

Recently while resolving users queries , I got to know one error which users are facing and face problem in fixing it. See query:

“I ran DBCC CHECKDB over the database and got following error:
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=xxxxxxx) of row (object_id=xxxxxx,column_id=11) in sys.columns does not have a matching row (object_id=xxxxxxx) in sys.objects.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. ”

Please help us out in resolving the issue without affecting the users.

Users facing SQL error 8992 need not to be worry. Here I am going to resolve SQL Server Error 8992. But first, Let us understand what issues you will be facing while you are getting SQL error msg 8992.

Why SQL Server error 8992 occur?

The reason for getting SQL error code 8992 could be any of the following:

  • You might be facing inconsistency in your system metadata when you update your SQL Server database.
  • Or maybe you might get this error when you update the system tables in SQL Server and run DBCC CHECKDB or DBCC CHECKCATALOG command.

Reason of Getting SQL error 8992

The error code 8992 occurs when SQL Server does not support the manual updates to system tables. Remember it must be updated only by the SQL database engine.

The error comes when DBCC CHECKDB can’t repair metadata corruptions.

Resolving SQL Server Error 8992

You have various options to repair SQL error 8992. You can choose it accordingly to your situation and get it resolved.

  1. So, if you have clean backup which is free from any inconsistencies, you can restore it from the backup. Here are the steps:
    • Click on the name of the Database you want to restore.
    • Right click on the Database, Click Restore Database.
    • Check ‘From Device’ option. Browse the location of the .bak file.And select the Type of Backup Media as File.
    • Select the database you want to restore and Check the Restore option.
    • In the Options pane, Select Overwrite the existing database(WITH REPLACE) under Restore options.
    • And click RESTORE WITH NORECOVERY under Recovery state section.
    • Click Ok. You have successfully restore your data from the clean backup.
  2. But what if you dont have backup , for that case you can export the data to new database. After that migrate all the content of the updated database to new database.
    Well you might be thinking, what about inconsistencies found in the system catalogues? The answer is you cannot repair the inconsistencies in DBCC CHECKDB by using REPAIR options. The repair command is the minimum level of corruption and it does not guarantee repairing your corruption.

Now let us imagine the worst case, you don’t have backup, and you have inconsistency in your database. So what will you do now??

Fix SQL Server error 8992 without any Data loss

So if you are in the worst case, the best way is to go for an professional approach ie SQL Repair Tool. It will remove and repair any type of inconsistencies found in the MDF / NDF File. You can try FREE demo version of this software. It supports MDF File version of 2017, 2016 & all its below version.

Conclusion

If you are looking to repair SQL Server error 8992, you are on a right place. The blog discusses the reason of getting this SQL code 8992 and every possible solution to fix SQL Server error 8992.

Categories: SQL 2016

Truth Behind SQL Server Transaction Log Misconception

Logs plays an important role in SQL Server Database. Transaction log records all transaction activities done in SQL Server database. After every modification in transaction, a log record is written to the transaction log file.

Here we will be discussing on various transaction log myths which various DBAs and users think that they are true. Let us discuss misconception around transaction logs.

#Myth1- Transaction Log truncation make the log file smaller.

Reality – No, Truncation does not reduce the size of physical log file. At the time of truncation, only the active portion is scanned. Some parts are marked as inactive and they are used as free space to write down new transaction. There is no change done in the size of transaction log as the parts which are inactive remain intact and nothing is removed or deleted.

#Myth2- No need to take log backup for disaster recovery, if you are taking full backup daily.

Reality – This is wrong! Full backup does not mark log file as reusable. It depends on the amount of data you can afford to loose. If you don’t worry to loose your transaction log data, then you should use Simple recovery model. Regular taking transaction log backup does not allow you to afford lost transaction log data.

#Myth3- SQL Server is too busy. I think, I don’t need to take backup of SQL Server Transaction log

Reality – This is completely untrue! If your SQL Server is too busy, you should take more frequent backups. If you are not taking regular SQL transaction log backup, the transaction log will become full resulting in growth of transaction logs. Busier the SQL Server, more frequent you should take log backups. Taking regular log backup does not block transaction log, but result in an Auto growth event.

#Myth4 – If I perform full backup, I Don’t need to perform log backup for point-in-time restore.

Reality- This is one of the common misconception every user is believing. The reason for this myth is RESTORE command used with STOPAT clause. STOPAT clause specifies point in time for RESTORE LOG command. This command works well when it is used with log backups. Hence it can also be used with full database backup, it is clear that transaction log backup are not needed to recover at specific point in time.

#Myth5 – Shrinking frees space in SQL transaction log so taking transaction log backup is not necessary.

Reality – Shrinking operation is not a good practice. It does not resolve the log size issue. The transaction log will grow again, after performing initial shrinking operation.Auto-growth event must be avoided. You can maintain the size of transaction log by performing regular log backup. Or if you afford data loss, you can set recovery Model as Simple Recovery Model.

Want to read Transaction Logs Quickly?

You can read transaction log in SQL Server by using SQL Server Transaction log reader. It previews all the log activities like Transaction , Time, table name , query. The software fetch & preview records from Live database and it works both as online and offline SQL database environment.

Conclusion:

Transaction logs plays crucial role in SQL Server Database. The blog covers misconception around transaction logs in SQL Server. It also discusses a quick way to read transaction log activities in SQL Server.

Categories: SQL 2014, SQL 2016

Simple Way to Find Transaction Log Activities in SQL Server

“I am using SQL Server 2005. When I was doing modification to my database, I found that some of the data has already been modified. I want to find out the person who did changes to my database. Kindly help me in finding how to check transaction log in sql server 2005. I want to know the name of the person who did the changes along with the transaction date and time.”

There are many people who got stuck when comes to finding a transaction in a log for a particular user. In this case, SQL log file help user to examine all the transaction activity done in the SQL Server. In this blog we will discuss how to find transaction log in SQL Server.

Understanding Transaction Log in SQL Server

SQL Server database consists of transaction log that records all transaction activity like transaction time, transaction name, table name in LDF file. It also records each and every database modifications made by person. So, when a person did any modification in the database, then it becomes easy to identify the person who did changes in a log file. Reading logs in SQL Server is not an easy task, so here we will be discussing an easy & quick way to find out the transaction in SQL Server.

Quick way to Check Transaction log in SQL Server

Many business decision-makers face problem in connection with database due to many reasons such as sudden system shutdown, delay in troubleshooting, control audits or changes in employees. In such case, sometimes, they need to recover accidentally deleted data, track unwanted changes done in the database and find out the name of the user that has changed the data. To keep in mind all the things, one easy & quick solution to track down the transaction in SQL Server is by using SQL transaction log reader. It analyze all the transaction details like transaction name, transaction time, table name, query in SQL Server.The tool also offers many advanced features like fetch and view SQL database records from the live database, read & analyze all transactions like insert, delete, update, etc.

How to Check Transaction Log in SQL Server

You can easily find out the transaction for a particular user from LDF file with the help of SQL Log Analyzer tool. However, it works in both online and offline environment.

  1. Install & open SQL Log Analyzer tool.
  2. Then, click on the Open button to add .ldf file.
  3. Now, you will get two options:
    • Online DB Option
    • Offline DB Option

    If you have selected Online database option, then you have to provide all server details.

  4. After that, the software will preview all transaction activities. From here, you will get the transaction log for a particular user.
  5. Conclusion

    In this write up, we have discussed an easy solution on how to read transaction logs in SQL Server. One can analyze and read all the transaction activities done in the logs by using this professional third party utility.

%d bloggers like this: