Understanding .TUF file in log shipping
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!!
Deadlock – Implicit Conversion
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
Perform Counter Poster
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.
MSSQL Server & Interoperability
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.
Data Modeling
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!
MS SQL Server CODE NAMES – Updated
I was wondering “where and how does Microsoft get all the code names”… Off course I did some googling and found one interesting web link shared by like minded DBA: SQL Server code names are named after National Parks!! Wow… Amazing!
SQL Server Code Names – Reference
MS SQL Server CODE NAMES
I understand I have been off for quite sometime now. I don’t have any particular reason except Work!
All said and done… let me start sharing some of the learnings over time…
I am sure that many of us are aware of the different code names used for different versions of SQL Server… let’s have a quick recap… the latest being the ‘DENALI’
For all the previous versions:
http://en.wikipedia.org/wiki/List_of_Microsoft_codenames#SQL_Server_family
Thats it for now!
Some more time!
Friends,
Its been quite a while; since I continued with my two topics “SQL Server IO” and “Replication Architecture”… the reason is obvious… have been really busy with many changes happening around! Will be back in as early as possible. Hope we don’t lose track on what we started!
SQL Server IO – 2
Now that we understand what is “Transaction” and “ACID properties” of transaction… lets get moving on the next set…Write Ahead Log (WAL) :O
What!?… How is WAL related to ACID properties or transaction? Yes, they are related.
WAL is technique which helps to adhere to two of the four ACID properties “Atomicity” and “Durability”. Now that we understand what is atomicity and durability… next is to understand “How WAL works” and also we need to peep into the advantages we get from it.
Let me just put my words as per SQL Books online which is very much easier to understand:
Write-ahead log (WAL) guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.
To understand how the write-ahead log works, it is important for us to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification needs to be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.
At the time when modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.
I hope the above is very much clear in its explanation…
This is it for now…
