Advertisements

Archive

Archive for the ‘SQL 2016’ Category

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.

Advertisements
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.

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.

SQL Server 2016 Launch Event in Edmonton SQLPASS

May 24, 2016 1 comment

SQL Server 2016 release date is June 1st; to help get the moment started, Edmonton SQL Pass is holding a launch event on June 25, 2016.  Keep your calendar clear and come on down.  Not a lot of seats open.  Register here!

I am going, see Edmonton SQL Geeks there :).

 

SQL Server 2016 Release Date

May 3, 2016 Leave a comment

SQL Server 2016 GDR has been set to release on June 1, 2016 (Ref).

SQL 2016 is going to be a major blast in how SQL Server is used for OLTP, OLAP and how it integrates with Microsoft Azure.  If you are not already playing with it, take time to install it and explore the gems hidden in multitude of new functionality.

 

Categories: Announcement, SQL 2016 Tags:

SQL Server 2014 & 2016 Developer Edition

April 26, 2016 Leave a comment

Microsoft SQL Server has been in developer edition as far back as 2005 (if my memory serves me); however each license was $50.00.  Now with SQL Server 2014 & 2016, Microsoft has made developer edition free.

There is no better time to get on board with SQL Server 2016, there are lots of new functionality and features available.  That can help your organization reach new levels of performance, scalability, and data insight.

Download and start playing with SQL 2014 here.

 

SQL Server 2016 RC3 Released

April 19, 2016 2 comments

SQL Server 2016 RC3 has been released, it is the final release before RTM.  In this release no major functionality is introduced.

Download from here.

Categories: Announcement, SQL 2016 Tags:
%d bloggers like this: