Backup on SQL Server 2008 R2 database fails if you enable change tracking on database

BACKUP DATABASE [DB_Name] TO  DISK = N'D:\MSSQL\DB_Name.bak'
WITH STATS = 10
GO

Executing the query "BACKUP DATABASE [DB_Name] TO  DISK = N'D:\MSSQL\DB_Name.bak' failed with the following error: "Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'.
Failed to flush the commit table to disk in dbid 5 due to error 2601. Check the errorlog for more information.
BACKUP DATABASE is terminating abnormally.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This error occuring due to change tracking enabled on the database and Microsoft confirmed this a bug in SQL 2008 R2 RTM version. Need to apply latest service pack to fix this issue.

Ref - http://support.microsoft.com/kb/2522893 , http://support.microsoft.com/kb/2603910

Database Mirroring status is disconnected or synchronizing but no logs moving

Problem::
Database Mirroring status is disconnected or synchronizing but no logs moving.

Steps to Resolve::

Resume the mirroring.
Note :: Trying to resume mirroring from remote would not work all the times, So connect directly and resume it.

ALTER DATABASE <Database Name> SET PARTNER RESUME;

If no changes in mirroring state try to pause(suspend) and resume it.

ALTER DATABASE <Database Name> SET PARTNER SUSPEND;

ALTER DATABASE <Database Name> SET PARTNER RESUME;

If it fails with below error.
Msg 1447, Level 16, State 21, Line 1
ALTER DATABASE "Database Name" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.
(OR)
Msg 1404, Level 16, State 11, Line 1
The command failed because the database mirror is busy. Reissue the command later.

Try to restart the endpoints(This should fix the issue)
use master
GO
alter endpoint endpoint_name state = stopped;
GO
alter endpoint endpoint_name state = started;
GO

If nothing works, restart the Mirror server SQL services. After restart if mirroring is in suspended state, resume it.

SQL Server Resource Database

Resource database is a system database which is not accessible during normal operations.It is a read only database that contains all system objects used by an SQL server.It is logically presented as system schema in every database. It contains the structure and description of all system objects and no user data.

This database helps to roll back the service pack installation very easily. All you have to do is before SP installation just make a copy of this database so that you can replace the new resource database with old one if needed.

This concept introduced in SQL 2005.

TUF File in SQL server

TUF file is a Transaction Undo file. File containing information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed. A transaction undo file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.

TUF file is created when configuring log shipping in Standby mode, TUF file plays important role in log shipping. All uncommitted transactions are recorded to the TUF file while restoring the log backup into standby database so that users can read the database. And in next log backup, server will fetch all uncommitted transactions from undo file and check whether committed or not. If committed, those transactions will included in the log backup else it will be remain in TUF file.

Databases without any backup

SELECT    
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   master.dbo.sysdatabases.NAME AS database_name,
   NULL AS [Last Data Backup Date],
   9999 AS [Backup Age (Hours)]
FROM
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY
   msdb.dbo.backupset.database_name 

SQL Server/Agent restart time

SELECT [SQL Server Start Time] = CONVERT(VARCHAR(23), a.sql_start, 121),
       [SQL Agent Start Time] = CONVERT(VARCHAR(23), a.agent_start, 121),
       [SQL Uptime] = CONVERT(VARCHAR(15),
                      RIGHT(10000000+Datediff(dd, 0, Getdate()-a.sql_start), 4)
                      + ' '
                      + CONVERT(VARCHAR(20), Getdate()-a.sql_start, 108)),
       [Agent Uptime] = CONVERT(VARCHAR(15),
RIGHT(10000000+Datediff(dd, 0, Getdate()-a.agent_start), 4)
+ ' '
+ CONVERT(VARCHAR(20), Getdate()-a.agent_start, 108))
FROM   (SELECT SQL_Start = Min(aa.login_time),
               Agent_Start = NULLIF(Min(CASE
                                          WHEN aa.program_name LIKE 'SQLAgent %'
                                        THEN
                                          aa.login_time
                                          ELSE '99990101'
                                        END), CONVERT(DATETIME, '99990101'))
        FROM   master.dbo.sysprocesses aa
        WHERE  aa.login_time > '20000101') a 

 

Compressed objects in a database

SELECT st.name, st.object_id, sp.partition_id, sp.partition_number, sp.data_compression,

sp.data_compression_desc FROM sys.partitions SP

INNER JOIN sys.tables ST ON

st.object_id = sp.object_id

WHERE data_compression <> 0

SQL Server 2012

SQL SERVER 2012 is now available for download. Try new SQL Server 2012 Evaluation and SQL Server 2012 Express.

SQL Server Release History

SQL Server 2012
codename Denali

SQL Server 2008 R2
codename Kilimanjaro

SQL Server 2008
codename Katmai

SQL Server 2005
codename Yukon

SQL Server 2000
codename Shiloh

SQL Server 7.0 OLAP
codename Plato

SQL Server 7.0
codename Sphinx

SQL Server 6.5
codename Hydra

SQL Server 6.0
codename SQL95

SQL Server 4.2.1
codename SQLNT

SQL Server 1.0, 1.1