What is .TUF file? What is the significance of the same? Any implications if the file is deleted?
.TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.
When the database is in Standby mode the database recovery is done when the log is restored; and this mode also creates a file on destination server with .TUF extension which is the transaction undo file.
This file contains information on all the modifications performed at the time backup is taken.
The file plays a important role in Standby mode… the reason being very obvious while restoring the log backup all uncommited transactions are recorded to the undo file with only commited transactions written to disk which enables the users to read the database. So when we restore next transaction log backup; SQL server will fetch all the uncommited transactions from undo file and check with the new transaction log backup whether commited or not.
If found to be commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.
So… thats it for now! Happy Reading!!
Like this:
Be the first to like this post.
Do we see deadlocks because of ‘Implicit Conversions’?
Let me start defining deadlocks and then the reasons for deadlocks –
Deadlock – refers to a specific condition when two or more processes are waiting for the other to release a resource, or more than two processes are waiting for resources in a circular chain.
There can be number of reasons for deadlocks – such one reason can be ‘Implicit Conversions’
What is Implicit Conversion?
A system where in some tables has columns defined as a particular data type and a procedure is written with the wrong data type with reference to the same column. Say for example that our table actually has ID defined as an Int, but the developer knowingly or unknowingly wrote the procedure as if the ID column was a VarChar(20) data type.
So, now every time SQL Server has to look for ID it has to convert @ID from Varchar to INT. This is an implicit conversion of the data type.
How does implicit conversion cause performance bottleneck leading to deadlock?
SQL server internally uses a function ‘CONVERT_IMPLICIT’ to do conversion and when the conversion happens the INDEXES are not used effectively due to the uncertainty involved in the result of the function – which means it has to convert the value for each and every row. This results in SQL Server scanning the entire table looking for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating records while the scan is taking place.
This hold on the entire set of rows in a table might lead to a deadlock when explicit DML operations are required to be performed on the same set of rows.
Solution?
To make sure to have the correct Data type defined in all definitions.
The following execution plan depicts the usage of Implicit Conversion function
Like this:
Be the first to like this post.
As DBAs we are for sure to work on performance tuning. Performance Tuning requires proper planning with all the needed tools and execution to nail the TRUE culprit.
To make our life simpler Quest has released a poster which has comprehensive list of perform counters. I would say this should be part of the DBA tool kit.
Download Perfmon Counter Poster here
Like this:
Be the first to like this post.
I am sure we all know that MS SQL server is specific to MS tool set only!… till this happened 
Here is the change… we all have been waiting for.
MS has provided SQL Server ODBC driver for LINUX (Red Hat) – allows native application developers (C/C++) running on Linux to connect to SQL Server.
For more reading –
Announcement by Microsoft on the release and FAQ’s surrounding the same.
SQL Server ODBC Driver for Linux
FAQ
Like this:
Be the first to like this post.
Friends,
Another year has passed by and no blogs or updates from me! why… I should ask myself!? no answer 
I have started exploring some Data Modeling tools from the last few days… and came across a web link which lists many of the tools widely used. The tools are detailed out with prices and also the purpose they serve.
We can refer to the link Data Modeling Tools
I will do my best to be active.
Happy Reading!
Like this:
Be the first to like this post.
