Find an object name in all databases.


, ,

I have one user defined function , but i couldn’t find where the function is and where the function is using.. anyone pls help me to overcome this one.?

The below script will help you find all the references within each database.

SET @ObjectName = N'split' --Give your function/object name
Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id)
,SourceObject = OBJECT_NAME(sed.referencing_id)
,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME())
,ReferencedSchema = ISNULL(sre.referenced_schema_name,
,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ''.'' + OBJECT_NAME(sed.referencing_id), ''OBJECT'') sre
WHERE sed.referenced_entity_name like ''' + @ObjectName + ''' AND sre.referenced_entity_name like ''' + @ObjectName + '''';

EXEC sp_MSforeachdb  @MyQuery

If you just want to know the database name where the object located in with out reference details, then you can make use of below script.

EXEC sp_MSforeachdb 'use ? if exists(SELECT * from sys.objects where  name = ''split'' ) begin; print ''?''; end;';




The system procedure sp_MSforeachdb is neither documented nor officially supported by Microsoft.
Hope this helps…!

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


, , ,

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

find last day of the month using EOMONTH


, ,

In my previous blog post i have demonstrated how to find first and last date of any month. SQL Server 2012 has introduced a new date function EOMONTH to find the last day of the month.

EOMONTH ( start_date [, month_to_add ] )

    Date expression specifying the date for which to return the last day of the month.
    Optional integer expression specifying the number of months to add to start_date.

EOMONTH without the month_to_add parameter to find last day of current month
EOMONTH - CurrentMonth

EOMONTH with the month_to_add parameter to find last day of last and next month
EOMONTH - last&previousMonths

SQL Server doesn’t provide any function to find start of the month, but we can make use of EOMONTH function to find start date of any month.
EOMONTH - StartofTheMonth

EOMONTH function is available in SQL Server 2012 and higher, for SQL Server 2008 R2 and below you can refer to this blog post.

Determining Drive letter database files use


, ,

Many a times i need to move database files to new drive letter just to avoid existing drive going out of space, to determine which database files a particular drive host, you can  make use of catalog view sys.master_files as below query.

SELECT  DB_NAME(database_id) [Database Name],  
		Name [Logical File Name] , 
		physical_name [Physical File Name] , 
		Case When type_desc = 'ROWS' Then 'Data' Else 'Log' End [type]  
FROM sys.master_files 
WHERE LEFT(physical_name , 1) = 'C'

You will get result set as below.

Hope this helps…!

Thank you all for 1000+ views!


First off, I’d like to say a big thank you for visiting my blog for more than 1000 times:

I’ve had hits from 65 countries around the world, top three being, India with 347, US with 310, UK 60.

I know that other bloggers have 1,000 views per day but for me, reaching 1,000 has made me happy and at the same time this milestone has motivated me to keep updating my blog.

Here are some blog stats.

top postscountry breakup


Thank you all very much…!.

Quickly search SQL Server Error Log



As a Part of my routine DBA activity, I have to go through sp_readerrorlog to locate errors if any. In our production environment we have have Both Failed and Successful logins auditing enabled. Hence if i run sp_readerrorlog without any filters it would return me huge no of rows and go through each and every row is a time consuming and repetitive activity.
Here i would like to share how we can quickly go through Error Log with few filters.

sp_readerrorlog takes 4 parameters to narrow down our search.

  1. @p1 – Log file we want to read, 0 = current error log, 1 = Archive #1, 2 = Archive #2 etc..
  2. @p2 – 1 or null = SQL Server error log, 2 = SQL Agent error log.
  3. @p3 – string which we want to search.
  4. @p3 – string to further refine our search.

I basically filter one day error log as below.

EXEC sp_readerrorlog 0 , null , '2014-04-13 10:00' , '2014-04-14 10:00'

You can also refine your search just to check database backups like below.

EXEC sp_readerrorlog 0 , 1 ,   'Database Mirroring' , 'error'

As sp_readerrorlog is undocumented stored procedure to review error log, you will not find any official references in Books online.

Keep your current error log under control just by recycling it, so that you can quickly retrieve its output, refer sp_cycle_errorlog.

Hope this helps…!

Quick start of SSMS


, ,

Instead of starting SSMS through Start->Programs-> or through a desktop shortcut, one can start the SSMS in a much faster way by

1. Start->RUN or WINDOWS key+R
2. Type SqlWb.exe for 2005 or SSMS.exe for later versions
SSMS Quick start2
3. Press enter and SSMS is opened up for you.

If you want to take this further by making use of more options such as to directly connect to the server by just mentioning server name like
SSMS Quick start localhost

If you like to know what other options are available, you can do that as follows.
SSMS Quick start options

this gives you a screen as below.
SSMS Quick start option list

Hope this helps…!

Find SQL Server deatils


, , ,

Sometimes DBA’s need to quickly extract few SQL Server details like
SQL Server Name
Instance Name
IP Address
TCP Port
Product Name
SP Level
Product Version

Many a times we extract this information by running multiple queries or toggling multiple windows, this T-SQL script will help you to extract same information through a single execution.
You can download T-SQL script from Microsoft gallery.

Basically in this script I have demonstrated how to use SERVERPROPERTY and CONNECTIONPROPERTY functions available in SQL Server 2008 and higher.

Hope this helps…!

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


, ,

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
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101) AS LastBackUpDate
sys.sysdatabases db
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name =

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

Hope this helps…!

SQL Server: Instant File Initialization Exceptions


, , ,

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


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.

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;
--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
(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 10GB)
(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
(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 1GB)
(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

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.

DBCC TRACEON(1806 , -1)
(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 10GB)
(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;
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Test Certificate';

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

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:\')

And errorlog result indicates zero initialization of data files.

2014-02-26 19:27:46.890 spid53       Zeroing C:\ from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:27:46.890 spid53       Zeroing completed on C:\
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.



Get every new post delivered to your Inbox.

Join 208 other followers