Thursday, August 30, 2012

SQL Server: Script to Start and Stop a New Trace without Using Profiler GUI


SQL Server Profiler is an useful tool, but basically, important part of profiler is trace which this graphical interface shows. Trace data can be collected and saved with in a file, even without using SQL Server Profiler graphical interface.
To do so you need a script that will perform this task. Good thing is that, SQL Server Profiler helps us to create this script, through following path.

Now, only you need to change it according to your requirements. Here is a complete script for auto trace which we use on production servers, to capture quires taking time more than 5 seconds to execute. Script can be used in a job to execute on daily bases. 
 
To stop this auto trace, you just need to change its status to STOP and then CLOSE. Keep in mind to close a trace, you must stop it first.

Monday, August 27, 2012

SQL Server Community: Microsoft Virtual Academy


Recently, I have joined Microsoft Virtual Academy and found it an amazing, free, trusted and up to date source of learning about Microsoft Cloud Technologies. Being a student of SQL Server, I was found 5 interesting tracks. You can learn some advance topics about SQL Server in a very efficient way. Not only you can learn, but you can assess your learning through self-assessments tests.

Everyone is eligible, and anyone can learn, totally free of charges.
Currently 5 SQL Server tracks with around 50 videos are available.


Friday, August 24, 2012

SQL Server: Why We Should Avoid NOLOCK Table Hint in DELETE/UPDATE Queries

Recently, I was asked to review, already written stored procedures for optimization purpose. During this review process I have found that a group of developers is regularly committing a big mistake. This group of developers believes that table hint NOLOCK is used to execute queries quickly, as this hint will avoid placing any lock on target table records and it can you used in any query. Even they have applied this NOLOCK in DML statements.
WRONG
First thing, NOLOCK hint means, it will not take care of any lock (instead of placing lock). It will return data, that could be dirty (NOT YET COMMITTEED by other transactions). We can use this table hint to get results quickly when we are dead sure that dirty data is TOTALLY bearable.
In DELETE/UPDATE queries it should be totally avoided as it can produce junk results. Let’s prove.
In following example, we need to correct discount column of SalesOrderDetail, but according to discount provided in lookup table of SpecialOffer. Before we execute our update statement (Statement #2 in Transaction# 2), someone has accidently changed SpecialOffer, but good thing is that, he has not committed these changes yet. But as we have placed NOLOCK hint in our Statement #2 in Transaction# 2, it will change data according to dirty data, though, later on transaction#1 is rolledback.


Thursday, August 23, 2012

SQL Server: Query to Find Upcoming birthdays for Current Week

 A common query, for Human Resource databases or different social sites is to find out employee/subscribers name who’s birthday is coming in near future i.e. (In current week, or in next given days).
To find out, whose birthday is coming in given number of days is bit simple.
--Create table variable to hold our test records
DECLARE  @Workers  TABLE (WorderName VARCHAR(50), DOB DATETIME)
--Insert test records
INSERT INTO @Workers
SELECT 'Ryan','1972-08-24 00:00:00' UNION ALL
SELECT 'James','1985-09-26 00:00:00' UNION ALL
SELECT 'Jasson','1983-08-25 00:00:00' UNION ALL
SELECT 'Tara','1991-09-24 00:00:00' UNION ALL
SELECT 'William','1992-08-19 00:00:00' UNION ALL
SELECT 'Judy','1989-09-23 00:00:00'
--Variable to provide requried number of days
DECLARE @InNextDays INT
SET @InNextDays = 3       
-- Query to find workers, whose birthday is in given number of days

SELECT  *
FROM    @Workers e
WHERE   1 =
CASE WHEN MONTH(GETDATE()) < MONTH(GETDATE() + @InNextDays)
     THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE() + @InNextDays)
            AND DAY(DOB) BETWEEN DAY(DATEADD(s, -1,
                                    DATEADD(mm, DATEDIFF(m, 0,
                                    GETDATE()) + 1, 0) + 1))
                     AND     DAY(GETDATE()
                                    + @InNextDays) THEN 1
               WHEN MONTH(DOB) = MONTH(GETDATE())
                    AND DAY(DOB) BETWEEN DAY(GETDATE()) + 1
                                 AND     DAY(GETDATE())
                                         + @InNextDays THEN 1
               ELSE 0
          END
     ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())
                    AND DAY(DOB) BETWEEN DAY(GETDATE()) + 1
                                 AND     DAY(GETDATE())
                                         + @InNextDays THEN 1
               ELSE 0
          END
END
 
And following query will help you to find out workers with birthday in current week.
-- Query to find workers, whose birthday is in current week

SELECT  *
FROM    @Workers e
WHERE   1 = CASE WHEN MONTH(GETDATE()) < MONTH(DATEADD(WK,
                                       DATEDIFF(WK, 0, GETDATE())+1,-1))
THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE()) + 1
            AND DAY(DOB) >= 1
            AND DAY(DOB) < DAY(DATEADD(WK,
                                 DATEDIFF(WK, 0, GETDATE())
                                       + 1, -1)) THEN 1
    WHEN MONTH(DOB) = MONTH(GETDATE())
            AND DAY(DOB) >= DAY(GETDATE())
            AND DAY(DOB) <= DAY(DATEADD(s,-1,DATEADD(mm,
                                                            DATEDIFF(m,0,GETDATE()),0))) THEN 1
     
       ELSE 0 END
 
ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())
            AND DAY(DOB) >= DAY(GETDATE())+1
            AND DAY(DOB) < DAY(DATEADD(WK,
                                 DATEDIFF(WK, 0, GETDATE())
                                       + 1, -1)) THEN 1
       ELSE 0
  END
END




Thursday, August 9, 2012

SQL Server: Applying Filter on sp_MSforeachDB


Working on multiple databases on a single instance, sometime you need to execute a query for each database and for that sp_MSforeachdb is the best choice.
Recently talking to my development team I came to know that a very few guys have idea about filter for sp_MSforeachDB.

For example, if I need to get database physical files information for each database on my instance, I will use following simple query
EXEC sp_MSforeachdb '
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'


BUT what if, I need to omit MSDB, TempDB and Model databases for this query. Now I have to apply filter. This can be achieved by simple IF statement.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'

You can even use ? sign in WHERE clause.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
       WHERE name  LIKE ''?%'' -- Only Files starting with DB name
END'

Output can be saved in tables (user, temporary) or table variables
DECLARE   @DatabasesSize TABLE
    (
      name VARCHAR(50),
      physical_name VARCHAR(500),
      state BIT,
      size INT
    )

INSERT  INTO @DatabasesSize
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'

Tuesday, August 7, 2012

SQL Server: Controlling SQL Server Log Information


For DBAs, SQL Server Log is the main source to troubleshoot problems related to SQL Server. It contains user-defined events and certain system events.  By default 6 files are created for an instance and are recycled once sql server is restarted or you can force for new sql server log file with following simple statement.
EXEC sp_cycle_errorlog

Number of SQL Server Log files can be increased up to 99, while minimum value is 6.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99

Or you can change these values through graphical interface, move your mouse pointer to SQL Server Log and right click to choose “Configure” option.
Not all the messages or errors are critical that should be followed, and somehow, quantity of such messages is quite large in a normal SQL Server Log file and finding messages and errors of critical nature are sometime a big problem itself. 
It can be managed by extracting necessary errors and messages and saving in a separate user defined table and later on it could be queried, or most DBAs like to send these messages and errors through HTML mail (This is a more appropriate way, as you need not to visit your SQL Server instance and necessary information can be found in your inbox).

Following is the script which can be used to extract necessary information from SQL Server Log and send through HTML mail.

Mail in you inbox would look like following.

Thursday, August 2, 2012

SQL Server : How to Keep Database Restore History

Recently, one of our DBA restored a database on live server, but with old backup accidently. Later on, we have found that it was hard to detect which backup was actually restored. To check, which backups we have created for database we have a perfect script, which you can find over here and here. But unfortunately no such script found anywhere to get restore history.
Here is a script, we have used to create a job, which will fetch restore related log entries from SQL Server Log and will archive it to a user created history table.

Output of history table will be as following.
.