Experience… Life!
Sometimes I do ask myself… What is Life? I understand we do have the ability of defining the term ‘Life’ in our own ways or just google it to understand more technically 
For me ‘Life’ is a precious gift… gifted to us with the only intention of experiencing it to the fullest! So, Why do we waste time hating someone or be selfish? All we need to do is… be happy with whatever we have been gifted with and experience it… at the end of the day. we have only one life! precious and beautiful term referred to as ‘Life’.
So.. be good do good! 
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!
