SQL Server 2008 SP4 and SQL Server 2008 R2 SP3 have been released

Tags

SQL Server, SQL Server 2008 R2 SP3, SQL Server 2008 SP4, SQL Server Service Pack

Microsoft SQL Server 2008 Service Pack 4 (SP4) and SQL Server 2008 R2 Service Pack 3 (SP3) are now available for download.

Download Service Pack 4 for Microsoft® SQL Server® 2008 

Download Service Pack 3 for Microsoft® SQL Server® 2008 R2

SQL Server – Finding Last Backup & Restore Date for All Database

Tags

last backup, last restore, SQL Server

Many a times we need to find when my database last backed up or when it was last restored.
Below is the quick script i use to find last backup and restore dates.

Script to find last backup date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name = db.name
GROUP BY
db.Name

Script to find last restored date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(rh.restore_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = db.name
GROUP BY db.Name

Hope this helps…!

SQL Server: Instant File Initialization Exceptions

Tags

IFI, Instant File Initialization, SQL Server, Trace flag 1806

Happy to inform all my blog readers this particular post has won me SILVER in TechNet Guru Award under SQL Server General and Database Engine Technical Guru – February 2014.
Visit TechNet Guru Awards – February 2014 for winner list

Introduction

DBA’s are always on the lookout for Performance Quick Wins to improve the SQL Server performance under their care, one of the highly recommended performance booster is to have Instant File Initialization (IFI) enabled. IFI is a feature that’s seemingly simple;it allows file allocation requests to skip zero initialization on creation as a result, file allocation requests can occur instantly.

I will not get into the details of IFI, we already have good amount of articles on this topic and I personally recommend Instant Initialization – What, Why and How? by Kimberly Tripp and How and Why to Enable Instant File Initialization by Cindy Gross and Denzil Ribeiro.
Basic intention of this article is to demonstrate the Instant File initialization exceptions, which means SQL Server engine will go ahead with zeroing out disk space inspite of having IFI enabled.

Performing the test

Let me check if SQL Server is able to use IFI in my environment by creating a database. If it is enabled you will see message in SQL Server error log for the zeroing out of the log files, and if it is not enabled, you will see messages for the zeroing out for both log files and data files.

I am creating a dummy database with 10 GB data file and 1 GB log file total size would be 11 GB.

Caution

To see which files are being zeroed out, this article uses the undocumented trace flag 3004. Undocumented means it is safe to use in production but any support related to this command cannot be claimed from Microsoft.It also means that any change can be made to this command without officially notifying end user so unexpected results might come if used on same environment after some time. Although their usage is safe, keep in mind to use it in test environments only to avoid any impact to production processes. Trace flag 3004 shows information about backups and file creations, Trace flag 3605 redirects the output to the SQL error log.

USE master;

GO

--Set Trace Flags 3004 and 3605 to On.

DBCC TRACEON(3004,-1);

DBCC TRACEON(3605,-1);

  

--Create a dummy database to see what output is sent to the SQL Server Error Log

CREATE DATABASE IFITest

ON  PRIMARY

(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 10GB)

 LOG ON

(NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf',SIZE = 1GB)

It took 4 sec to create above database on my laptop, let me check SQL errorlog.

2014-02-26 19:12:36.090 spid56       Zeroing c:\IFITest_log.ldf from page 0 to 131072 (0x0 to 0x40000000)
2014-02-26 19:12:40.190 spid56       Zeroing completed on c:\IFITest_log.ldf
2014-02-26 19:12:40.300 spid56       Starting up database 'IFITest'.
2014-02-26 19:12:40.320 spid56       FixupLogTail(progress) zeroing c:\IFITest_log.ldf from 0x5000 to 0x6000.
2014-02-26 19:12:40.320 spid56       Zeroing c:\IFITest_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2014-02-26 19:12:40.330 spid56       Zeroing completed on c:\IFITest_log.ldf

As you can see in above errorlog result, only log file is zeroing out it confirms IFI is enabled in my environment.

Just to cross verify, I create above database by interchanging data file size and log file size but total size of the database remains the same i.e. 11 GB.

Use Master

Go

DROP DATABASE IFITest;

Go

CREATE DATABASE IFITest ON 
PRIMARY

(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 1GB)

 LOG ON

(NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf',SIZE = 10GB)

As expected it took me 1 min 35 sec to create database, this also confirms IFI is in action in my environment.

Note
If you want to check if Instant File Initialization is enabled in your environment then I would recommend you to read http://www.johnsansom.com/sqlserver-instant-file-initialization.

Instant File Initialization Exception Scenarios

Now I demonstrate you few different scenarios wherein IFI has to zero out data files in spite of having IFI enabled.

Scenario 1: Enable Trace Flag 1806.

Turning on trace flag 1806 will disable instant file initialization.

DROP DATABASE IFITest;

GO

DBCC TRACEON(1806 , -1)

go

CREATE DATABASE IFITest ON 
PRIMARY

(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 10GB)

 LOG ON

(NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf',SIZE = 1GB)

DBCC TRACEOFF(1806 , -1)

Let me check errorlog.

2014-02-26 19:05:41.440 spid56       DBCC TRACEON 1806, server process ID (SPID) 56. This is an informational message only; no user action is required.
2014-02-26 19:05:49.240 spid56       Zeroing C:\IFITest.mdf from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:06:32.070 spid56       Zeroing completed on C:\IFITest.mdf
2014-02-26 19:06:32.160 spid56       Zeroing c:\IFITest_log.ldf from page 0 to 131072 (0x0 to 0x40000000)
2014-02-26 19:06:37.190 spid56       Zeroing completed on c:\IFITest_log.ldf
2014-02-26 19:06:37.320 spid56       Starting up database 'IFITest'.
2014-02-26 19:06:37.330 spid56       FixupLogTail(progress) zeroing c:\IFITest_log.ldf from 0x5000 to 0x6000.
2014-02-26 19:06:37.330 spid56       Zeroing c:\IFITest_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2014-02-26 19:06:37.340 spid56       Zeroing completed on c:\IFITest_log.ldf

As you can see in above result set, enabling trace flag 1806 forces SQL Server engine to zero out both data and log files and it took 48 sec to create database.

Scenario 2: Transparent Data Encryption (TDE).

When you have enable Transparent Data Encryption on your database then SQL Server Engine will ignore your IFI settings and go ahead with zero initialization of data files.

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';

go

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Test Certificate';

go

USE IFITest;

GO

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

ALTER DATABASE IFITest SET ENCRYPTION ON;

ALTER DATABASE IFITest

MODIFY FILE(NAME = 'IFITest' , SIZE = 15GB)

In my above query I encrypted the database and then I altered data file from 10GB to 15GB, let us see errorlog result.

2014-02-26 19:16:28.770 spid56       Setting database option ENCRYPTION to ON for database IFITest.
2014-02-26 19:16:28.770 spid18s      Beginning database encryption scan for database 'IFITest'.
2014-02-26 19:16:42.140 spid56       Zeroing C:\IFITest.mdf from page 1310720 to 1966080 (0x280000000 to 0x3c0000000)
2014-02-26 19:17:06.050 spid56       Zeroing completed on C:\IFITest.mdf

As you can see in result set, SQL Server Engine is zeroed out data file and it took 24 sec to extend data file and without encryption same query took only 1 sec.

Note
Refer technet article Transparent Data Encryption (TDE) to know more about Transparent Data Encryption.

Scenario 3: CHECKDB.

One more case wherein SQL Server Engine has to zero initialize data files is when we run DBCC CHECKDB.

Use IFITest

Go

DBCC CHECKDB

Below errorlog result clearly shows SQL Server Engine is zeroing out data files.

2014-02-26 19:25:08.360 spid53       Zeroing C:\IFITest.mdf:MSSQL_DBCC9 from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:25:08.360 spid53       Zeroing completed on C:\IFITest.mdf:MSSQL_DBCC9
2014-02-26 19:25:08.650 spid53       DBCC CHECKDB (IFITest) executed by \********* found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000013:00000040:0001 and first LSN = 00000013:0000003f:0001.

Note
Refer technet article DBCC CHECKDB to know more about DBCC CHECKDB.

Scenario 4: Database Snapshot.

When we create database snapshot, SQL Server Engine go ahead and zero initializes data file.

CREATE Database IFITest_SS

ON (Name= 'IFITest' , Filename ='C:\IFITest_data.ss')

AS SNAPSHOT OF [IFITest]

And errorlog result indicates zero initialization of data files.

2014-02-26 19:27:46.890 spid53       Zeroing C:\IFITest_data.ss from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:27:46.890 spid53       Zeroing completed on C:\IFITest_data.ss
2014-02-26 19:27:46.940 spid53       Starting up database 'IFITest_SS'.

Note
Refer technet article Create a Database Snapshot to know more about Database Snapshot.

---

Conclusion

Basically, with Instant File Initialization turned on it cuts out database creation or data file growth time, most DBA’s are aware of this fact, but most of us not aware of the fact that in some exceptional cases Instant File Initialization has to zero initializes data files, I have demonstrated four such scenarios in this article.

References

• How and Why to Enable Instant File Initialization by Cindy Gross and Denzil Ribeiro
• Instant Initialization – What, Why and How? by Kimberly Tripp
• SQL Server Instant File Initialization (PQW) by John Sansom
• Do you have Instant File Initialization? by Tibor Karaszi
• Misconceptions around instant file initialization by Paul Randal
• http://technet.microsoft.com/en-us/library/bb934049.aspx

Find Windows and SQL Server Last Restart time

Tags

sp_readerrorlog, SQL DMV, SQL Server, SQL Server last restart, sql server restart

As a routine DBA activity I was analyzing sp_readerrorlog this morning and surprised to see that SQL Server has restarted early this morning.

Here are few different ways that you can make use of to tell when SQL Server was last restarted.

Method 1: 

sp_readerrorlog 0,1,'Copyright (c)'

Method 2:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

sqlserver_start_time
———————–
2013-11-26 01:16:10.757

(1 row(s) affected)

Method 3:

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1

login_time
———————–
2013-11-26 01:16:24.127

(1 row(s) affected)

Method 4: 

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

create_date
———————–
2013-11-26 01:16:13.023

(1 row(s) affected)

Method 5:

Right click on the server name in SSMS and select Reports > Standard Reports > Server Dashboard and you will get a report similar to below report.

Method 6: 

start -> run -> eventvwr

Chose Windows log -> Application
Open filter and enter Event Sources (MSSQLSERVER) and Event Id (17163)

Here is the output of above filter.

 

Here are a few different ways that you can tell when Windows last rebooted.

Method 1:

Start Task Manager->Performance
Screen below gives you total up time since last restart.

Method 2:

start -> run -> eventvwr
Choose: Windows Log – > System
Open filter and following Event Id’s
6005 to see when the Event Log service was started. It gives the message “The Event log service was started“.
6006 to see when there was a clean shutdown. It gives the message “The Event log service was stopped“.
6008 to see when there was a dirty shutdown. It gives the message “The previous system shutdown at time on date was unexpected“.
6009 is logged during every boot.

Here is the output of above filter.

Hope this helps!

SERVERPROPERTY – one stop shop for all SQL Server properties.

Tags

Microsoft SQL Server 2005, SERVERPROPERTY, SQL Server, SQL Server 2005

SQL Server has little known yet powerful function called SERVERPROPERTY which allows you to extract tremendous amount of information about your SQL Server.

Syntax
SERVERPROPERTY(propertyname)

Below are some example of SERVERPROPERTY.

SELECT 
SERVERPROPERTY('Edition') AS Edition ,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion')AS ProductVersion ,
SERVERPROPERTY('Collation') AS EngineEdition ,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled ,
SERVERPROPERTY('IsSingleUser') AS IsSingleUser ,
SERVERPROPERTY('MachineName') AS MachineName

Result

Check here for full list of parameters.

Why SERVERPROPERTY Why not @@VERSION
Most of the time I have seen DBA’s using @@VERSION  to extract SQL Server properties, but be aware that in some cases using @@VERSION would mislead you, take a below example.
If you run @@VERSION on SQL Server 2005.

SELECT @@VERSION

Result
Microsoft SQL Server 2005 – 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1)

Here Service pack 1 is of operating system not of SQL Server,instead you can make use of SERVERPROPERTY to identify service pack for SQL Server.

SELECT SERVERPROPERTY (‘ProductLevel‘)

Result
SP4

This issues is been fixed in SQL Server 2008 onwards, 2008 onwards @@VERSION returns SP of both SQL and OS.

SELECT @@VERSION

Result
Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1)

New Parameters
In SQL Server 2012 two new parameters are added to SERVERPROPERTY function namely InstanceDefaultDataPath and InstanceDefaultLogPath.
InstanceDefaultDataPath would return you default data directory and InstanceDefaultLogPath would return you log directory.

Hope this helps u!
Praveen