Advertisements

Archive

Archive for the ‘SQL 2016’ Category

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.

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

SQLPASS Edmonton (Apr. 9, 2016) – Key Note Presentation

April 10, 2016 6 comments

Friday/Saturday was a great day, got to meet lots of wonderful people from US/Canada at the Alberta’s first SQL Saturday.  I delivered they Key Note speech for SQL Server 2016.  I would love to hear back from people, so please provide evaluation on SQL Saturday site and you can download the PowerPoint deck here.

 

 

Microsoft SQL Server 2016 RC2 Released

April 1, 2016 Leave a comment

In SQL Server 2016 RC 2, enhancements include:

  • R Services setup – the setup process for R Services is much more integrated into SQL Server setup. There is no longer a need to manually download and install Microsoft R open and R Server if the SQL Server is connected to the Internet; it becomes part of the SQL Server install sequence.
  • SQL Server Management Studio (SSMS) – This release of SSMS features an update to the Visual Studio 2015 shell bringing enhancements such as the quick launch toolbar and improved theming support.
  • Mobile reports – Brand Packages will now be downloaded to the mobile report publisher from a server running RC2 and available for use in report creation.  Basic mobile report content migration between servers is now supported.

https://blogs.technet.microsoft.com/dataplatforminsider/2016/04/01/sql-server-2016-release-candidate-2-now-available/?utm_content=buffer3b3f6&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer

Categories: Announcement, SQL 2016

Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group

January 13, 2016 2 comments

One of customers changed the value returned from @@SERVERNAME.  SQL Server works no problem, however an unexpected behavior appeared.  Changing the value for @@SERVERNAME, caused the backups to fail.

Looking at the maintenance jobs, found all jobs completed successfully and without issues.  However, upon looking at the database’s statics it states no backups completed.

image

Because the database in question is part of AlwaysOn Availability Group (AG); SQL Server executes sys.fn_hadr_backup_is_preferred_replica to determine if the backup should take place on the current node.  However, it returns value of 0 for all databases, if the preferred replica is set.  Because, the script makes a check that is running on the server that is preferred.  It does this by comparing the value to @@SERVERNAME to value of replica_server_name in sys.availability_replicas.  Because value will never match, it skips the database on both primary and secondary replica.

I have created a Microsoft Connect article (link); asking this little bit of information to be added to Books Online article (link).  There was a request submitted by Ola Hallengren (Blog | Twitter), which was closed as Won’t Fix (link).   Please vote!

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

%d bloggers like this: