Month: September 2011

Windows8 – First Look and My personal Experiences!!!

Windows 8……….!! IMHO, this is one of the best products(with respect to client OS’es) from Microsoft so far. Being in Pre-development phase, it is showing stunning performance. As  already shared in my previous post, I Installed Windows8 with Dual boot Option. Let me tell you something, Once i select Windows8 from the Boot Screen, all it takes is 2 seconds(Yes, I repeat…It’s 2 Seconds) to get into Logon Screen. Once i enter my password, I’m welcomed with windows8 Metro screen in another 1-2 seconds. Believe me guys….It’s blazing blazinggg….Fast!!!

You can see, the Brand New Metro Style Look of  Windows8 in the below screenshot.

If you want the old Windows 7 look(Standard Desktop), all you have to do is Click the Desktop App(You can see in 2nd row from bottom in the above Screenshot)and you will be right there. You can switch between Apps just with our typical key board shortcut(Alt+Tab or Window+tab) or just place your mouse anywhere on the left corner of your screen, you’ll see a small window popped out for switching between Apps.

It got IE 10(Internet Explorer 10), which is again wayyyyyyyyyyyyy better than the current IE8/9 in terms of speed and refresh rate(s).  See the below Screenshot for Brand new Metro Style Control Panel.

Personally, am Very much impressed with brand new Task Manager(It has a blend of multiple Sysinternal Tools all at a single place!!) Let’s take detailed look at Windows Task Manager now. See the various Screenshots of Tabs available within Windows8 TaskManager below….

If you click on Launch Resource Monitor in the above Screenshot, You’ll be getting the below.

Look at the details which it is presenting in Memory and Network Activity…..!!(Basically Very Similar to Win7, but with rich graphics)

You can see the Disk Read/Write activity at file level and Process level at a single glance 🙂

In the above Screenshot you can see the resource consumption history! Tadaaaaa!!!!!

The Services tab gets a feel of running actual “Services.msc” with Icons and PIDS!

Let’s switch gears now!

See how Windows8 shows the File Copy progress in the below screenshot. I captured the Screenshot when i was copying a video file from my external Hard Disk.

Huhuuuuuuuuu!!

The Bottom Line is “I’m Loving Windows8 Experience!!!” Kudos Microsoft! Keep up the good work:)

Installing Windows8 with Dual Boot Option

Hey……!All excited to let you guys know that at laaaaast I was able to Install Windows 8 in my Laptop 🙂 Huhuuuuuuuu. Thought of sharing my experiences with getting this done!

How to Install Windows8 with Dual Boot Option?

Before jumping into actual content, let me tell you guys difficulties I had when i was trying to Install on Virtual Machine(s).

My Laptop Hardware Specs(in brief):

Windows 7 X64, Intel CPU with No VT(Virtualization Technology), Harddrive which already had 4 partitions which is the maximum limit for mine 😦

Initially, I thought of Installing Windows 8 on a Virtual Machine. I started with VMware WorkStation(Version 7.x) which i was continuously getting BSOD(Blue Screen of Death) no matter what options I tweak while installing Windows8.

I gave up and tried downloading Oracle Virtual Box. Annoyingly I was not getting BSOD but, all kinds of other CPU related Errors :(. I Guess this is because my CPU doesn’t support Virtualization Technology. PS, even if your CPU supports VT, it will be disabled by Default in most of the laptops and you’ve to manually enable that feature from BIOS Settings.

If you are using Intel CPU, to identify whether your CPU is VT compatible or not, you can make use of “Intel Processor Identification Utility“.  Or you can use one of my favorite tool called CPUZ. CPUZ is vendor independent.  See the below Screenshot of Intel’s Util.

Basically I lost hopes on Installing Windows 8 on a virtual machine and i had to Install on my Laptop. Here we’ve two Options

1. Overwriting Windows 7( which I don’t recommend)

2. Installing Windows8 on a separate partition with Dual boot Option(in other words, you will be having both Windows 7 and Windows8; you’ll be given an option to choose either OS while you boot your machine. 

Let’s see what I did to make this happen!

Step1:

Created a separate partition from Disk Management(named it as Win8). How to create a new partition in Windows?Right Click on My Computer – >Manage->Disk Management. Right Click on Shrink Volume and choose the required Size in the next screen and click on “SHRINK” button. It will create un-allocated space. Now Right Click on “Un allocated Space” and select “create a Simple Volume” and Choose NTFS as File System,Choose Quick Format, Choose a Drive letter, Name the Volume and You can leave the rest to defaults.  You can see below Screenshot in my laptop.

As you can see I took the above Screenshot from Windows8:)

Step2:

You’ve to download Windows 8 ISO file(Windows is providing X86 and X64 versions). Choose accordingly.

Step3:

Download Windows7 USB DVD Download Tool, for creating a bootable USB or DVD from the above ISO. How to create a Bootable USB Device? You can simply follow the below Screen shots once you are done with Installing the tool.

I’ve inserted a USB drive instead of a DVD. PS your USB device will be formatted.

Step4:

We are all set with pre-requisites! Now, Plug in your USB Stick and Restart your Computer and change your Boot Priority to USB(or DVD) device first, or You can choose Select Boot Device Option(If you’ve it) when you restart your Computer.

Step5:

Once you boot your machine via USB or DVD which you prepared in the above steps, You’ll be welcomed with the brand new Windows 8 Installation Center.  Here you’ve to choose your Language, TimeZone and Keyboard Settings as shown below.

In the Next Screen, You’ll be seeing two Options a) Upgrade b) Custom(Advanced). Since we are doing Dual boot, you’ve to select option b(Custom). Now you’ve to select the Partition(not the C Drive) which we’ve created earlier and decided to dedicate to Windows8 and hit next.

That’s it…..You are all set:) Your Installation will be done with couple of Next Next Buttons! It’ll ask for few personalization settings at the end of setup. If you see any errors, in the last step, probably your ISO file is corrupted, you’ve to download a fresh copy from scratch.

From next time On….you’ll be welcomed with two Options(Windows Vista/7 and Windows8) to choose each time you reboot your machine!

I’m having lot of Fun playing with Windows 8. Try it yourself and feel the difference 🙂

Intel CPU’s for a SQL DBA- Few Awesome Resources to start with!

In recent times, I started spending most of my time analyzing the Hardware of a Physical Server and thought of sharing few resources from Intel’s website which i found very useful myself(Exploring CPU). Believe me guys, underlying H/W is the most interesting aspect to explore if you’ve really started thinking about “Well, This is a brand new X64 Windows Server 2008R2 running SQL Server 2008R2  Everything Looks Okay with my Server, But why is not upto our expectations? What’s really missing in my Server??” When you start thinking about these aspects, you’ll come to know loooooooot of secrets which are hidden behind the scenes.

Most of the times as a SQL DBA, we are only concerned about Memory and IO but as far as CPU is concerned, we are masked at a very highlevel. How many of us(SQL DBA’s) really understand the underlying micro architecture of our CPU’s?? You might be thinking, well, do i really need to know that being a DBA? The answer is Yes, to Some extent at least! As a DBA, if you are responsible to architect your SQL Server, if you’ve liberty to recommend the underlying hardware, then definitely yes! You should know this.

Let’s consider a scenario, You were given a choice to choose between Intel Xeon Xxxxx, Intel Xeon Exxxx and Intel Xeon Lxxxx. What would you choose without understanding what X,E,L really mean to Intel??? (I’ve seen people choosing L3406 for SQL Servers even in Year 2011, which is one of the biggest flaw you could do while architechting a SQL Server.)

X –  Performance – Best choice for a SQL Server in general.(PS I’m not talking about new numbering-Modeling[E3/E7]/OLTP vs OLAP)

E – Mainstream(rack – optimized)

L – Power – Optimized – Worst choice for a SQL Server in general.

Below is the information I captured from Intel’s official public website.

Intel® Xeon® Processor E3 and E7 families

The latest Intel® Xeon® processor numbering system is an alpha numeric representation of product line, product family and version. An ‘L’ suffix will be used identify a low power processor. The version number will not be used in the first processor generation.

Processor Name = Brand (Intel® Xeon® processor) + Number (E3- 1 2 35)

Intel® Xeon® processor families and product lines

Processor Family Product Line System Type
Intel® Xeon® processor E7 Multi-processor
Intel® Xeon® processor E5 (future release)
Intel® Xeon® processor E3 Single-processor

Intel® Xeon® and Intel® Itanium® processors

Intel® Xeon® and Intel® Itanium® processor numbers are categorized in four digit numerical sequences, and may have an alpha prefix to indicate power and performance.

Processor Name = Brand (Intel® Xeon® processor) + Number (X5482)

Alpha Prefix Description
X Performance
E Mainstream (rack-optimized)
L Power-Optimized

Intel® Xeon® and Intel® Itanium® processor families and their number sequence

Processor Family Number Sequence System Type
Intel® Itanium® processor 9000 Multi-processor and dual-processor
Intel® Xeon® processor 7000 Multi-processor
Intel® Xeon® processor 5000 Dual-processor
Intel® Xeon® processor 3000 Single-processor

To architect a SQL Server is quite different from architechting any other Physical Server running any other application. Looks like i’m deviating a bit( a lot 😉 this post is not to discuss, what are the considerations for building a SQL Server) from what i actually thought of sharing with you all! Let’s go the actual intended content for this blog post..Sighs!!

My Fav places to explore Intel CPU Architectures:

http://www.intel.com/content/www/us/en/silicon-innovations/intel-tick-tock-model-general.html

http://www.intel.com/products/processor_number/about/xeon_itanium.htm

http://ark.intel.com/#serverprocessors

Hope this gives a kick start to dive deep into Intel CPU architectures. Cheers!!…

SQL Server CheckPoints – Trace Flag 3502

Checkpoint – One of the wonderful mechanisms baked within SQL Server to Flush Dirty Pages to Disk and to clear Transaction log(When a Database is Set to SIMPLE Recovery Model) automatically.

Few scenarios where CHECKPOINTS are triggered automatically:

When SQL Instance has been Shutdown, a checkpoint on all the databases.

A Checkpoint on Model Database each time a new database has been created.

T-Log 70% full and Database is in Simple Recovery Model.

A Manual CHECKPOINT by a Sysadmin or Database Owner/Backup Operator.

So…Is there any easy way to monitor the occurrences of Checkpoint??

Yes…We’ve a Documented Trace Flag 3502 which can be enabled Globally.

Demo: Scenario 1 – Shutting Down SQL Instance

I’ve enabled 3502 Traceflag and shut down my SQL Instance. See the Error Log contents below.

2011-09-19 22:12:08.200 spid52 Microsoft SQL Server 2008 R2 (SP1) – 10.50.2425.0 (X64)
Apr 6 2011 21:03:25
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
2011-09-19 22:12:08.210 spid52 (c) Microsoft Corporation.
2011-09-19 22:12:08.210 spid52 All rights reserved.
2011-09-19 22:12:08.210 spid52 Server process ID is 1968.
2011-09-19 22:12:08.210 spid52 System Manufacturer: ‘Dell Inc.’, System Model: ‘Studio 1555’.
2011-09-19 22:12:08.210 spid52 Authentication mode is MIXED.
2011-09-19 22:12:08.220 spid52 Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Log\ERRORLOG’.
2011-09-19 22:12:08.220 spid52 The error log has been reinitialized. See the previous log for older entries.
2011-09-19 22:12:29.830 spid52 DBCC TRACEON 3502, server process ID (SPID) 52. This is an informational message only; no user action is required.
2011-09-19 22:13:09.050 spid7s Checkpointing and truncating dbid 1 (master).
2011-09-19 22:13:09.060 spid7s Ckpt dbid 1 started (8)
2011-09-19 22:13:09.060 spid7s About to log Checkpoint begin.
2011-09-19 22:13:09.100 spid7s Ckpt dbid 1 phase 1 ended (8)
2011-09-19 22:13:09.130 spid7s About to log Checkpoint end.
2011-09-19 22:13:09.140 spid7s Ckpt dbid 1 complete
2011-09-19 22:13:09.140 spid7s Checkpointing and truncating dbid 2 (tempdb).
2011-09-19 22:13:09.140 spid7s About to log Checkpoint begin.
2011-09-19 22:13:09.160 spid7s About to log Checkpoint end.
2011-09-19 22:13:09.200 spid7s No checkpoint needed on dbid 3 (model).
2011-09-19 22:13:09.200 spid7s Checkpointing and truncating dbid 4 (msdb).
2011-09-19 22:13:09.220 spid7s Ckpt dbid 4 started (8)
2011-09-19 22:13:09.220 spid7s About to log Checkpoint begin.
2011-09-19 22:13:09.220 spid7s Ckpt dbid 4 phase 1 ended (8)
2011-09-19 22:13:09.260 spid7s About to log Checkpoint end.
2011-09-19 22:13:09.310 spid7s Ckpt dbid 4 complete
2011-09-19 22:13:09.350 spid7s No checkpoint needed on dbid 5 (LiteSpeedLocal).
2011-09-19 22:13:09.360 spid7s No checkpoint needed on dbid 6 (LiteSpeedCentral).
2011-09-19 22:13:09.730 spid7s No checkpoint needed on dbid 8 (AdventureWorksDW2008R2).
2011-09-19 22:13:09.780 spid7s No checkpoint needed on dbid 9 (AdventureWorksLT2008R2).
2011-09-19 22:13:09.910 spid7s Checkpointing and truncating dbid 10 (AdventureWorks).
2011-09-19 22:13:09.910 spid7s Ckpt dbid 10 started (8)
2011-09-19 22:13:09.930 spid7s About to log Checkpoint begin.
2011-09-19 22:13:09.930 spid7s Ckpt dbid 10 phase 1 ended (8)
2011-09-19 22:13:09.930 spid7s About to log Checkpoint end.
2011-09-19 22:13:09.980 spid7s Ckpt dbid 10 complete
2011-09-19 22:13:10.000 spid7s No checkpoint needed on dbid 11 (AdventureWorksDW).
2011-09-19 22:13:10.030 spid7s Checkpointing and truncating dbid 12 (AdventureWorksLT).
2011-09-19 22:13:10.030 spid7s Ckpt dbid 12 started (8)
2011-09-19 22:13:10.060 spid7s About to log Checkpoint begin.
2011-09-19 22:13:10.070 spid7s Ckpt dbid 12 phase 1 ended (8)
2011-09-19 22:13:10.070 spid7s About to log Checkpoint end.
2011-09-19 22:13:10.120 spid7s Ckpt dbid 12 complete
2011-09-19 22:13:10.150 spid7s Checkpointing and truncating dbid 13 (AdventureWorks_11).
2011-09-19 22:13:10.150 spid7s Ckpt dbid 13 started (8)
2011-09-19 22:13:10.150 spid7s About to log Checkpoint begin.
2011-09-19 22:13:10.170 spid7s Ckpt dbid 13 phase 1 ended (8)
2011-09-19 22:13:10.170 spid7s About to log Checkpoint end.
2011-09-19 22:13:10.210 spid7s Ckpt dbid 13 complete
2011-09-19 22:13:10.220 spid7s Checkpointing and truncating dbid 14 (AdventureWorks_tstst).
2011-09-19 22:13:10.220 spid7s Ckpt dbid 14 started (8)
2011-09-19 22:13:10.230 spid7s About to log Checkpoint begin.
2011-09-19 22:13:10.230 spid7s Ckpt dbid 14 phase 1 ended (8)
2011-09-19 22:13:10.240 spid7s About to log Checkpoint end.
2011-09-19 22:13:10.280 spid7s Ckpt dbid 14 complete
2011-09-19 22:13:10.300 spid7s No checkpoint needed on dbid 15 (AdventureWorks_testst).
2011-09-19 22:13:10.320 spid7s No checkpoint needed on dbid 16 (tstrestore).
2011-09-19 22:13:10.350 spid7s Checkpointing and truncating dbid 17 (ReportServer).
2011-09-19 22:13:10.350 spid7s Ckpt dbid 17 started (8)
2011-09-19 22:13:10.350 spid7s About to log Checkpoint begin.
2011-09-19 22:13:10.370 spid7s Ckpt dbid 17 phase 1 ended (8)
2011-09-19 22:13:10.370 spid7s About to log Checkpoint end.
2011-09-19 22:13:10.420 spid7s Ckpt dbid 17 complete
2011-09-19 22:13:10.450 spid7s Checkpointing and truncating dbid 18 (ReportServerTempDB).
2011-09-19 22:13:10.450 spid7s Ckpt dbid 18 started (8)
2011-09-19 22:13:10.480 spid7s About to log Checkpoint begin.
2011-09-19 22:13:10.500 spid7s Ckpt dbid 18 phase 1 ended (8)
2011-09-19 22:13:10.500 spid7s About to log Checkpoint end.
2011-09-19 22:13:10.550 spid7s Ckpt dbid 18 complete
2011-09-19 22:13:10.570 spid7s Checkpointing and truncating dbid 19 (3014_demo).
2011-09-19 22:13:10.570 spid7s Ckpt dbid 19 started (8)
2011-09-19 22:13:10.580 spid7s About to log Checkpoint begin.
2011-09-19 22:13:10.590 spid7s Ckpt dbid 19 phase 1 ended (8)
2011-09-19 22:13:10.600 spid7s About to log Checkpoint end.
2011-09-19 22:13:10.630 spid7s Ckpt dbid 19 complete
2011-09-19 22:13:10.630 spid7s Checkpointing and truncating dbid 32767 (mssqlsystemresource).
2011-09-19 22:13:11.160 spid14s Service Broker manager has shut down.
2011-09-19 22:13:11.250 spid7s Skipping CleanShutdownDB() checkpoint on dbid 19 (3014_demo) with 0 freedOutcomes.
2011-09-19 22:13:11.640 spid7s Skipping CleanShutdownDB() checkpoint on dbid 18 (ReportServerTempDB) with 0 freedOutcomes.
2011-09-19 22:13:11.740 spid7s Skipping CleanShutdownDB() checkpoint on dbid 17 (ReportServer) with 0 freedOutcomes.
2011-09-19 22:13:11.850 spid7s Ckpt dbid 16 started (8)
2011-09-19 22:13:11.850 spid7s About to log Checkpoint begin.
2011-09-19 22:13:11.870 spid7s Ckpt dbid 16 phase 1 ended (8)
2011-09-19 22:13:11.890 spid7s About to log Checkpoint end.
2011-09-19 22:13:11.920 spid7s Ckpt dbid 16 complete
2011-09-19 22:13:12.050 spid7s Skipping CleanShutdownDB() checkpoint on dbid 15 (AdventureWorks_testst) with 0 freedOutcomes.
2011-09-19 22:13:12.180 spid7s Skipping CleanShutdownDB() checkpoint on dbid 14 (AdventureWorks_tstst) with 0 freedOutcomes.
2011-09-19 22:13:12.280 spid7s Skipping CleanShutdownDB() checkpoint on dbid 13 (AdventureWorks_11) with 0 freedOutcomes.
2011-09-19 22:13:12.390 spid7s Skipping CleanShutdownDB() checkpoint on dbid 12 (AdventureWorksLT) with 0 freedOutcomes.
2011-09-19 22:13:12.480 spid7s Ckpt dbid 11 started (8)
2011-09-19 22:13:12.480 spid7s About to log Checkpoint begin.
2011-09-19 22:13:12.510 spid7s Ckpt dbid 11 phase 1 ended (8)
2011-09-19 22:13:12.530 spid7s About to log Checkpoint end.
2011-09-19 22:13:12.560 spid7s Ckpt dbid 11 complete
2011-09-19 22:13:12.690 spid7s Skipping CleanShutdownDB() checkpoint on dbid 10 (AdventureWorks) with 0 freedOutcomes.
2011-09-19 22:13:12.860 spid7s Ckpt dbid 9 started (8)
2011-09-19 22:13:12.860 spid7s About to log Checkpoint begin.
2011-09-19 22:13:12.900 spid7s Ckpt dbid 9 phase 1 ended (8)
2011-09-19 22:13:12.920 spid7s About to log Checkpoint end.
2011-09-19 22:13:12.950 spid7s Ckpt dbid 9 complete
2011-09-19 22:13:13.180 spid7s Ckpt dbid 8 started (8)
2011-09-19 22:13:13.180 spid7s About to log Checkpoint begin.
2011-09-19 22:13:13.200 spid7s Ckpt dbid 8 phase 1 ended (8)
2011-09-19 22:13:13.220 spid7s About to log Checkpoint end.
2011-09-19 22:13:13.270 spid7s Ckpt dbid 8 complete
2011-09-19 22:13:13.410 spid7s Ckpt dbid 6 started (8)
2011-09-19 22:13:13.410 spid7s About to log Checkpoint begin.
2011-09-19 22:13:13.460 spid7s Ckpt dbid 6 phase 1 ended (8)
2011-09-19 22:13:13.480 spid7s About to log Checkpoint end.
2011-09-19 22:13:13.510 spid7s Ckpt dbid 6 complete
2011-09-19 22:13:13.640 spid7s Ckpt dbid 5 started (8)
2011-09-19 22:13:13.640 spid7s About to log Checkpoint begin.
2011-09-19 22:13:13.660 spid7s Ckpt dbid 5 phase 1 ended (8)
2011-09-19 22:13:13.670 spid7s About to log Checkpoint end.
2011-09-19 22:13:13.710 spid7s Ckpt dbid 5 complete
2011-09-19 22:13:13.880 spid7s Ckpt dbid 4 started (8)
2011-09-19 22:13:13.880 spid7s About to log Checkpoint begin.
2011-09-19 22:13:13.880 spid7s Ckpt dbid 4 phase 1 ended (8)
2011-09-19 22:13:13.890 spid7s About to log Checkpoint end.
2011-09-19 22:13:13.890 spid7s Ckpt dbid 4 complete
2011-09-19 22:13:14.000 spid7s Skipping CleanShutdownDB() checkpoint on dbid 3 (model) with 0 freedOutcomes.
2011-09-19 22:13:14.140 spid7s Ckpt dbid 1 started (8)
2011-09-19 22:13:14.140 spid7s About to log Checkpoint begin.
2011-09-19 22:13:14.140 spid7s Ckpt dbid 1 phase 1 ended (8)
2011-09-19 22:13:14.140 spid7s About to log Checkpoint end.
2011-09-19 22:13:14.140 spid7s Ckpt dbid 1 complete
2011-09-19 22:13:14.140 spid7s SQL Server is terminating in response to a ‘stop’ request from Service Control Manager. This is an informational message only. No user action is required.
2011-09-19 22:13:14.180 spid7s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
Sooo….SQL Server is making sure that a CHECKPOINT is issued on all the Databases as mentioned above before Shutting down the SQL Instance.( Which plays a huge and a very critical role in crash recovery when SQL Instance has been started)

Scenario 2- Creating a new User Database:

I’ve created a Database called “Checkpoint_tst”. See the Error Log contents below.

2011-09-19 22:18:58.020 spid53 Microsoft SQL Server 2008 R2 (SP1) – 10.50.2425.0 (X64)
Apr 6 2011 21:03:25
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
2011-09-19 22:18:58.020 spid53 (c) Microsoft Corporation.
2011-09-19 22:18:58.020 spid53 All rights reserved.
2011-09-19 22:18:58.020 spid53 Server process ID is 9120.
2011-09-19 22:18:58.020 spid53 System Manufacturer: ‘Dell Inc.’, System Model: ‘Studio 1555’.
2011-09-19 22:18:58.020 spid53 Authentication mode is MIXED.
2011-09-19 22:18:58.020 spid53 Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Log\ERRORLOG’.
2011-09-19 22:18:58.020 spid53 The error log has been reinitialized. See the previous log for older entries.
2011-09-19 22:18:58.020 spid53 DBCC TRACEON 3502, server process ID (SPID) 53. This is an informational message only; no user action is required.
2011-09-19 22:20:11.500 spid53 Ckpt dbid 3 started (8)
2011-09-19 22:20:11.500 spid53 About to log Checkpoint begin.
2011-09-19 22:20:11.510 spid53 Ckpt dbid 3 phase 1 ended (8)
2011-09-19 22:20:11.510 spid53 About to log Checkpoint end.
2011-09-19 22:20:11.520 spid53 Ckpt dbid 3 complete
2011-09-19 22:20:11.790 spid53 Starting up database ‘checkpoint_tst’.
2011-09-19 22:20:11.840 spid53 Ckpt dbid 20 started (0)
2011-09-19 22:20:11.840 spid53 About to log Checkpoint begin.
2011-09-19 22:20:11.840 spid53 Ckpt dbid 20 phase 1 ended (0)
2011-09-19 22:20:11.840 spid53 About to log Checkpoint end.
2011-09-19 22:20:11.840 spid53 Ckpt dbid 20 complete
2011-09-19 22:20:55.860 spid11s About to log Checkpoint begin.
2011-09-19 22:20:55.870 spid11s About to log Checkpoint end.

(22 row(s) affected)

dbid= 3 is MODEL Database. You can pull that info by

select name,database_id from sys.databases
where database_id = 3

Hope this is informative!

Backup Internals – Trace Flag 3014

Trace Flag 3014–  This is one of the undocumented Trace flags in SQL Server, which basically gives a detailed information(Well, This might not be useful in most of the cases) regarding File Creation, Padding and much more related Info while you are taking a Backup of your Database.

Do you really need this Info? IMHO, 95% of the cases, i would say NO. I consider this as Just an FYI kind of Information. When i try to troubleshoot Backup/Restore Issues, I rather enable Trace Flags 3004 and 3014 along with 3605(to enable logging of all the entries in our Error Log) instead of Just enabling Single Flag(Make sure you disable these Flags once you are done with diagnostics).

Let’s See what exactly get’s logged when i Enable these Trace Flags and Take a Full Database Backup.

Tadaaaaaaaaaa!
2011-09-15 22:00:40.470 spid54 BackupStream(0): Starting MSDA of size 384 extents
2011-09-15 22:00:40.470 spid54 Started file C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\3014_demo.mdf
2011-09-15 22:00:40.780 spid54 Completed file C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\3014_demo.mdf
2011-09-15 22:00:40.790 spid54 Padding MSDA with 327680 bytes
2011-09-15 22:00:40.790 spid54 BackupStream(0): Total MSDA: 384 extents
2011-09-15 22:00:40.860 Backup Database backed up. Database: 3014_demo, creation date(time): 2011/09/15(21:46:19), pages dumped: 3100, first LSN: 163:1808:37, last LSN: 163:1824:1, number of dump devices: 1, device information: (FILE=2, TYPE=DISK: {‘D:\Backs\3014_full.bak’}).

So..Basically you can see exact times while starting backup stream , how many Extents being affected, what file(s) etc…Again This is Just an FYI information in most of the cases while you are dealing with Backups, where as the Trace Flag 3004(see here) is Extremely Useful while investigating DB Restore Issues. YMMV!