Microsoft SQL Server 2008 Service Pack 4 (SP4) and SQL Server 2008 R2 Service Pack 3 (SP3) are now available for download.
SQL Server 2008 SP4 and SQL Server 2008 R2 SP3 have been released
02 Thursday Oct 2014
Posted SQL Server
in02 Thursday Oct 2014
Posted SQL Server
inMicrosoft SQL Server 2008 Service Pack 4 (SP4) and SQL Server 2008 R2 Service Pack 3 (SP3) are now available for download.
10 Monday Mar 2014
Posted MS SQL Tips, SQL Script
inMany 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…!
28 Friday Feb 2014
Posted SQL Server
inHappy 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
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.
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.
![]() |
---|
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.
![]() |
---|
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. |
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)
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.
![]() |
---|
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.
![]() |
---|
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'.
![]() |
---|
Refer technet article Create a Database Snapshot to know more about Database Snapshot. |
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.
26 Tuesday Nov 2013
Posted SQL Server
inAs 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!
12 Tuesday Nov 2013
Posted SQL Server
inSQL 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
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