Automated Trace job

USE [msdb]
GO

/****** Object:  Job [Automated Trace - 1 Minute (ad-hoc)]    Script Date: 5/28/2014 1:13:23 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [SQL Vision]    Script Date: 5/28/2014 1:13:23 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'SQL Vision' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'SQL Vision'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Automated Trace - 1 Minute (ad-hoc)',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'SQL Vision',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Team_DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run Trace]    Script Date: 5/28/2014 1:13:24 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run Trace',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- Variables
declare @rc int, @TraceID int, @filename NVARCHAR(256), @maxsize BIGINT, @on BIT
DECLARE @Folder VARCHAR(1000)


-- Check for other running traces
SELECT @rc = COUNT(DISTINCT traceid) FROM ::fn_trace_getinfo(default)
IF (@rc > 1) goto error


-- Create the holding folder
SELECT TOP 1 @Folder = RTRIM(RootFolderPath) FROM SysAdmin.dbo.tbl_AutoTrace
IF RIGHT(@Folder, 1) <> ''\''
SET @Folder = @Folder + ''\''

SELECT @Folder = @Folder +
                @@SERVERNAME + ''\'' +
''output_'' +
CONVERT(VARCHAR(8), GETDATE(), 112) + ''_'' +
LEFT(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), '':'', ''''), 4)

exec master.dbo.xp_create_subdir @Folder

SELECT @filename = @Folder + ''\'' + REPLACE(@@SERVERNAME, ''\'', ''_'') + ''__sp_trace''
SELECT @maxsize=50, @on=1


-- Create a Queue
exec @rc = sp_trace_create @TraceID output, 2, @filename, @maxsize, NULL
if (@rc != 0) goto error


-- Set the events
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @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, 35, @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, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
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, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 10, 2, @on  -- Needed for 2008 RML
exec sp_trace_setevent @TraceID, 12, 2, @on  --  Needed for 2008 RML

--exec sp_trace_setfilter @TraceID, 10, 0, 7, N''SQL Server Profiler - 8deeb35d-8e51-401e-bc3c-2574de5948eb''


-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1


-- Wait 10 min
WAITFOR DELAY ''00:01:00''

-- Stop/Delete the trace

exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2

goto finish

error:
select ErrorCode=@rc

finish:',
@database_name=N'SysAdmin',
@output_file_name=N'G:\Traces\1mintrace.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Will produce candidate shrink/grow commands based on given

USE tempdb
GO

SET NOCOUNT ON
SET   QUOTED_IDENTIFIER ON

DECLARE @target DECIMAL(5,2)
SET         @target = 20      -- Modify to desired value of free space %

DECLARE @DriveLetter nvarchar(4)
SET @DriveLetter  ='H'


/***************************************************************************
Candidate Commands Utility                     
                                         
Written by: David Paul Giroux                                    
Date: Fall 2008                                                                                      
Purpose: Assist in file size management.  Will produce candidate shrink/grow commands based on given Target                                
Calls: xp_fixeddrives        
Data Modifications: None

User Modifications:
* Modify @target to desired value of free space percent

Known Issue: There is an issue when dealing with smaller values because the
candidate commands use int data type. The candidate command may round to a value equal to the current file size.
Thus a MODIFY FILE statement will fail. Workaround: Add 1 to the candidate command 
                                                     
***************************************************************************/

DECLARE     @cmd varchar(1000)          -- various commands
DECLARE     @counter tinyint            -- Number of databases
DECLARE     @crlf char(2)               -- carriage return line feed
DECLARE     @db sysname                 -- Database Name

SET         @crlf = CHAR(13) + CHAR(10)
SET         @target = (100.0 - @target) * .01

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
      DBName sysname,
      [FileName] sysname,
      FileType sysname,
      Drive char(1),
      UsedData varchar(25),
      TotalDataSize varchar(25),
      Smallest decimal(10,2)
      )

-- Databases to examine
DECLARE @Databases TABLE  (
      DID tinyint IDENTITY(1,1) primary key,
      db sysname NULL
      )

-- Hold values from xp_fixeddrives
DECLARE @DiskInfo TABLE(
      Drive char(1) primary key,
      MBFree int
      )

-- Gather databases
INSERT @Databases
SELECT      DISTINCT sd.[name]
FROM  sys.master_files mf WITH (NOLOCK)
JOIN  sys.databases sd WITH (NOLOCK)
ON          mf.database_id = sd.database_id
WHERE sd.[state] = 0
AND         sd.is_read_only = 0
AND         sd.is_in_standby = 0
AND         sd.[name] NOT IN ('model', 'tempdb')
AND         mf.[type] = 0
-- to exclude databases that have a full text-catalog offline
AND         sd.database_id NOT IN (
                            SELECT DISTINCT database_id
                            FROM  sys.master_files WITH (NOLOCK)
                            WHERE [state] <> 0)

SET         @counter = SCOPE_IDENTITY()

WHILE @counter > 0
BEGIN
            SELECT      @db = db
            FROM  @Databases
            WHERE DID = @counter   

            SELECT @cmd =

            N'USE [' + @db + N']' +  @crlf +
            N'SET NOCOUNT ON' + @crlf +
            N'SELECT     '+ QUOTENAME(@db, '''') + N',' + @crlf +
            N'[name],' + @crlf +
            N'CASE type ' + @crlf +
            N'     WHEN 0 THEN ''DATA''' + @crlf +
            N'     WHEN 1 THEN ''LOG'''  + @crlf +
            N'     ELSE ''Other''' + @crlf +
            N'END,' + @crlf +
            N'LEFT(physical_name, 1), ' + @crlf +
            N'CAST(FILEPROPERTY ([name], ''SpaceUsed'')/128.0 as varchar(15)),' + @crlf +
            N'CAST([size]/128.0 as varchar(15))' + @crlf +
            N'FROM sys.database_files WITH (NOLOCK)' + @crlf +
            N'WHERE      [state] = 0' + @crlf +
            N'AND        [type] IN (0,1)'

            -- Preliminary results
            INSERT #Results
            (DBName, [FileName], FileType, Drive, UsedData, TotalDataSize)
            EXEC (@cmd)

            SET   @counter = @counter - 1
END

ALTER TABLE #Results
ALTER COLUMN TotalDataSize decimal(10,2)

ALTER TABLE #Results
ALTER COLUMN UsedData decimal(10,2)

UPDATE      #Results
SET         Smallest = UsedData / @target

-- Command determines free space in MB
INSERT INTO @DiskInfo
EXEC master..xp_fixeddrives

---- Final Query
SELECT      upper(DBName) as dbname,
            upper([FileName]) as filename,
            upper(FileType) as filetype,
            upper(r.Drive) as Drive,
            CAST((TotalDataSize)/1024 as decimal(5,2)) AS [Total Size GB],
            CAST((UsedData /1024) as decimal(5,2))  [Used Space GB],
            CAST((TotalDataSize - UsedData)/1024 as decimal(5,2))  [Free Space GB],
            UsedData,
            TotalDataSize - UsedData N'FreeData',
            TotalDataSize,
            CAST(((TotalDataSize - UsedData) / TotalDataSize) * 100 as decimal(5,2)) [%DataFeeSpace],
            d.MBFree N'DiskFreeSpace',
            Smallest N'SmallestForTarget',
            CASE
                    WHEN TotalDataSize > Smallest THEN CAST(TotalDataSize - Smallest as varchar(10)) + N' Decrease'
                    ELSE CAST(Smallest - TotalDataSize as varchar(10)) + N' Increase'
            END N'CandidateResult',
            CASE
                  WHEN Smallest - TotalDataSize > d.MBFree THEN N'Insufficient Disk Space'
                    WHEN TotalDataSize > Smallest
                              THEN N'USE [' + DBName + N'] DBCC SHRINKFILE(' + QUOTENAME([FileName], '''') + N', ' + CAST(CAST(Smallest as int) as varchar(10)) + N')'
                    ELSE  N'ALTER DATABASE [' + DBName + N']' + @crlf +
                                      N'MODIFY FILE (' + @crlf +
                                      N'     NAME = ' + [FileName] + N',' + @crlf +
                                      N'     SIZE = ' + CAST(CAST(Smallest as int) as varchar(10)) + @crlf +
                                      N'     )'
            END N'CandidateCommand'
FROM  #Results r
JOIN  @DiskInfo d
ON          r.Drive = d.Drive
and     r.drive = @DriveLetter
--ORDER BY (TotalDataSize - UsedData) / TotalDataSize
order by CAST((TotalDataSize - UsedData)/1024 as decimal(5,2)) desc

DROP TABLE #Results

Partitioning: Three useful query

http://weblogs.sqlteam.com/dmauri/archive/2006/04/20/9646.aspx

DECLARE @PartTables Table (DBName sysname, TableName sysname, PartitionScheme sysname)


INSERT INTO @PartTables
EXEC sp_msforeachdb '    select DISTINCT ''[?]'' AS DBName, t.name As TableName, s.name AS PartitionScheme
                        from [?].sys.indexes i
                        join [?].sys.partition_schemes s
                            on i.data_space_id = s.data_space_id
                        join [?].sys.tables t
                            on i.object_id = t.object_id'

SELECT * FROM @PartTables


DECLARE @OBJECTNAME sysname = 'XXXXX'

select distinct
   p.object_id,
   index_name = i.name,
   index_type_desc = i.type_desc,
   partition_scheme = ps.name,
   data_space_id = ps.data_space_id,
   function_name = pf.name,
   function_id = ps.function_id
from
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
where p.object_id = object_id(@OBJECTNAME)




DECLARE @OBJECTNAME sysname = 'XXXX'


select
   p.object_id,
   p.index_id,
   p.partition_number,
   p.rows,
   index_name = i.name,
   index_type_desc = i.type_desc,
   i.data_space_id,
   pf.function_id,
   pf.type_desc,
   pf.boundary_value_on_right,
   destination_data_space_id = dds.destination_id,
   prv.parameter_id,
   prv.value
from
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
inner join
   sys.destination_data_spaces dds on dds.partition_scheme_id = ds.data_space_id and p.partition_number = dds.destination_id
left outer join
   sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
where p.object_id = object_id(@OBJECTNAME)