continue from http://sajha.com/sajha/html/openThread.cfm?forum=144&threadid=65869

Admin(SAN),

Please donot move this thread from Kurakani as this is important for our brother/sister who need help for SQL Server .

Here is my script for server-side trace that runs 30 mins and stops.

--Stored Procedure to run trace for 30 minutes
Create Procedure dbo.Usp_Tracefor30minutes
as
set nocount on

declare @stop datetime
set @stop = dateadd(mi,30,getdate())
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
-- create traces and saves in folder specified
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @stop
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on -- 17 - Existing Connections
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on -- 10 - RPC:Completed
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on -- 43 - SP:Completed
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 6, @on -- 37 - SP:Recompile
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on -- 12 - SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on -- 13 - SQL :BatchStarting
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
 
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler'
set @bigintfilter = 5000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc

Most of Tech- interview questions I ask to candidate while screening:

SQL DBA

1)Explain about yourself regarding SQL Server exposure.(This question filters out  many candidates with the way they communicate.Remember you can estimate his/her experience with 2-3 sentences.)

2)How do you resolve when developers comes infront of you and say query is running really slow or server is running slow?

3) How do you measure memory pressure ? Be specific

4)What do you call table without clustered index? When do you need Clustered index in table?

5)What is difference between Deadlocking and locking? What resolves them?

6) How do you remove Bookmark lookup in Execution plan?

7) What permission you need to truncate table?

8)Explain database backups and recovery scenerio?

9) What is most difficult scenerio you have faced in your professional career as SQL DBA? and how did you resolve it?

continue......

10) What is XML shredding? How do you import XML files into SQL Server tables?

11) Have you used any Third-Party tools for monitoring SQL Server?

12)Does Truncate table command work in Database mirroring concept?

13)What do you mean by Cardinality in SQL Server?

14)How do you implement Normalization?

hey,

thanks a lot for sharing information. appreciated for your effort.

Here is question from Jaynir

Could u please help me out on the real time scenarios that most of the DBAs face? Could you please highlight the probable issues that could arise during migration from 2000 to 2005?

Probable issue that could arise during migration:

1)Unexperienced DBA handling the migration process without creating proper documentation.

2)During migration of DTS package to SSIS,all activexscript task involved in COM object should be rewritten in script task in SSIS

3)When there is no team work with Network team during switching server to new one.

Here is what I do During migration of SQL 2000 to SQL Server 2005

1) Create proper documentation (show manager how good you are ) with failover plan as well

2) Install SQL Server 2005 on new server with necessary patches and Service packs.

3)Migrate DTS packages with COM object and associated database to SSIS and test in new environment and delete database after you are successful with testing.

4) Backup all database  and restore in new server in Norecovery mode

5) Take last transactional log backup all database and restore in new server 

5a) Migrate all DTS packages to SSIS packages. 

6)Tell your network guys to switch the DNS  name or Server name  so that application will see same Server name and I.p address

7) Make sure all applications are working fine  and update stats

Jaynir,

It is good to post here so everyone can share.Isn't it?