Database Refresh

Hi,
Off late I have been doing a lot of db refresh on adhoc basis but I tend to do some mistakes each time. So I thought of tabulating the steps in one central point. I hope some of you can also benefit from it. Below mentioned are logical steps and some scripts to make things easier.


Steps for refresh:-
>take backup from prod
>zip the bakcup
>move the zip to UAT server
>unzip the backupfile
>take backup of the database on UAT and keep it in a separate folder just in case of roll back

>run
[restore filelistonly from device ] and [sp_helpdb ] and check that both have matching files.
>script out the roles access rights on UAT database
>script out the user access rights on UAT database
>kill all user connections on the database on UAT
>restore the database on UAT
>delete the users exect dbo,sa,guest and other
>run the user access script
>use sp_change_users_login 'report' to generate the orphaned logins
>run sp_changedbowner 'sa'
>change the recovery plan of the UAT back to its original as the Prod may have a different recovery plan.
>send mail to use rto verify the database access for a couple of logins

That should be basic steps. I will soon update the script to get the access list for the users in a database.

Script for users and user access :


set nocount on

-- users created for logins and with their defualt schema
select 'CREATE USER ['+p.name +'] '+
case when l.name is not null then ' FOR LOGIN ['+l.name+'] ' else ' WITHOUT LOGIN ' end +
case when p.default_schema_name is not null then ' WITH DEFAULT_SCHEMA = '+p.default_schema_name +' 'else ' ' end + CHAR(13)+CHAR(10)+ 'GO '
from sys.syslogins l
right outer join sys.database_principals p on p.SID=l.SID
where (p.type='S' or p.type ='U')

--roles related to users in database
select 'Exec sp_addrolemember ' + ''''+prole.name+''''+','+'''' +puser.name +'''' + CHAR(13)+CHAR(10)+' GO'
from sys.database_role_members r
join sys.database_principals prole on r.role_principal_id= prole.principal_id
join sys.database_principals puser on r.member_principal_id= puser.principal_id
order by 1

set nocount off

-------------------------------------------------

Hope this helps.I am still missing the grants in the script. Will work on that today and add it to the script.






Tushar

Linked Server

Now there was this issue from one the developers when they are trying to connect to a database through the linked server it errors out. I tried to login and fire the same query it ran so I asked him to check again with the proper credential. He was using his windows login which was part of some windows domain group which I was unable to resolve. So I asked him to use the login the application will actually use. He tried that but there was no lock. Now I looked into the configuration of the linked server it was set to use the login's current security content. User was trying to run a proc which was calling a table in the linked server. So while he tried to execute this he forgot to check if the login actually also existed in the linked server as well. I identified that the login in the linked server was missing. We changed the security content to use a paricular login for the linked server to fix this.


Tushar

T-Replication 101

Well there have been a lot of articles for starting with transactional-replication. But there have been rarely an article for fixing the common issues one runs into. That's the motivation for this article. Actually I had also run into these issues a couple of times but used to drop the whole replication and bring it up again. But then one fine day I decided to find out the basics and try to help others on this.

After configuring the distribution, publication and subscription when you start the replication monitor you may encounter the below mentioned error.

Error authenticating proxy tushar-hub\lifercks, system error: Logon failure: unknown user name or bad password.). The step failed.

Well the reason may be you have put a windows account with some wrong password. Well the immediate fix can serve as you go to the agent job and in the step('Run agent') where the agent starts; use the 'Run As' in the property and change to run under the sql server service account. This should fix the issue. I am presuming here that your sql server agent service account is running under a domain account with all security checks.

http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/42229a2c-a9ef-4082-806c-3381e9f6d510

The next most common error will be the replication is failing.
Error :
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)

The issue is some application/user updated/deleted/inserted some row directly in the subscriber. Then when there is a change in publisher and the distributor tries to push those changes into subscriber the error shows up as there is inconsistancy between subscriber and publisher.

To fix this there are a couple of steps we have to follow.
1. To find the sequence number and then the articles involved in the mismatch.
2. Find the data miss match

Now to find the lsn number we will use the query mentioned below.

select * from distribution..MSrepl_errors with(nolock) where time> '20110921' order by time desc

here '20110921' is the time stamp from when we suspect the error has occured.

Now when we have the sequence number we fire the next query to identify the objects which are in suspect.

exec distribution..sp_browsereplcmds @xact_seqno_start='0x00023C6800000BA3000C00000000', @xact_seqno_end='0x00023C6800000BA3000C00000000'

here 0x00023C6800000BA3000C00000000 is the lsn number.

Now since we have the objects we can use the table diff utility to find the actual data issues we have



to be continued.

Lets Start with Kid's Stuff..

How to delete a login which has users mapped to different databases?
Well you can drop a login to which database users are mapped but this will create orphaned users.

Well to begin with we have to know the name of the login name from syslogins table in case you are not sure abpout the login name.

query : select name from syslogins where name like '%%'

Now we got the login name to begin with and then we can get all the user databases the login has access to with help of sp_helplogins
query: Exec sp_helplogins 'loginName'

Now we need to delete the user from the different databases.
We will use the sp_dropuser to delete the user from the databases.
query : EXEC revokedbaccess ''
remeber this name is the username in the database corresponding to the login.

Now after deleting all users from all the databases. We need to delete the login as well. You can do that from the GUI or use the script.

script: Exec sp_droplogin ''

References: MSDN

Tushar

Virtual Log Active and Inactive

Hi,
This is a script which will help to look up the active and inactive virtual log file count.


CREATE TABLE #eachDB(FileID INT, FileSize BIGINT, StartOffset BIGINT, FSeqNo BIGINT
, [Status] BIGINT, Parity BIGINT, CreateLSN NUMERIC(38))

CREATE TABLE #summary(Database_Name sysname, VLF INT, VLF_Count INT, Log_File_ID INT)

EXEC sp_MSforeachdb N'Use [?];

Insert Into #eachDB

Exec sp_executesql N''DBCC LogInfo(?)'';

Insert Into #summary

Select DB_Name(),Status,Count( Status), FileID

From #eachDB
group by Status,FileID

Truncate Table #eachDB;'

SELECT Database_Name,Case When VLF=2 Then 'Active' else 'InActive' End as Virtual_log,VLF_Count,Log_File_ID
FROM #summary
where Database_Name not in ('model','master','tempdb','msdb')
ORDER BY Database_Name,Log_File_ID ;

DROP TABLE #eachDB;
DROP TABLE #summary;

Tushar

Generating Script for re-building and re-organising indexes

Hi,
This script will help to prepare the script for re-building or re-oragnising index based on 30% default fragment index.

-- USE DB
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
-- EXEC (@command); -- the execute command is commented here..
PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

reference: sqlservercentral.com

Tushar

Listing Jobs names and Schedule

Hi,
This is a script to detail the job names and schedule.

Script:
use msdb
Select Job.job_name, Job.category,

CASE
WHEN Job.enabled=0 THEN 'INACTIVE'
WHEN Job.enabled=1 THEN 'ACTIVE'
END AS STATUS

,D.schedule
from
(select j.name as job_name,c.name as category,j.enabled ,j.job_id
from sysjobs j
join syscategories c on c.category_id=j.category_id
) as Job
join sysjobschedules sc on sc.job_id = Job.job_id
join
(SELECT s.schedule_id,
s.[name],
CASE
WHEN s.freq_type = 0x1
THEN
'Once on '
+ CONVERT (
CHAR (10),
CAST (CAST (s.active_start_date AS VARCHAR) AS DATETIME),
102)
WHEN s.freq_type = 0x4 AND s.freq_interval > 0
THEN
CASE
WHEN s.freq_interval > 1
THEN
'Every ' + CAST (s.freq_interval AS VARCHAR) + ' days'
ELSE
'Every day'
END
WHEN s.freq_type = 0x8
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Weekly on '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' weeks on '
END
+ LEFT (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END,
LEN (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END)
- 1)
WHEN s.freq_type = 0x10
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CAST (s.freq_interval AS VARCHAR)
+ CASE
WHEN s.freq_interval IN (1, 21, 31) THEN 'st'
WHEN s.freq_interval IN (2, 22) THEN 'nd'
WHEN s.freq_interval IN (3, 23) THEN 'rd'
ELSE 'th'
END
WHEN s.freq_type = 0x20
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CASE s.freq_relative_interval
WHEN 0x01 THEN 'first '
WHEN 0x02 THEN 'second '
WHEN 0x04 THEN 'third '
WHEN 0x08 THEN 'fourth '
WHEN 0x10 THEN 'last '
END
+ CASE s.freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'day'
WHEN 9 THEN 'week day'
WHEN 10 THEN 'weekend day'
END
WHEN s.freq_type = 0x40
THEN
'Automatically starts when SQLServerAgent starts.'
WHEN s.freq_type = 0x80
THEN
'Starts whenever the CPUs become idle'
ELSE
''
END
+ CASE
WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1
THEN
' at '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' every '
+ CAST (s.freq_subday_interval AS VARCHAR)
+ CASE freq_subday_type
WHEN 0x2 THEN ' second'
WHEN 0x4 THEN ' minute'
WHEN 0x8 THEN ' hour'
END
+ CASE WHEN s.freq_subday_interval > 1 THEN 's'
ELSE ' '
END
ELSE
' '
END
+ CASE
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' between '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
+ ' and '
+ LEFT (s.active_end_time, 2)
+ ':'
+ substring (s.active_end_time, 3, 2)
+ ':'
+ right (s.active_end_time, 2)
ELSE
''
END
AS schedule
FROM (
SELECT schedule_id,
[name],
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
REPLICATE ('0', 6 - len (cast (active_start_time AS VARCHAR)))
+ cast (active_start_time AS VARCHAR)
AS active_start_time,
REPLICATE ('0', 6 - len (cast (active_end_time AS VARCHAR)))
+ cast (active_end_time AS VARCHAR)
AS active_end_time,
date_created,
date_modified,
version_number
FROM msdb.dbo.sysschedules) AS s ) as D on D.schedule_id=sc.schedule_id


Refernence: sqlservercentral.com

Tushar

When was Server Last Restarted

There was a report that we had to put together for the servers that had re-started within the tenure and the probable reason for the restart.
Below query gives you the time the SQL Server Reatrted. This query assumes that the tempdb is created everytime the SQL Server restarts.
SELECT [Name] , CrDate as DateServerRebooted
from sys.sysdatabases
WHERE dbid = 2

Now if you want to further when the SQL Server was stopped and the probable reason for the Server shutdown you have to go to the individual error logs and identify.

Tushar

Getting User Access of all Users in a Database

Hi,
Had a requirement to refresh the database in dev from prod but the criteria was to retain the user access for that db intact. So needed to work on this as a priority. I have broken down the task in a two phases.

1st phase:
To identify the script to generate the present user access for the users in a database. Below is mentioned the script which will help me get the list.

select su.name as UserName ,sg.name as AccessGroup from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid

2nd phase:
To identify the script which will help me restore the access for the users in dev enviroment after the database refresh.

select 'Exec sp_addrolemember ' +''''+ sg.name+ ''''+','+''''+ su.name+''''
from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid


Tushar

Why no T-Log Backup possible in SIMPLE

When in SIMPLE recovery model a database log records in the active VLFs. These records are retained, as they may be required for a rollback operation. However, the inactive VLFs are truncated when a checkpoint occurs, meaning that the log records in these VLFs can be immediately overwritten with new log records. This is why a database operating in SIMPLE recovery is referred to as being in auto-truncate mode. In this mode, no "history" is maintained in the log and so it cannot be captured in a log backup and used as part of the restore process.

reference: sqlservercentral

Tushar

sp_lock and sp_who

Hi prepared a script to imitiate the sp_lock using DMV.

SELECT
tl.request_session_id as spid,
tl.resource_database_id as dbid,
tl.resource_associated_entity_id as objid,
tl.resource_type type,
tl.resource_description as resource,
tl.request_mode as mode,
tl.request_status as status
FROM sys.dm_tran_locks tl

Preparing a script for sp_who and sp_who2. Should be here soon.

Tushar

optimisng database maintenance plans

Fisrt step will be to identify the database files read,writes,i/o stats. For that we may utilise the below metioned queries.

1. To list NumberReads,NumberWrites,IOStallMS and Recovery_Model use the below mentioned script.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


CREATE TABLE #tbl_DB_FileStats
(
DatabaseName varchar(255),
FileName varchar(255),
NumberReads numeric(18,0),
NumberWrites numeric(18,0),
IOStallMS numeric(18,0)
)



EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBID int;
USE [?];
SET @DBID = DB_ID();
INSERT INTO #tbl_DB_FileStats
SELECT
DB_NAME(DBID) AS DatabaseName,
FILE_NAME(FileID) AS FileName,
NumberReads,
NumberWrites,
IoStallMS
FROM ::fn_virtualfilestats(@DBID, -1);'





create table #data (name sysname , db_size nvarchar(13), owner sysname, dbid smallint, created datetime, status varchar(600),
compatability_level tinyint)
insert into #data Exec sp_executesql N'sp_helpdb'

select name as 'Database',db_size,owner,substring(status,patindex ('%Recovery%',status)+9,patindex ('%, Ver%',status)- patindex ('%Recovery%',status)-9 ) as Recovery_Model
into #t1
from #data
where name not in ('model','master','tempdb','msdb')

select DatabaseName as 'Database' ,NumberReads,NumberWrites,IOStallMS
into #t2
from #tbl_DB_FileStats
where FileName not like '%log'
and DatabaseName not in ('model','master','tempdb','msdb')




select t1.[Database],t1.db_size,t1.[owner],t2.NumberReads,t2.NumberWrites,t2.IOStallMS,t1.Recovery_Model
from #t1 t1
inner join #t2 t2 on t1.[Database]=t2.[Database]




drop table #tbl_DB_FileStats
drop table #data
drop table #t1
drop table #t2

2. Database Index Fragmentation Information use the below mentioed script

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


CREATE TABLE #tbl_DB_IDX_Stats
(
[Database] sysname,
[Object] sysname,
[Index_ID] int,
[Index_Name] sysname,
avg_fragmentation_in_percent numeric(18,0)
)


EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBID int;
USE [?];
SET @DBID = DB_ID();
INSERT INTO #tbl_DB_IDX_Stats
SELECT db_name(ps.database_id) as [Database], Object_name(ps.OBJECT_ID) as [Object],
ps.index_id as [Index_ID], b.name as [Index_Name],ps.avg_fragmentation_in_percent as avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
and avg_fragmentation_in_percent>70
and name is not null
and @DBID not in (1,2,3,4)
ORDER BY ps.OBJECT_ID;'

select * from #tbl_DB_IDX_Stats

drop table #tbl_DB_IDX_Stats


Tushar

Identify the Recovery Model

Hi All,
In this post I will like to share a script which will generate a report about the detail of the databases on the server. The script can be modified to show more information as per requirement. I have used sp_helpdb to get all the information.

Script:
create table #data (name sysname , db_size nvarchar(13), owner sysname, dbid smallint, created datetime, status varchar(600),
compatability_level tinyint)
insert into #data Exec sp_executesql N'sp_helpdb'
select name as 'Database',db_size,owner,substring(status,patindex ('%Recovery%',status)+9,patindex ('%, Ver%',status)- patindex ('%Recovery%',status)-9 ) as Recovery_Model
from #data
where name not in ('model','master','tempdb','msdb')
drop table #data


Hope this helps a lot of accidental DBA's to perform thier task of reporting.

Tushar

SQL Server Backup and Restore History

Hi All.. This is one place I know most you must have knowledge about. Just mentioning this so that you all have one stop for the information.

To identify the backup history for the databases on the server.

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

To identify the restore history of the databases on the server.
select restore_history_id,restore_date,destination_database_name,
user_name,restore_type,backup_set_id from msdb..restorehistory


Tushar

SQL Agent Service running under which account

Well. Today morning got an unusual requirement to identify the account under which the SQL SErver Agent Service is running. I did identify the undocumented stored procedure that helped me out in the work but also had an interesting finding.

If you want to know the aoount under which the SQL Server is running the try the below mentioed query.

DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccountName OUTPUT,
N'no_output'

SELECT @ServiceaccountName

This gives the service account name for the SQL Server.

To identify the service account for the SQL Server Agent use the undoucmented stored procedure as is mentioned below.
exec msdb..sp_get_sqlagent_properties
Note that there might be some risk factors involved while using undocumented procedures in production.

Tushar

RAID(Redundant Array of Independent Disks)

There are a lot of optimization in SQL Server performance on the Server level. We have to also consider the hardware level optimization possible. The biggest bottle neck in performance in any RDBMS is the I/O bottle neck. There is no standard solution to all the issues of bottle neck and the issues are customised for a particular database with the kind of data and the kind of operations are most frequent in the database. There are a lot of research going around in this area and solid state devices are playing an imporatant part in it. In this article I will focus on the RAID array. This article will be the preface to how and when to choose the specific kind of RAID array.


RAID is a technology that provides increased storage functions and reliability through redundancy. This is achieved by combining multiple disk drive components into a logical unit, where data is distributed across the drives in one of several ways called "RAID levels".

to be continued...

Log File Accidently Deleted!!

Watch out this section for steps to be taken when log file is deleted.

LazyWriter

Well this is no story about a writer who is lazy. Ironically they actually are : ) But then today I thought to document something about SQL Server which I think about lot of times and then as usual forget to document.

Now to start with lazy writer is a process in sql server which helps in clearing the buffer cache. The buffer cache as the name suggests are the cache pages which sql sever uses to load the data from data pages for manipulation. Now Lazy Writer is the process which flushes out batches of dirty, aged buffers and makes them available to user processes. The lazy writer eliminates the need of perfomrning frequent checkpoint in order to create available buffers.

The process of clearing the buffer cache is not that easy. By that I mean it's calculative. Now each page in the buffer cache has a refernce counter and a bit to note whether it is a dirty page. (Now a dirty page is one which has modified data which has yet not been written back to disk.) Comming back to the calculative part the reference counter that I mentined associated to the page gets decremented each time a buffer scan happens. Now if the count is reduced to 0 then the page is written back to the data file page and the buffer page is emptied. This job is done by the lazy writer.

Basically LazyWriters ensure two things:
a) There are adequate resources in buffer pool for the sql server to use.
b) Monitor the usage of commited memory by the buffer pool and ajust it as necessary so that enough physical memory remains free to prevent windows from paging.

LazyWriter can adjust the number of buffers in the buffer pool if the dynamic memory management is enabled in SQL Server. SQL Server estimates the adequate number of buffer pools depending upon the system activity and the number of stalls.(Stall happens when the system has to wait for a buffer free page when a request for memory is raised)

Tushar

ACID

ACID Properties
The ACID (Atomicity, Consistency, Isolation, and Durability) properties are a core requirement for SQL Server and other transactional, reliable database products.
Atomicity
A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them are performed.
Consistency
When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain data integrity. All internal data structures, such as B-tree indexes or doubly linked lists, must be correct at the end of the transaction.
Isolation
Modifications made by concurrent transactions must be isolated from modifications made by all other concurrent transactions. A transaction either sees the data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it provides the system with the capability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.
Durability
After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.
Write-Ahead Logging (WAL) Protocol
Write-Ahead Logging is a key technique for providing the ACID properties. Briefly, WAL requires that all the transaction log records associated with a particular data page be flushed to stable media before the data page itself can be flushed to stable media.

Reference : Technet