21 July 2011

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.

18 July 2011

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.

Truncate LOG- Replication

Truncating the log of a previously replicated database

(The following is specific to SQL Server 2000 and might not apply to more recent versions.)

I occasionally restore production databases to a test system. Normally I just flip the recovery model from full and simple and I’m good to go. Unfortunately, if the database was being replicated it’s not so easy.

Even if you restore the database without “KEEP REPLICATION”, which would imply all the replication bits would be cleaned up for you, the transaction log will still have a replication marker that prevents it from being truncated. This means the log file, even in “simple” mode, will grow unbounded (not good!).

I’m always reminded of this when I try to clean up an ever-growing log with this command:

BACKUP LOG yourdb WITH TRUNCATE_ONLY

and I get this error:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed

Not one to ignore the advice of error messages, I then try running the following commands:

-- see what's going on
DBCC OPENTRAN

-- not too much? just clear the replication marker
EXEC sp_repldone @xactid = NULL,
@xact_seqno = NULL,
@numtrans = 0,
@time = 0,
@reset = 1

Unfortunately, this fails with the following error:

The database is not published.

OK, so part of SQL Server knows it’s not being replicated, I guess that’s good. A lot of sites suggest physically removing the log file by detaching the database, renaming or deleting the log file, and reattaching the database. There’s a much simpler, gentler way:

-- publish database (this doesn't actually create
-- a snapshot--it only takes a cople seconds)
sp_replicationdboption 'yourdb','publish','true'

-- clear that replicaton marker (yourdb should be selected)
EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

-- unpublish database
sp_replicationdboption 'yourdb','publish','false'

Yes, you simply enable replication long enough to clear the marker. This only takes a few seconds as it doesn’t actually generate a new snapshot or anything expensive like that. Now you’re free to truncate the log!

OR

http://blog.wassupy.com/2011/03/truncating-log-of-previously-replicated.html

Truncating the log -Replication

Truncating the log of a previously replicated database

(The following is specific to SQL Server 2000 and might not apply to more recent versions.)

I occasionally restore production databases to a test system. Normally I just flip the recovery model from full and simple and I’m good to go. Unfortunately, if the database was being replicated it’s not so easy.

Even if you restore the database without “KEEP REPLICATION”, which would imply all the replication bits would be cleaned up for you, the transaction log will still have a replication marker that prevents it from being truncated. This means the log file, even in “simple” mode, will grow unbounded (not good!).

I’m always reminded of this when I try to clean up an ever-growing log with this command:

BACKUP LOG yourdb WITH TRUNCATE_ONLY

and I get this error:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed

Not one to ignore the advice of error messages, I then try running the following commands:

-- see what's going on
DBCC OPENTRAN

-- not too much? just clear the replication marker
EXEC sp_repldone @xactid = NULL,
@xact_seqno = NULL,
@numtrans = 0,
@time = 0,
@reset = 1

Unfortunately, this fails with the following error:

The database is not published.

OK, so part of SQL Server knows it’s not being replicated, I guess that’s good. A lot of sites suggest physically removing the log file by detaching the database, renaming or deleting the log file, and reattaching the database. There’s a much simpler, gentler way:

-- publish database (this doesn't actually create
-- a snapshot--it only takes a cople seconds)
sp_replicationdboption 'yourdb','publish','true'

-- clear that replicaton marker (yourdb should be selected)
EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

-- unpublish database
sp_replicationdboption 'yourdb','publish','false'

Yes, you simply enable replication long enough to clear the marker. This only takes a few seconds as it doesn’t actually generate a new snapshot or anything expensive like that. Now you’re free to truncate the log!

OR


06 July 2011

Instant File Initialization Speeds SQL Server

http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/

OR

Sometimes, its just the smallest of details that can make all the difference. For example, on my test system (see the end of this posting for a description), I created a new 50GB database. The database creation process took about 5 minutes and 50 seconds to complete.



Next, I populated the database with over 61 million rows of data, which virtually occupied all of the available space within the newly created database. After that, I backed up the database using SSMS, and then I deleted the original database.

At this point, I restored the database from the backup using SSMS. Below, you see the typical Restore Database screen.



At the bottom, right-hand side of the screen in the Progress box, notice the “Executing (0%)” indicator. Between the time I clicked the OK button to begin the restore, and when the “Executing (0%)” counter began to move, it took about 5 minutes and 50 seconds. At that point, the counter began to increment and the database was restored.

Now I make one very small change to my SQL Server instance (I’ll describe it in just a moment), and then I repeat the above steps (after deleting the database I just restored). First, I created a new 50GB database. This time, instead of taking 5 minutes and 50 seconds to create the database, it takes just under 2 seconds, a savings of about 5 minutes and 48 seconds. Next, I populated the database with the same amount of data as before, backed it up, and then deleted the original file. When I restored the database this time around, instead of having to wait 5 minutes and 50 seconds before the backup began to restore, I only had to wait just under 2 seconds. In both of these cases, I saved a significant amount of time.

So what was the very small change that I made, and why did it radically reduce the amount of time for database creation and database restoration to occur? I turned instant file initialization on.

What is Instant File Initialization?

In my first two examples, before instance file initialization was turned on, the reason it took so long for the database to be created, or the database to be restored (before a database can be restored, its space must first be pre-allocated, much like creating a new database), SQL Server had to go to every page in the 50 GB database and zero each one of them out. It can take a lot of time for SQL Server to go to every 8K page in a file (especially very large files) and physically zero out each page. When instant file initialization is turned on, SQL Server doesn’t have to zero out every 8K page that has been allocated. Instead, the space is just allocated to SQL Server by the operating system in one fell swoop, which is a very quick process, potentially saving you a great deal of time.

How Do You Turn Instant File Initialization On?

Unlike most configuration features in SQL Server, there is no on/off switch for instant file initialization. Instead, you have to assign a specific user right to the SQL Server Service (mssqlserver) account. Here’s what you need to do to turn on instant file initialization.

First of all, to use instant file initialization with SQL Server in a production environment, you must be using some combination of:
•Windows Server 2003 or
•Windows Server 2008 or
•Windows Server 2008 R2

and using:
•SQL Server 2005 (any edition) or
•SQL Server 2008 (any edition) or
•SQL Server 2008 R2 (any edition)

Second, you must assign the SQL Server Service (mssqlserver) a special user right called “Perform volume maintenance tasks”. To do this, start the Local Security Policy tool (you must be a local administrator to perform this task), then drill down to Security Settings | Local Policies | User Rights Assignment | Perform volume maintenance tasks, as you see in the screenshot below.



Once you have located “Perform volume maintenance tasks”, right-click on it and select “Properties”, and the “Perform volume maintenance tasks Properties” screen appears. Click on “Add User or Group” and then proceed through the remaining screens until you select the account that is being used as the service account for SQL Server. In the screen shot below, notice that I have added the BRADMCGEHEE\sqlserverservice account to this user rights assignment. This is the user account I use on my test server to run my SQL Server instance.



Once the SQL Server service account has been assigned this user right, you will have to restart the SQL Server service (of course, only when it is not being used), and from this point forward, instant file initialization is turned on for all MDF files in your SQL Server instance.


Note: If your SQL Server service account is a member of the local administrators group, then the account already has the “Perform volume maintenance tasks” user right and you don’t need to assign it again.

Why Isn’t Instant File Initialization Turned On by Default?

When a SQL Server instance is first installed, one of the things you must enter is a SQL Server service account. If you follow the best practice and select a domain user account to be used as the SQL Server service account, the setup process automatically assigns the domain user account with only just enough rights and permissions to run SQL Server. The “Perform volume maintenance tasks” user right is not automatically assigned during installation because it is not required to run SQL Server, and because allowing the service account to have this additional user right introduces a very small security risk.

Oh no, a security risk! Well, not really much of a security risk. Here’s the possible security risk scenario. The disk that is being used to create the new database on has been used for storing data that has been previously deleted. As you may know, when data is deleted from disk by the operating system, it really is not physically deleted; the space holding the data is just marked as being available. At some point, the older data will be overwritten with new data. This occurs all the time on millions of computers throughout the world every day. And as such, any data that has been marked for deletion, but not yet overwritten, is potentially available for access if you have the right tools and know what you are doing. In fact, undelete software uses this to recover data that has been accidently deleted.

When instant file initialization is not turned on, and when SQL Server allocates space for an MDF file, each of the pages allocated for the database is zeroed out, which removes the older data, in theory, preventing it from being accessed. I say “in theory” because there are computer forensics techniques that can even recover data that has been overwritten, but that discussion is really not applicable here.

So if instant file initialization is turned on, there is a very slight risk that someone could go to the pages allocated for the new database and read any older data that still may exist there. This is essentially a non-issue in virtually every organization, other than those that require very high security. But because of this potential security issue, instant file initialization is not turned on by default.


If instant file initialization is turned on, and pages are not zeroed out when the database is initially created, SQL Server will automatically overwrite any data that might have been on those pages when SQL Server needs that space.

When Is Instant File Initialization Used?

If instant file initialization is turned on, it is used in all of these cases:
•When a database is first created
•When a an existing database’s size is manually increased
•When tempdb is recreated each time SQL Server is restarted
•When autogrowth kicks in
•When backups are restored (as the space has to be pre-allocated before a restore can occur)

Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization. This is because log files are circular in nature and must be zeroed out, as random data in transaction log pages can be problematic. In my earlier test, when I created a new 50 GB database, the MDF file was 50 GB and the log file was only 1 MB. If I had created a large log file (which is not uncommon), it would have taken awhile for the log to be created, although the MDF file would have been instantly created. This is also true when you manually increase the size of a log file, or when log file autogrowth occurs. In other words, don’t expect to have all of your databases (MDF and LDF files) created in less than 2 seconds like in my test. While the MDF will be created virtually instantly, the log file may take awhile to be created.


When I was working with SQL Server 2000 a few years back, which does not support instant file initialization, one of the things that annoyed me the most when restoring large databases was waiting for the database space to be allocated before the restore actually began. During emergency database restores, this wasted a lot of precious time, preventing me from getting the database back into production as fast as I would have preferred. If you aren’t using instant file initialization today, you are facing this same problem. That’s why I recommend all SQL Server 2005/2008 instances have instant file initialization turned on. The time saved when restoring databases is the best reason to use instant file initialization.

Check to See if Your SQL Server Instances Have Instant File Initialization Turned On

Hopefully, by now, you see the benefits of using instant file initialization. Assuming that you don’t already know if instant file initialization is turned on or off on the SQL Servers your manage, I challenge you to check and see, and if you find it turned off, turn it on and reap its many benefits.




Test Hardware
•Dell T610 Tower, with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one dual-port HBA (to connect to the DAS); and dual network connections. Hyper-threading turned off.
•One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives. MDF files located on RAID 10 array with 10 spindles, LDF files on RAID 10 array with 4 spindles, backup drive on a single spindle.