FAQ : What all are the recommended events for DTA / ITW workload

If you have provided same workload without different events and columns DTA may give you different report. For eg. if you have not included Duration column, DTA will tune the workload in the order they appear in the workload. IF the workload contains duration column , DTA will tune the events in the descending order of the Duration. So to get better result from DTA included recommended events and columns.

Events Recommended
RPC:Completed
RPC:Starting
SP:StmtCompleted
SP:StmtStarting
SQL:BatchCompleted
SQL:BatchStarting
SQL:StmtCompleted
SQL:StmtStarting

FAQ : How to search for an object in all the databases

SQL Server 2005

CREATE TABLE #TEMP (TABLENAME SYSNAME, OBJECTNAME SYSNAME,TYPE CHAR(10))

INSERT INTO #TEMP

EXEC SP_MSFOREACHDB "SELECT '?' DATABASENAME, NAME,TYPE FROM ?.SYS.ALL_OBJECTS WHERE NAME='YourSearchingObjectName'"

SELECT * FROM #TEMP

DROP TABLE #TEMP

SQL Server 2000

CREATE TABLE #TEMP (TABLENAME SYSNAME, OBJECTNAME SYSNAME,TYPE CHAR(10))

INSERT INTO #TEMP

EXEC SP_MSFOREACHDB "SELECT '?' DATABASENAME, NAME,XTYPE FROM ?..SYSOBJECTS WHERE NAME='YourSearchingObjectName'"

SELECT * FROM #TEMP

DROP TABLE #TEMP


Note : Replace "YourSearchingObjectName" with the object name which you are searching in the select query

How to Audit or Bypass TRIGGER Execution Using CONTEXT_INFO()

When a TRIGGER executes, it may be useful to have the TRIGGER code know which Stored Procedure caused the action that is executing the TRIGGER code. It may be that there is a need to log the entry point, or perhaps, there is conditional logic in the TRIGGER that depends upon the entry point. In this paper, two different scenarios are addressed. First, How to Track Which Stored Procedure Fired the Trigger, and Second, How to Limit a TRIGGER to Fire ONLY from Certain Stored Prodedures. This approach has been tested with SQL Server 2005 and 2008. SQL Server 2000 requires a minor modification that is presented at the end of the paper.

Check my paper in MSDN Wiki page
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AuditOrBypassTriggerExecution

FAQ - How to use Stored Procedure in Select Query

Very common question in T-SQL Forums

The requirement is, need to use Stored procedure output (Exec SP) in Select query or in a Join. There is a method which may not be a recommended one. You can create a LoopBack Linked server to your own server and use OPENQUERY TO extract the data by EXECUTE Stored procedure.

LoopBack Linked Server

Linked servers can be defined to point back (loop back) to the server on which they are defined. Loopback servers are most useful when testing an application that uses distributed queries on a single server network.

Eg.

SELECT *FROM OPENROWSET
('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')

OR

sp_addlinkedserver @server = N'MyLink',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'MyServer',
@catalog = N'AdventureWorks'

Note :
@server = N'MyLink' : It can not be your Server name. It is just a name and can be anything otherthan your actual servername. IF you give your server name in this parameter you will get an error as follows
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server 'LHI-115' already exists.

@datasrc = N'MyServer' : This parameter value has to be your server name or IP.

@catalog =N'AdventureWorks' : This is the database in which the Stored procedure exists.


OPENQUERY to Extract data from Loopback linkeserver

Select *from openquery([YourLoopbackServerName],'exec AdventureWorks.dbo.sptest')

FAQ : How can we know the progress of a maintenance command using DMVs

Its very common that, when we run DBCC maintenance command we would like to know how long will it take or how much percentage the process is completed. Here we go…


Select R.Session_id,R.Command,R.Percent_complete
From
sys.dm_exec_requests R
Inner Join
Sys.dm_exec_sessions S
on S.Session_id=R.Session_ID and S.IS_User_Process=1

This process is supported for DBCC CheckDB, DBCC CheckTable, DBCC CheckFilegroup,
DBCC IndexDefrag, DBCC Shrinkfile

FAQ : How to truncate and shrink Transaction Log file in SQL Server

First of all truncation of transaction log is not a recommended practice. But it is unavoidable if you have not kept proper backup policy and recovery model for your database. Its always better to know the cause and prevention for the transaction log size issue. Refer the following KB for more info

Managing the Size of the Transaction Log File
ahttp://msdn.microsoft.com/en-us/library/ms365418(SQL.100).aspx
Transaction Log Physical Architecture
http://msdn.microsoft.com/en-us/library/ms179355(SQL.100).aspx
Factors That Can Delay Log Truncation
http://msdn.microsoft.com/en-us/library/ms345414(SQL.100).aspx


Now coming to the point. If you have no space left with the drive where the Log file is kept and the size of the Transaction Log file is not manageable then its better to shrink the log.

Broadly , you have two steps here.
(a) Mark the inactive part of Trasaction log to release.
(b) Release the marked release portion of Transaction log to OS.

SQL Server 2005

-- Step 1 – Mark the inactive part of the log for release

Use YourDatabaseName
Go
Backup Log YourDatabaseName With Truncate_Only
GO

-- Step 2 - Release the marked space to OS

Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Note : If you have single log file the above mentioned script will work. IF you have multiple log file the change the script accordingly

SQl Server 2000

-- Step 1 – Mark the inactive part of the log for release

Use YourDatabaseName
Go
Backup Log YourDatabaseName With Truncate_Only
GO

-- Step 2 - Release the marked space to OS

Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sysfiles where filename like '%.ldf'
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)


Note : If you have single log file and the extension of the log file is .LDF the above mentioned script will work. IF you have multiple log file the change the script accordingly

SQL Server 2008

In SQL Server this process have been changed. In 20008, just change the recovery model to simple and then use DBCC SHrinkfile command.

select name,recovery_model_desc from sys.databases
GO
Alter database YourDatabaseName Recovery simple
GO
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

FAQ : How triggers in SQL Server 2005 impact Tempdb

In SQL Server 2005 the triggers are implemented using Version store feature. In earlier versions (SQL Server 2000/ SQL 7), the trigger's Inserted and Deleted tables data were captured by reading Transaction log. But in SQL Server 2005, the Inserted and Deleted data is stored in Tempdb as row version. So, if you have more trigger in SQL Server 2005, it may impact Tempdb performance.

FAQ : How to run DBCC DBREINDEX against all the user table in SQL Server

Simple, use undocumented stored procedure sp_msforeachtable. Since its undocumented there is no gurantee that it will be available in all the future versions. But in SQL Server 2000 and 2005 it works.

Use YourdatabaseName
EXEC sp_msforeachtable 'DBCC DBREINDEX( ''?'')'

FAQ : SQL Server DBA as a career option

This is a very common question in SQL Forums hence i thought to blog my view.

If you are a college passed out or a newbie in IT field, then I would suggest you to go for SQL Server MCTS (70-431) and MCITP DBA (70-443 and 70-444) certifications. Do not clear the certification for the sake of it. Learn the techniques and best practices mentioned in the syllabus. You must also go through the Virtual Courses available in Microsoft site. One great thing about Microsoft is , there are fantastic resource available in internet which comes free of cost. You just need to have MSN Live ID and login with that ID and refer the resource. Also must not forget download all the SQL OnDemand webcasts available in Microsoft site. Mind you, Webcast comes right from very experience folks who have been working for decades in this technology and also from the product teams who developed that products. Basically, reading , listening to experience people (webcast) and learn new technology will certainly give you a good launch in SQL Server. Always try to start with new product versions available (like at this point of time we have 2005 and 2008 CTP) in the market to be a leader rather than a follower.

For those who are already into IT field and wants to switch to DBA career can also more or less follow the same steps I have mentioned earlier. Key point in DBA for that matter any field, is Documentation. Try to document day to day activities and be process oriented. DBA is very risky at the same time secure job. Contradictory statements right?. Risky because you are working on production and data is nothing less than god for a DBA or you can say Data is your job. If anything goes wrong, it will may cost your job also. Secure, because the technology in Database generally not changes drastically. Consider one is working in VB 6 and one fine day he has been told to work in .Net 2.0. He will surely have tough time. But for a DBA to jump from SQL Server 2000 to 2005 or 2008 may be a smooth drive compared to other technologies.


SQL Server 2008 Virtual Lab
http://technet.microsoft.com/en-us/cc164207.aspx
SQL Server 2005 Virtual Lab
http://technet.microsoft.com/en-us/bb499681.aspx
SQL Server 2000 Virtual Lab
http://technet.microsoft.com/en-us/bb499685.aspx

Microsoft ondemand Webcasts
http://www.microsoft.com/events/webcasts/ondemand.mspx

Information about all the certification available SQL Server 2005
http://madhuottapalam.blogspot.com/2007_10_01_archive.html

How to find the time taken (total elapsed time) by the query?

Though SQL Server provides SET STATISTICS TIME option to find the total elapsed time taken by a query or a sp, I generally do not use this because, for large number of statement reading the output of Statistics time will be difficult. It is better to keep your own script to get the elapsed time. Here is the script

DECLARE
@total_elapsed_time VARCHAR(100),
@start_time Datetime,
@complete_time Datetime

SELECT @start_time = GETDATE()
Print @start_time
-- here Paste the query which you want to execute
select *From sysobjects ,sys.tables – Replace this query with your query /sp

Set @complete_time=getdate()

SELECT
@total_elapsed_time = 'Total Elapsed Time (minutes:seconds) ' +
CONVERT(CHAR(3),
DATEDIFF(SS,@start_time,@complete_time)/60) +
':' +
CONVERT(CHAR(3),
DATEDIFF(SS,@start_time,@complete_time)%60)
print @total_elapsed_time

Note : If you want to know how long compilation and optimization took then use SET STATISTICS TIME