LOG TRUNCATION

Log truncation occurs at these points:
======================================
>> At the completion of any BACKUP LOG statement.

>> Every time a checkpoint is processed, provided the database is using the simple recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system.

>> The exception is that the log is not truncated if the checkpoint occurs when a BACKUP statement is still active. For more information about the interval between automatic checkpoints, see Checkpoints and the Active Portion of the Log..

>> Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of truncation. When a transaction log is truncated, all log records before the start of the virtual log file containing the MinLSN are deleted.

CHECKPOINT

Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.

Events That Cause Checkpoints
=============================
>> Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup.

>> The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.

>> The log becomes 70 percent full, and the database is in log-truncate mode.

>> A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:

> A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.

> An ALTER DATABASE statement is executed that adds or deletes a file in the database.

>> Also, stopping a server issues a checkpoint in each database on the server. The following methods of stopping SQL Server perform checkpoints for each database:

Using SQL Server Configuration Manager.

Using SQL Server Management Studio.

Using the SHUTDOWN statement.

Using the net stop mssqlserver command in a command-prompt window.

Using Services in Control Panel, selecting mssqlserver, and clicking Stop.

Bringing an instance offline in a cluster..

> A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.

> A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.

> An ALTER DATABASE statement that adds or deletes a file in the database is executed.

Note

The SHUTDOWN WITH NOWAIT statement shuts down SQL Server without executing a checkpoint in each database. This may cause the subsequent restart to take a longer time than usual to recover the databases on the server.

Scripts

1> To find recent Backups for all DATABASES
============================================
SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type

2) Rebuild System Databases in SQL Server 2008
================================================

setup.exe /QUIET/ACTION=REBUILDDATABASE /INSTANCENAME=instance_name
/SQLSYSADMINACCOUNTS= accounts [/SAPWD=password] [/SQLCOLLATION=collation_name]

1. Find setup.exe either from your original media or the "local" setup.exe as found in the directory where you have installed SQL Server in the 100\Setup BootStrap\Release directory. So on my machine, I changed directory to C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release.

2. Run setup.exe with the following syntax from a Windows command prompt:

If you have SQL configured for Windows Authentication Mode use this syntax:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= /SQLSYSADMINACCOUNTS=

where

is either the name of your named instance or MSSQLSERVER for the default instance

are Windows groups or individual accounts to provision as sysadmin

If you have SQL configured for Mixed Authentication Mode use the same syntax except you must also provide the /SAPWD parameter to specify the SA password. If you don't, you will get an error.

If you want to rebuild the system databases with a different collation than what you used to install SQL Server, you would need to supply the /SQLCOLLATION parameter. If you don't supply this parameter, then SQL Server will rebuild the system databases with the collation you selected when you installed SQL Server.

3. When setup has completed rebuilding the system databases, it will return to the command prompt with no messages (It always first prints out the version). If you have any syntax problems or issues with parameters you will see these errors in the command window. If you don't see any errors, then you will need to examine the "Summary" log file to verify it was completely successful.

4. If you immediately go to the directory where logs are stored for setup (100\setup bootstrap\logs), you can open up a file called Summary.txt. This file represents the most recent summary of any execution of setup. If you run setup for any other reason after rebuilding the databases before you look at the summary.txt file you will have to look for a folder inside the logs directory that matches the datetime when you run setup to rebuild the system databases. This may not be something that is simple to do if you have run setup several times so a tip here is to use findstr.exe from the command prompt like the following:

findstr /s RebuildDatabase summary*.*

3) To find database restore history from MSDB
============================================

SELECT TOP 10 *

FROM restorehistory WITH (nolock)WHERE (destination_database_name = ‘Database Name’)ORDER BY restore_date DESC

All Databases

SELECT TOP 10 * FROM restorehistory WITH (nolock)ORDER BY restore_date DESC

4) How to Move Resource Database?
=================================

Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf

Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.

You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.

5) Query when log shipping breaks on secondary server due to an out-of-sequence log
===================================================================================

SELECT TOP 20 b.physical_device_name, a.backup_start_date, a.first_lsn, a.user_name FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.type = 'L'
ORDER BY a.backup_finish_date DESC









1

SELECT SERVERPROPERTY(‘RESOURCEVERSION’);





2

GO





3

SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);





4

GO


To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.

You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
NET START MSSQLSERVER /f /T3608
Named Instance
NET START MSSQL$instancename /f /T3608

Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.










1

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')





2

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')

Differences Between Logshipping and Mirroring

Log Shipping:
============

Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required

Database Mirroring:
===================

Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes <3 seconds but not more than 10 sec
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0

Copy-Only Backups

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005. The types of copy-only backups are as follows:

Copy-only full backups (all recovery models)

A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base.


Copy-only log backups (full recovery model and bulk-logged recovery model only)

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore.

To create a copy-only backup (Transact-SQL)
-------------------------------------------

Beginning in SQL Server 2008, SQL Server Management Studio supports copy-only backups.


How to: Back Up a Database (SQL Server Management Studio)


How to: Back Up a Transaction Log (SQL Server Management Studio)


The essential Transact-SQL syntax for a copy-only full backup is:

BACKUP DATABASE database_name TO … WITH COPY_ONLY …



COPY_ONLY has no effect when it is specified with the DIFFERENTIAL option.



The essential Transact-SQL syntax for a copy-only log backup is:

BACKUP LOG database_name TO … WITH COPY_ONLY …

Tail-Log Backups

>> If the database is ONLINE and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:

BACKUP LOG database_name TO WITH NORECOVERY

>>If the database is offline and does not start.

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:

BACKUP LOG database_name TO WITH CONTINUE_AFTER_ERROR

If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

SQL Server 2008 R2 -Uninstall issues

Go to this registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

Assume need to unintall the "Microsoft SQL Server 2008 Database Engine Shared",

* Search for SQL Server 2008 keys in the uninstall registry.
* Keep searching until you find the Database engine shared install location
Note the “Install source”, copy the path and access that path via Start :: Run
* Right click on the msi and choose uninstall.