CTE - Generate Sequence

WITH Nbrs ( n ) AS (

        SELECT 1 UNION ALL

        SELECT 1 + n FROM Nbrs WHERE n < 500 )

    SELECT n FROM Nbrs

    OPTION ( MAXRECURSION 500 )

with DateCTE as

(

    select cast('2012-01-01' as datetime) DateValue

    union all

    select DateValue + 1

    from    DateCTE  

    where   DateValue + 1 < GETDATE()

)

select DateValue

 from    DateCTE

 OPTION (MAXRECURSION 0)

with DateCTE as

(

    select cast('2012-01-01 00:00:00' as datetime) DateValue

    union all

    select dateadd(hh, 1, DateValue)

    from    DateCTE  

    where   dateadd(hh, 1, DateValue) < GETDATE() + 1

)

select DateValue

 from    DateCTE

 OPTION (MAXRECURSION 0)

Generate Sequence from a Range

;with cte as

(

      select Id, StartIp, EndIP, StartIp as Ip

      from Subnets

union all

      select Id, StartIp, EndIP, Ip + 1 as Ip

      from cte where Ip + 1 <= EndIP

)

select * from cte

order by id

OPTION ( MAXRECURSION 5000 

SQL Profiler Trace

-- STOP TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 0

-- START TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 1

-- CLOSE & DELETE TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 2

-- INFO ABOUT A TRACE

-- ALL TRACES

select *

from fn_trace_getinfo (NULL)

select *

from fn_trace_getinfo (0)

select *

from fn_trace_getinfo (DEFAULT)

-- INFO OF SPECIFIC TRACE_ID

select *

from fn_trace_getinfo (3)

-- IMPORT TRACE FILE INTO A TABLE

SELECT * INTO yourtracetable

FROM ::fn_trace_gettable('C:\MyTrace.trc', default)

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 500

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

exec @rc = sp_trace_create

      @TraceID output,

      @options = 2,           -- TRACE_FILE_ROLLOVER

      @tracefile = N'Z:\MSSQL10.MSSQLSERVER\MSSQL\SQLTraces\AdvancedReportsDecommission',

      @maxfilesize = @maxfilesize, -- MB

      @stoptime = NULL, -- date and time the trace will be stopped

      @filecount = 10         -- maximum number or trace files to be maintained with the same base filename

-- FIND THE SOURCE OF A TRACE

select *

from sys.traces

Database File Growth History

-- SCRIPT 1

SELECT te.name, t.DatabaseName, t.FileName, t.StartTime, t.ApplicatioNname

FROM fn_trace_gettable('M:\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc', NULL) AS t

INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id

WHERE te.name LIKE '%Auto Grow'

ORDER BY StartTime desc

-- SCRIPT 2

DECLARE @path NVARCHAR(1000)

SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +

                      '\log.trc'

FROM   sys.traces

WHERE  id = 1

PRINT @path

SELECT databasename,

       e.name   AS eventname,

       cat.name AS [CategoryName],

       starttime,

       e.category_id,

       loginname,

       loginsid,

       spid,

       hostname,

       applicationname,

       servername,

       textdata,

       objectname,

       eventclass,

       eventsubclass

FROM   ::fn_trace_gettable(@path, 0)

INNER JOIN sys.trace_events e ON eventclass = trace_event_id

INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id

WHERE  e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )

ORDER  BY starttime DESC