Understanding .TUF file in log shipping

I am very happy and excited to be part of Sans SQL with this being my first contribution here. So without wasting much time let us get on...

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 happens 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 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 uncommitted transactions are recorded to the undo file with only committed 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 uncommitted transactions from undo file and check with the new transaction log backup whether committed or not.

If found to be committed the transactions will be written to disk else it will be stored in undo file until it gets committed or rolled back.

So... that's it for now! Happy Reading!!

Order of Installing Service Pack on systems hosting Database Mirroring

If you have servers which hosts database mirroring and you want to install the service pack on those systems and you are trying to find out the order in which you need to do it then, here is the order in which you need to install service pack or a hotfix on the systems which hosts Database Mirroring.

1. Backup the principal
2. If you are using a witness, remove it from mirroring
3. Upgrade the mirror
4. Failover to mirror
5. Upgrade original principal/current mirror
6. If you wish to fail back to original principal continue on; otherwise, proceed to step 8
7. Failover to original principal
8. If you are using a witness, add it back into mirroring

For more information refer to the following article to know the entire process for upgrading both the principal and mirror servers.
http://technet.microsoft.com/en-us/library/bb497962(SQL.90).aspx

Error while starting SQL Server Agent in Denali (OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2).)

I had recently installed SQL Server 2012 AKA Denali CTP version on my system and when I tried to start the SQL Server agent, The agent was starting and was immediately getting stopped by displaying the below message.

When I investigated further, I found the below error message from the event viewer.

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2).

Possible Workaround:

I got this working by changing the Log on account of SQL server agent from "NT SERVICE\SQLServerAgent" to "Local System" or a domain account.

Recycle error log and SQL Server agent error log (SQLAgent.out) file

Recycle Error log:
When we run the below command, it Closes the current error log file and cycles the error log extension numbers just like a server restart.
Permission Required: sysadmin fixed server role

USE msdb 
GO
EXEC sp_cycle_errorlog
GO

Recycle SQL Server Agent error log (SQLAgent.out):
When we run the below command, it Closes the current SQL Server Agent error log file and cycles the SQL Server Agent error log extension numbers just like a server restart.
Permission Required: sysadmin fixed server role

USE msdb 
GO
EXEC dbo.sp_cycle_agent_errorlog 
GO

Three new DMV's "sys.dm_server_services", "sys.dm_server_registry" and "sys.dm_server_memory_dumps"

The DMV's "sys.dm_server_services", "sys.dm_server_registry" and "sys.dm_server_memory_dumps" are the 3 new DMV's that were introduced in SQL Server 2008 R2 and have been enhanced in SQL Server 2012 AKA Denali.


sys.dm_server_services 
This DMV gives information and status of the services SQL Server, SQL Server Agent and SQL Full-text Filter Daemon Launcher
Usage:

SELECT * FROM sys.dm_server_services

Result:

Reference:
http://msdn.microsoft.com/en-us/library/hh204542.aspx


sys.dm_server_registry
This DMV gives information about installation and configuration data that is stored in the windows registry for the current instance of SQL server.
Usage:

SELECT * FROM sys.dm_server_registry

Result:

Reference:
http://msdn.microsoft.com/en-us/library/hh204561.aspx


sys.dm_server_memory_dumps
This DMV gives information about memory dump files generated by the SQL Server database engine.
Usage:

SELECT * FROM sys.dm_server_memory_dumps

Result:

There are no dump files created yet from my Database engine.
Reference: http://technet.microsoft.com/en-us/library/hh204543.aspx