Advertisements

Archive

Archive for the ‘SQL 2016’ Category

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.

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

SQL Server 2016 CTP Released

May 28, 2015 11 comments

Microsoft SQL Server 2016 CTP is released, you can download it form here.  It looks like a promising release, there are lots of positives being introduced.  Sure to keep SQL Junkies busy for a while, to list the few functionality I am looking forward to (or rather my clients):

*Copied from Release Blog Announcement (reference)*

Always Encrypted

Data security is top of mind, especially for mission critical applications, and SQL Server has been the enterprise database with the fewest security vulnerabilities six years running.*  To help customers with data security and compliance when using SQL Server on-premises or in the cloud, we are introducing Always Encrypted. Always Encrypted, based on technology from Microsoft Research, protects data at rest and in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and best of all, the encryption key resides with the application in the customers trusted environment. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications.

Stretch Database

Today, in the Ignite keynote, we showcased how you can gain the benefits of hyper-scale cloud in the box with new hybrid scenarios including Stretch Database. As core transactional tables grow in size, you may need to archive historical data to lower cost and to maintain fast performance. This unique technology allows you to dynamically stretch your warm and cold transactional data to Microsoft Azure, so your operational data is always at hand, no matter the size, and you benefit from the low cost of using Microsoft Azure.  You can use Always Encrypted with Stretch Database to extend your data in a more secure manner for greater peace of mind.

Additional capabilities in SQL Server 2016 include:

  • Additional security enhancements for Row-level Security and Dynamic Data Masking to round out our security investments with Always Encrypted.
  • Improvements to AlwaysOn for more robust availability and disaster recovery with multiple synchronous replicas and secondary load balancing.

Really there are more functionality, but these turned some heads.  The stretch database seems like a really nice idea.  However I think it will be difficult to implement, as most of the time, when I ask what is archiving strategy for data; I get blank stairs.  So it will be interesting to see how this can be implemented with existing systems, or rather how much effort will be required with updating existing system.

I am going to play with it soon, every SQL Junkie should too :D.

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