CHECKPOINT and Transaction Log in SQL Server

CHECKPOINT is nothing but just a process which writes all dirty pages to the database. After reading this statement, question might pops up in your mind that what are dirty pages? So, Dirty page is nothing but just data pages that have been modified but not written to the disk.

So making a long story short, CHECKPOINT writes all dirty data pages to disk so you don’t lose any data. CHECKPOINT is a command which you can execute stand alone or/else it get executed automatically in following situations.

–> When you start talking the backup of database, database engine first make CHECKPOINT so that each and every data will be in backup file.
–> At the time of altering database, especially when you add/drop any database files.
There are few more situations other then above described situations.
Since I am talking about CHECKPOINT, how come I forgot to mention one of the very hot topics in SQL Server which is truncate transaction log?
I have observed many times that when log file became bigger in SQL Server, developer or DBA used to truncate transaction log file. I really against this habit as long as possible, don’t truncate your transaction log, it will break your log chain. Rather do regular full backup and transaction log backup which will CHECKPOINT itself and will keep your log file in control.
For more details on CHECKPOINT, please do refer MSDN.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

DDL Trigger in SQL-Server 2005 for Create table, alter table, drop table, create procedure, alter procedure, drop procedure etc.

DDL Trigger in SQL-Server 2005 for Create table, alter table, drop table, create procedure, alter procedure, drop procedure etc.
You might be aware with the term of “TRIGGER” which is not at all new concept but yes, it has new feature in SQL-Server 2005 which you have not experienced in any of prior edition of SQL-Server before 2005.
Microsoft has introduced two type of DDL trigger. Database level DDL trigger and Server level DDL trigger. Now, you will think that where can you use it???? Answer is very broad, you can use it to protect your database or your server from being create, drop or alter table or procedure. You may also wish to have track about who is doing what???? You can track any create, alter or drop regarding your tables or your procedures.
I am going to show you one short example, further you can develop your own TRIGGER for your customize need. I assume that all of you are having AdventureWorks database installed on your computer, ships with SQL-Server 2005.
Let us create one small table for tracking database activity.
–this table will store some information about user activity
CREATE TABLE dbLOG
(
e_time VARCHAR(50),
e_servername VARCHAR(25),
e_username VARCHAR(15),
e_commandtext VARCHAR(max)
)
Now, we will create one database level trigger which will track any user if s/he tries to create, alter or drop table in AdventureWorks database.
CREATE TRIGGER [db_LOG]
ON DATABASE –this statement will create DDL trigger on database level, you can use
— ON ALL SERVER for making trigger for server (for all database in your server)
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE –will track only create, drop or alter table
AS
SET NOCOUNT ON
DECLARE @xEvent XML
SET @xEvent = eventdata() –capture eventdata regarding SQL statement user have fired
INSERT INTO dbLOG VALUES(
REPLACE(CONVERT(VARCHAR(50), @xEvent.query(‘data(/EVENT_INSTANCE/PostTime)’)),‘T’, ‘ ‘),
CONVERT(VARCHAR(25), @xEvent.query(‘data(/EVENT_INSTANCE/ServerName)’)),
CONVERT(VARCHAR(15), @xEvent.query(‘data(/EVENT_INSTANCE/UserName)’)),
CONVERT(VARCHAR(MAX), @xEvent.query(‘data(/EVENT_INSTANCE/TSQLCommand/CommandText)’))
)
GO
Now, you can try creating one table in database and you will get log of the same from our “dbLOG” table.
create table test1(id int)
Now check your dbLOG table. You will have information about user activity which we have captured from EVENTDATA() in our trigger. We have just captured very little information from EVENTDATA(), but you can have more than that. Following XML script shows you full data of EVENTDATA() contain.

<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2009-03-05T17:48:43.063</PostTime>
<SPID>56</SPID>
<ServerName>ECHEM-SRV</ServerName>
<LoginName>ECHEM-SRV\Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Test1</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS=”ON
ANSI_NULL_DEFAULT=”ON
ANSI_PADDING=”ON
QUOTED_IDENTIFIER=”ON
ENCRYPTED=”FALSE” />
<CommandText>CREATE TABLE [dbo].[Test1](
[id] [int]
) ON [PRIMARY];</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
You can use any of the information contained in above XML script.
You can disable, enable and drop DDL trigger with following commands.
DISABLE TRIGGER [db_LOG]
ON DATABASE
GO
ENABLE TRIGGER [db_LOG]
ON DATABASE
GO
DROP TRIGGER [db_LOG]
ON DATABASE
GO
Reference: Ritesh Shah