Archive for the ‘Uncategorized’ Category

xp_cmdshell and file paths with spaces

May 24, 2018

Not sure why but xp_cmdshell has issues with spaces in file paths. For example the following 7zip command results in an error:

EXEC xp_cmdshell ‘”c:\Program Files\7-Zip\7z” a “c:\test folder\test.zip” “c:\test folder\x.bmp’

‘c:\Program’ is not recognized as an internal or external command,operable program or batch file.

One workaround to this is to ensure that the qualified path is not at the beginning of the command. For example the following will work as expected

EXEC xp_cmdshell ‘cd.. && “c:\Program Files\7-Zip\7z” a “c:\test folder\test.zip” “c:\test folder\x.bmp’

So the work around here was to insert cd.. && at the beginning of the command string.

Advertisements

passing temp tables between stored procedures

May 23, 2018

Its not widely known that you can pass temp tables between stored procedures.

The basic idea is that you create a parent procedure which contains the temp table, and one or more child procedures that reference the table. The parent procedure will then call the child procedures after the temp table has been created.

A basic example can be seen below:

USE test
GO

CREATE PROC dbo.pass_temp_table_between_stored_procs

AS

SELECT GETDATE() x INTO #temp

EXEC test.dbo.pass_temp_table_between_stored_procs1
EXEC test.dbo.pass_temp_table_between_stored_procs2

GO

CREATE PROC dbo.pass_temp_table_between_stored_procs1
AS
SELECT * FROM #temp

GO

alter PROC dbo.pass_temp_table_between_stored_procs2
AS

UPDATE #temp SET x = x+1

SELECT * FROM #temp

GO

EXEC dbo.pass_temp_table_between_stored_procs

jobs/schedules/steps views

May 2, 2018

CREATE VIEW jobschedules

AS

SELECT SJ.name as job_name
, SJ.enabled as is_job_enabled
, SS.enabled as is_schedule_enabled
/*, ISNULL(SJ.description, ”) as job_desc*/
, SS.name as schedule_name
, CASE freq_type
WHEN 1 THEN ‘Occurs on ‘ + STUFF(RIGHT(active_start_date, 4), 3,0, ‘/’) + ‘/’ + LEFT(active_start_date, 4) + ‘ at ‘
+ REPLACE( RIGHT(CONVERT(varchar(30),
CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime) /* hh:mm:ss 24H */
, 9), 14), ‘:000’, ‘ ‘) /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
WHEN 4 THEN ‘Occurs every ‘ + CAST(freq_interval as varchar(10))
+ ‘ day(s) ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 8 THEN
‘Occurs every ‘ + CAST(freq_recurrence_factor as varchar(10))
+ ‘ week(s) on ‘
+
REPLACE( CASE WHEN freq_interval&1 = 1 THEN ‘Sunday, ‘ ELSE ” END
+ CASE WHEN freq_interval&2 = 2 THEN ‘Monday, ‘ ELSE ” END
+ CASE WHEN freq_interval&4 = 4 THEN ‘Tuesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&8 = 8 THEN ‘Wednesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&16 = 16 THEN ‘Thursday, ‘ ELSE ” END
+ CASE WHEN freq_interval&32 = 32 THEN ‘Friday, ‘ ELSE ” END
+ CASE WHEN freq_interval&64 = 64 THEN ‘Saturday, ‘ ELSE ” END
+ ‘|’, ‘, |’, ‘ ‘) /* get rid of trailing comma */

+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 16 THEN
‘Occurs every ‘ + CAST(freq_recurrence_factor as varchar(10))
+ ‘ month(s) on ‘
+ ‘day ‘ + CAST(freq_interval as varchar(10)) + ‘ of that month ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 32 THEN
‘Occurs ‘ +
CASE freq_relative_interval
WHEN 1 THEN ‘every first ‘
WHEN 2 THEN ‘every second ‘
WHEN 4 THEN ‘every third ‘
WHEN 8 THEN ‘every fourth ‘
WHEN 16 THEN ‘on the last ‘
END
+ CASE freq_interval WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘day’
WHEN 9 THEN ‘weekday’
WHEN 10 THEN ‘weekend’
END

+ ‘ of every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ month(s) ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 64 THEN ‘Runs when the SQL Server Agent service starts’
WHEN 128 THEN ‘Runs when the computer is idle’

END as [Description]
, CASE freq_type
WHEN 1 THEN ‘One Time’
WHEN 4 THEN CASE freq_subday_type WHEN 1 THEN ‘Daily’
WHEN 2 THEN ‘Second-ly’
WHEN 4 THEN ‘Minutely’
WHEN 8 THEN ‘Hourly’
ELSE ”
END
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly, relative to freq_interval’
WHEN 64 THEN ‘Runs when the SQL Server Agent service starts’
WHEN 128 THEN ‘Runs when the computer is idle’
END As [Freq_Type]
, CASE freq_subday_type
WHEN 1 THEN ‘At the specified time’
WHEN 2 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Second(s)’
WHEN 4 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Minute(s)’
WHEN 8 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Hour(s)’
ELSE ”
END AS freq_subday_type

, CASE
WHEN freq_type = 4 /* (daily) */ THEN ‘Every ‘ + CAST(freq_interval as varchar(10)) + ‘ Day(s)’
WHEN freq_type = 8 /* (weekly) */ THEN
REPLACE( CASE WHEN freq_interval&1 = 1 THEN ‘Sunday, ‘ ELSE ” END
+ CASE WHEN freq_interval&2 = 2 THEN ‘Monday, ‘ ELSE ” END
+ CASE WHEN freq_interval&4 = 4 THEN ‘Tuesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&8 = 8 THEN ‘Wednesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&16 = 16 THEN ‘Thursday, ‘ ELSE ” END
+ CASE WHEN freq_interval&32 = 32 THEN ‘Friday, ‘ ELSE ” END
+ CASE WHEN freq_interval&64 = 64 THEN ‘Saturday, ‘ ELSE ” END
+ ‘|’, ‘, |’, ‘ ‘) /* get rid of trailing comma */
WHEN freq_type = 16 THEN ‘On Day ‘ + CAST(freq_interval as varchar(10)) + ‘ of Every Month’
WHEN freq_type = 32 /* (monthly) */ THEN ‘Every ‘ +
CASE freq_interval WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘Day’
WHEN 9 THEN ‘Weekday’
WHEN 10 THEN ‘Weekend day’
END
ELSE ”
END as [freq_interval]
, CASE WHEN freq_type = 8 /* (weekly) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Week(s)’
WHEN freq_type = 16 /* (monthly) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Month(s)’
WHEN freq_type = 32 /* (monthly relative) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Week(s)’
ELSE ”
END as freq_recurrence_factor
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘)) as starttime
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘)) as endtime
, STUFF(STUFF(active_start_date, 5, 0, ‘/’), 8, 0, ‘/’) as schedule_start_date
, STUFF(STUFF(active_end_date, 5, 0, ‘/’), 8, 0, ‘/’) as schedule_end_date
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobschedules SJS
ON SJ.job_id = SJS.job_id
INNER JOIN msdb.dbo.sysschedules SS
ON SJS.schedule_id = SS.schedule_id

CREATE VIEW jobs

AS
SELECT a.name job_name ,
a.enabled is_job_enabled ,
description job_description ,
start_step_id start_at_step ,
b.name category_name ,
c.name job_owner_name ,
CASE notify_level_email
WHEN 0 THEN ‘never’
WHEN 1 THEN ‘when the job succeeds’
WHEN 2 THEN ‘when the job fails’
WHEN 3 THEN ‘when the job completes’
END notify_level_email ,
d.name notify_email_operator ,
date_created job_created_date ,
date_modified job_modifed_date
FROM msdb..sysjobs a
LEFT JOIN msdb..syscategories b ON b.category_id = a.category_id
LEFT JOIN msdb..sysusers c ON c.sid = a.owner_sid
LEFT JOIN msdb..sysoperators d ON d.id = a.notify_email_operator_id;
GO

CREATE VIEW jobsteps

AS
SELECT b.name job_name,
step_id job_step_number,
step_name job_step_name,
subsystem job_step_type,
command job_step_command,
CASE on_success_action
WHEN 1 THEN ‘go to next step’
WHEN 2 THEN ‘quit the job reporting success’
WHEN 3 THEN ‘quit the job reporting failure’
WHEN 4 THEN ‘go to step’
ELSE NULL
END on_success_action,
on_success_step_id ,
CASE on_fail_action
WHEN 1 THEN ‘go to next step’
WHEN 2 THEN ‘quit the job reporting success’
WHEN 3 THEN ‘quit the job reporting failure’
ELSE NULL
END ON_failure_action,
on_fail_step_id ,
database_name ,
retry_attempts ,
retry_interval retry_interval_mins,
output_file_name ,
CASE last_run_outcome
WHEN 0 THEN ‘failed’
WHEN 1 THEN ‘succeeded’
WHEN 2 THEN ‘retry’
WHEN 3 THEN ‘cancelled’
WHEN 5 THEN ‘unknown’
END last_run_outcome,
LEFT(oa1.last_run_duration, 2) + ‘:’ + SUBSTRING(oa1.last_run_duration, 3, 2) + ‘:’ + SUBSTRING(oa1.last_run_duration, 5, 2) last_run_duration,
last_run_retries ,
last_run_date ,
LEFT(oa1.last_run_time, 2) + ‘:’ + SUBSTRING(oa1.last_run_time, 3, 2) + ‘:’ + SUBSTRING(oa1.last_run_time, 5, 2) last_run_time,
c.name proxy_name
FROM msdb..sysjobsteps a
INNER JOIN msdb..sysjobs b ON b.job_id = a.job_id
LEFT JOIN msdb..sysproxies c ON c.proxy_id = a.proxy_id
OUTER APPLY ( SELECT RIGHT(‘000000’ + CAST(last_run_duration AS VARCHAR(100)), 6) last_run_duration ,
RIGHT(‘000000’ + CAST(last_run_time AS VARCHAR(100)), 6) last_run_time ) oa1;

Create a copy of a database without any data

April 10, 2018

Create dacpac

install:

Microsoft® SQL Server® Data-Tier Application Framework (June 30 2016)

https://www.microsoft.com/en-us/download/details.aspx?id=53013

C:\Program Files\Microsoft SQL Server\130\DAC\bin>sqlpackage /Action:Extract /SourceDatabaseName:”games” /SourceServerName:SERVERNAME /TargetFile:”C:\dac\games.dacpac”

create new database from dacpac

within management studio

right click databases folder
deploy data tier application
follow the GUI instructions
configure database

process a table of commands via a stored proc

April 3, 2018

CREATE TYPE command_list AS TABLE (id int,cmd varchar(MAX))

–SELECT * FROM sys.types WHERE is_user_defined = 1

GO

CREATE PROC dba.usp_process_command_list

@input_list command_list readonly
AS

–jlester 20180329 – takes a list of commands and processes them
–input table has 2 fields id – an int starting from 1 and incrementing by 1 for each command
–cmd – is the command to be processed
— here is a sample block of code:

/*

DECLARE @x command_list

INSERT INTO @x
SELECT 1 ,’select 1′
UNION ALL
SELECT 2 ,’select getdate()’

EXEC dba.usp_process_command_list @input_list = @x

*/

DECLARE @command_list TABLE
(id int,cmd varchar(MAX))

INSERT @command_list
SELECT * from @input_list

DECLARE @maxid INT = (SELECT MAX(id) FROM @command_list) –find the last command to be processed
DECLARE @curid INT = 1 –start from command 1
DECLARE @curcmd VARCHAR(MAX)

–PRINT @maxid

while @curid<=@maxid –loop through the list 1 record at a time
BEGIN
–PRINT @curid
SET @curcmd = (SELECT cmd FROM @command_list WHERE id =@curid) –fetch the command for the current id

–print @curcmd
exec (@curcmd)

SET @curid += @curid –increment the current id by 1
–PRINT @curid
END

–SELECT * FROM @command_list

GO

DECLARE @x command_list

INSERT INTO @x
SELECT 1 ,'select 1'
UNION ALL
SELECT 2 ,'select getdate()'

EXEC dba.usp_process_command_list @input_list = @x

GO

Latest Version of Code to move data to a new filegroup

February 16, 2018

The LOB data section needs some work, but otherwise it works well

— heaps

SELECT ‘alter table ‘ + QUOTENAME(d.name)+’.’+QUOTENAME(c.name) + ‘ add xq1 tinyint; create clustered index q1924 on ‘ + QUOTENAME(d.name)+’.’+QUOTENAME(c.name) + ‘(xq1) with (sort_in_tempdb=on,fillfactor= 100) on [DATA]; DROP INDEX q1924 on ‘ + QUOTENAME(d.name)+’.’+QUOTENAME(c.name) + ‘; ALTER TABLE ‘ + QUOTENAME(d.name)+’.’+QUOTENAME(c.name) + ‘ DROP COLUMN xq1’,*
FROM sys.allocation_units a
INNER JOIN sys.partitions b ON a.container_id = b.partition_id
INNER JOIN sys.objects c ON c.object_id = b.object_id
INNER JOIN sys.schemas d ON d.schema_id = c.schema_id
WHERE b.index_id IN (0) AND c.type_desc NOT IN (‘SYSTEM_TABLE’,’INTERNAL_TABLE’) AND a.data_space_id = 1 AND a.type !=2
ORDER BY a.total_pages DESC

–capture index information

SELECT
DB_NAME() AS database_name,sc.name sname,t.name tname,includes.include_definition,si.filter_definition,keys.key_definition,
sc.name + N’.’ + t.name AS table_name,
(SELECT MAX(user_reads)
FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
last_user_update,
CASE si.index_id WHEN 0 THEN N’/* No create statement (Heap) */’
ELSE
CASE is_primary_key WHEN 1 THEN
N’ALTER TABLE ‘ + QUOTENAME(sc.name) + N’.’ + QUOTENAME(t.name) + N’ ADD CONSTRAINT ‘ + QUOTENAME(si.name) + N’ PRIMARY KEY ‘ +
CASE WHEN si.index_id > 1 THEN N’NON’ ELSE N” END + N’CLUSTERED ‘
ELSE N’CREATE ‘ +
CASE WHEN si.is_unique = 1 then N’UNIQUE ‘ ELSE N” END +
CASE WHEN si.index_id > 1 THEN N’NON’ ELSE N” END + N’CLUSTERED ‘ +
N’INDEX ‘ + QUOTENAME(si.name) + N’ ON ‘ + QUOTENAME(sc.name) + N’.’ + QUOTENAME(t.name) + N’ ‘
END +
/* key def */ N'(‘ + key_definition + N’)’ +
/* includes */ CASE WHEN include_definition IS NOT NULL THEN
N’ INCLUDE (‘ + include_definition + N’)’
ELSE N”
END +
/* filters */ CASE WHEN filter_definition IS NOT NULL THEN
N’ WHERE ‘ + filter_definition ELSE N”
END +
/* with clause – compression goes here */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL
THEN N’ WITH (‘ +
CASE WHEN row_compression_partition_list IS NOT NULL THEN
N’DATA_COMPRESSION = ROW ‘ + CASE WHEN psc.name IS NULL THEN N” ELSE + N’ ON PARTITIONS (‘ + row_compression_partition_list + N’)’ END
ELSE N” END +
CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N’, ‘ ELSE N” END +
CASE WHEN page_compression_partition_list IS NOT NULL THEN
N’DATA_COMPRESSION = PAGE ‘ + CASE WHEN psc.name IS NULL THEN N” ELSE + N’ ON PARTITIONS (‘ + page_compression_partition_list + N’)’ END
ELSE N” END
+ N’)’
ELSE N”
END +
/* ON where? filegroup? partition scheme? */
‘ ON ‘ + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N”)
ELSE psc.name + N’ (‘ + partitioning_column.column_name + N’)’
END
+ N’;’
END AS index_create_statement,
si.index_id,
si.name AS index_name,
partition_sums.reserved_in_row_GB,
partition_sums.reserved_LOB_GB,
partition_sums.row_count,
stat.user_seeks,
stat.user_scans,
stat.user_lookups,
user_updates AS queries_that_modified,
partition_sums.partition_count,
si.allow_page_locks,
si.allow_row_locks,
si.is_hypothetical,
si.has_filter,
si.fill_factor,
si.is_unique,
ISNULL(pf.name, ‘/* Not partitioned */’) AS partition_function,
ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
t.create_date AS table_created_date,
t.modify_date AS table_modify_date
into #t
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N’ DESC’ ELSE N” END
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + QUOTENAME(c.name)
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS includes ( include_definition )
/* Partitions */ OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2) /* heap, clustered, nonclustered */
ORDER BY table_name, si.index_id
OPTION (RECOMPILE);
GO

SELECT *
INTO test..jl20180216
FROM #t WHERE index_id >0

–indexes

SELECT REPLACE(e.index_create_statement, ‘ON [PRIMARY]’,’WITH (DROP_EXISTING = ON,SORT_IN_TEMPDB = ON,FILLFACTOR=100) ON [DATA]’)
FROM sys.allocation_units a
INNER JOIN sys.partitions b ON a.container_id = b.partition_id
INNER JOIN sys.objects c ON c.object_id = b.object_id
INNER JOIN sys.schemas d ON d.schema_id = c.schema_id
INNER JOIN test..jl20180216 e ON e.tname = c.name AND e.sname =d.name AND e.index_id = b.index_id
INNER JOIN sys.indexes f on f.object_id = c.object_id AND b.index_id = f.index_id
WHERE b.index_id >0 AND c.type_desc NOT IN (‘SYSTEM_TABLE’,’INTERNAL_TABLE’) AND a.data_space_id = 1 AND a.type !=2
AND f.is_primary_key = 0
ORDER BY a.total_pages DESC

–primary keys

SELECT ‘CREATE UNIQUE CLUSTERED INDEX ‘ + e.index_name + ‘ ON ‘ + e.table_name + ‘(‘ + e.key_definition + ‘) WITH (DROP_EXISTING = ON,SORT_IN_TEMPDB = ON,FILLFACTOR=100 ) ON [DATA]’
FROM sys.allocation_units a
INNER JOIN sys.partitions b ON a.container_id = b.partition_id
INNER JOIN sys.objects c ON c.object_id = b.object_id
INNER JOIN sys.schemas d ON d.schema_id = c.schema_id
INNER JOIN test..jl20180216 e ON e.tname = c.name AND e.sname =d.name AND e.index_id = b.index_id
INNER JOIN sys.indexes f on f.object_id = c.object_id AND b.index_id = f.index_id
WHERE 1=1
–b.index_id >0
AND c.type_desc NOT IN (‘SYSTEM_TABLE’,’INTERNAL_TABLE’)
AND a.data_space_id = 1
AND a.type !=2
AND f.is_primary_key = 1
ORDER BY a.total_pages DESC

–LOB DATA: NEEDS TO BE MODIFIED TO CREATE INDEXES, DONT RUN AS IT IS

SELECT

‘BEGIN TRAN select * into ‘ + QUOTENAME(d.name) + ‘.[‘ + c.name + ‘_xtmp] from ‘ + QUOTENAME(d.name) + ‘.’ + QUOTENAME(c.name) +’;ALTER TABLE ‘+ QUOTENAME(d.name) + ‘.[‘ + c.name + ‘_xtmp] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
EXEC sp_rename ”’+ d.name + ‘.’ + c.name + ”’,”’+ c.name + ‘_prev” EXEC sp_rename ”’+d.name + ‘.’ + c.name +’_xtmp”,”’ + c.name +”’ COMMIT
‘,*

FROM sys.allocation_units a
INNER JOIN sys.partitions b ON a.container_id = b.partition_id
INNER JOIN sys.objects c ON c.object_id = b.object_id
INNER JOIN sys.schemas d ON d.schema_id = c.schema_id
WHERE c.type_desc NOT IN (‘SYSTEM_TABLE’,’INTERNAL_TABLE’) AND a.data_space_id = 1
ORDER BY a.total_pages DESC

generate a comma seperated list of columns heading

January 20, 2018

alter proc xxx.usp_generate_column_name_string
@database varchar(100),@schema varchar(100), @table varchar(100)

as

declare @str varchar(max) = ‘

select *,”’+@database+”’ as database_name,”’+@schema+”’ as schema_name,”’+@table+”’ as table_name from (
select stuff((select ”,” + name as [text()]
from
(
select c.name from ‘ + @database + ‘.sys.tables a
inner join ‘ + @database + ‘.sys.schemas b on a.schema_id = a.schema_id
inner join ‘ + @database + ‘.sys.columns c on c.object_id= a.object_id
where b.name = ”’+@schema+”’ and a.name =”’+@table+”’) x

for xml path (””)),1,1,””) cols
) a


exec (@str)

go

exec xxx.usp_generate_column_name_string ‘test’ , ‘dbo’,’jl1_tmp’

Defrag Database Files

January 15, 2018

I was prevented from running CHECKDB in one of our largest databases: CheckDB fails with error: The operating system returned error 665

This is due to fragmentation of database files.

I checked the fragmentation using: https://docs.microsoft.com/en-us/sysinternals/downloads/contig

I could not get the 64 bit version to work but the 32 bit version works

Extract the exe to the c drive, not your users folder and open up a command prompt in that directory example c:\contig -a “file path” will tell you how fragmented your file is, in my case 1.7 million fragments c:\contig “file path” will defrag the file, you dont need to be offline to do this. You can check progress by opening up a new command prompt and repeating the command with the -a switch

Resizing filegroup files

January 7, 2018

I used the following to resize database files

Essentially what we do is create a new filegroup, move tables, indexes etc over drop the old filegroup, rename the new filegroup so it has the name of the old one.

create filegroup
create files
make the new filegroup the default for when you move the tables

script out indexes, filter on your filegroup if necersary, this also wont script columnstore indexes, check if it works for primary keys - it should

SELECT
DB_NAME() AS database_name,
sc.name + N'.' + t.name AS table_name,
(SELECT MAX(user_reads)
FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
last_user_update,
CASE si.index_id WHEN 0 THEN N'/* No create statement (Heap) */'
ELSE
CASE is_primary_key WHEN 1 THEN
N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +
CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED '
ELSE N'CREATE ' +
CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END +
CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' +
N'INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' '
END +
/* key def */ N'(' + key_definition + N')' +
/* includes */ CASE WHEN include_definition IS NOT NULL THEN
N' INCLUDE (' + include_definition + N')'
ELSE N''
END +
/* filters */ CASE WHEN filter_definition IS NOT NULL THEN
N' WHERE ' + filter_definition ELSE N''
END +
/* with clause - compression goes here */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL
THEN N' WITH (' +
CASE WHEN row_compression_partition_list IS NOT NULL THEN
N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END
ELSE N'' END +
CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', ' ELSE N'' END +
CASE WHEN page_compression_partition_list IS NOT NULL THEN
N'DATA_COMPRESSION = PAGE ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END
ELSE N'' END
+ N')'
ELSE N''
END +
/* ON where? filegroup? partition scheme? */
' ON ' + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N'')
ELSE psc.name + N' (' + partitioning_column.column_name + N')'
END
+ N';'
END AS index_create_statement,
si.index_id,
si.name AS index_name,
partition_sums.reserved_in_row_GB,
partition_sums.reserved_LOB_GB,
partition_sums.row_count,
stat.user_seeks,
stat.user_scans,
stat.user_lookups,
user_updates AS queries_that_modified,
partition_sums.partition_count,
si.allow_page_locks,
si.allow_row_locks,
si.is_hypothetical,
si.has_filter,
si.fill_factor,
si.is_unique,
ISNULL(pf.name, '/* Not partitioned */') AS partition_function,
ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
t.create_date AS table_created_date,
t.modify_date AS table_modify_date
into #t
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + QUOTENAME(c.name)
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )
/* Partitions */ OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2) /* heap, clustered, nonclustered */
ORDER BY table_name, si.index_id
OPTION (RECOMPILE);
GO

select * from #t where index_id !=0

make sure you have script saved somewhere, you dont want to lose it

Move tables - use the following as a template, script it, for all the tables in the filegroup

select top 0 *
into database.schema.table_tmp
from database.schema.table

insert into database.schema.table_tmp with (TABLOCK)
select * from database.schema.table

drop table database.schema.table

exec sp_rename 'schema.table_tmp' , 'table'

apply the indexes you scripted earlier, make sure to edit the script to build indexes on new filegroup and sort in temp db on to speed it up (my temp is on an ssd)

if required

drop all the files from the old filegroup
drop the filegroup
rename the new filegroup so it matches the name of the old filegroup

other notes

may need to move lob data, if you use the above method you shouldnt need to, if you need to the only way i could find it so right click table, select design, view properties window, change text/image file group to the new file group, click back into the table so it registers the change, then script the change.
now edit the script to enable minimal logging, if you dont you could screw up your database, get rid of the tranasaction to prevent locking

indexes

you can script indexes via gui, helpful hints - sort in tempdb, drop existing, check the filegroup. use create script option.

primary keys cannot be created with drop existing, need to drop then create on the correct filegroup

i used this script to check what objects were on the filegroup, in the where clause i restriced it to just heaps/ clustered indexes/ primary keys depending on what i was working on

--exec sp_running_SQL

SELECT
(((sum(c.reserved_page_count)*8)/1024)/1)/1024.0
FROM database.sys.indexes a
INNER JOIN database.sys.objects b ON b.object_id = a.object_id
INNER JOIN database.sys.schemas e ON e.schema_id = b.schema_id
INNER JOIN database.sys.dm_db_partition_stats c ON c.index_id = a.index_id AND c.object_id = b.object_id
Inner join database.sys.data_spaces d on a.data_space_id = d.data_space_id
WHERE
1=1
--and b.name LIKE '%prev%'
AND d.name = 'PRIMARY'
--AND a.type_desc != 'Clustered'
--ORDER BY e.NAME,b.name
ORDER BY 1 DESC

SELECT
c.reserved_page_count,a.name,e.name+'.'+b.name,a.type_desc,*
FROM database.sys.indexes a
INNER JOIN database.sys.objects b ON b.object_id = a.object_id
INNER JOIN database.sys.schemas e ON e.schema_id = b.schema_id
INNER JOIN database.sys.dm_db_partition_stats c ON c.index_id = a.index_id AND c.object_id = b.object_id
Inner join database.sys.data_spaces d on a.data_space_id = d.data_space_id
WHERE
1=1
--and b.name LIKE '%prev%'
AND d.name = 'PRIMARY' --and a.type_desc='HEAP'-- and is_primary_key =1
--AND a.type_desc != 'Clustered'
--ORDER BY e.NAME,b.name
ORDER BY 3-- DESC

The above script told me everything had been moved but the following script told me that wasnt true so use this to confirm, e.* should give further info if needed

select d.name,c.name,e.reserved_page_count from sys.allocation_units a
inner join sys.partitions b on a.container_id = b.partition_id

inner join sys.objects c on c.object_id = b.object_id
inner join sys.dm_db_partition_stats e on e.object_id = c.object_id and e.index_id in (0,1)
inner join sys.schemas d on d.schema_id = c.schema_id
where data_space_id =1 --and reserved_page_count >=50000
--and c.name = 'name'
order by 3 desc

i used the following to help generate scripts

SELECT

'ALTER TABLE ' +e.name+'.'+b.name+ ' ADD jlz int; CREATE CLUSTERED INDEX I ON ' +e.name+'.'+b.name + ' (jlz) with (sort_in_tempdb=on) on [DATA]; drop INDEX I ON '+e.name+'.'+b.name + '; alter table '+e.name+'.'+b.name + ' drop column jlz',
c.reserved_page_count,a.name,e.name+'.'+b.name,a.type_desc,*
FROM database.sys.indexes a
INNER JOIN database.sys.objects b ON b.object_id = a.object_id
INNER JOIN database.sys.schemas e ON e.schema_id = b.schema_id
INNER JOIN database.sys.dm_db_partition_stats c ON c.index_id = a.index_id AND c.object_id = b.object_id
Inner join database.sys.data_spaces d on a.data_space_id = d.data_space_id
WHERE
d.name = 'PRIMARY' and a.type_desc='HEAP'
ORDER BY c.reserved_page_count DESC

--SELECT 'CREATE CLUSTERED INDEX I ON ' +e.name+'.'+b.name + ' (' + f.name + ') on [STAGING_NEW]; drop INDEX I ON '+e.name+'.'+b.name,
--c.reserved_page_count,a.name,e.name+'.'+b.name,a.type_desc,*
--FROM database.sys.indexes a
--INNER JOIN database.sys.objects b ON b.object_id = a.object_id
--INNER JOIN database.sys.schemas e ON e.schema_id = b.schema_id
--INNER JOIN database.sys.dm_db_partition_stats c ON c.index_id = a.index_id AND c.object_id = b.object_id
--Inner join database.sys.data_spaces d on a.data_space_id = d.data_space_id
--inner join database.sys.columns f on f.object_id = b.object_id and f.column_id=1
--WHERE
--d.name = 'STAGING' and a.type_desc='HEAP' and b.name like '%_bkp'
--ORDER BY c.reserved_page_count DESC

the following lets me check the log to ensure i am minimal logging, if you see 'LOP_INSERT_ROWS' then you arent minimal logging

SELECT Operation,AllocUnitName FROM fn_dblog (DEFAULT, DEFAULT) WHERE Operation = 'LOP_INSERT_ROWS'

 

Capturing Execution Plans for Overnight Processes

September 13, 2017

Scenario: I want to capture execution plans for a particular overnight process. Maybe I want to check what indexes a query is using at 3AM and compare its index usage at 7AM

Add the following code to your procedure:

DECLARE @xyz VARBINARY(MAX) = CAST(‘ANY TEXT HERE’ AS VARBINARY(MAX))
SET CONTEXT_INFO @xyz

Select @xyz and obtain the variable value

For example: 0x646174616D6172742E7573705F646D5F637573746F6D6572726567697374726174696F6E

Remove the initial 0x and make a note of the rest of the value

Create an Extended Event

add event “Query Post Execution Showplan”

add filter sqlserver.context_info equal binary data 646174616D6172742E7573705F646D5F637573746F6D6572726567697374726174696F6E

add an event file to store the output and start the Extended Event. Dont use ring buffer, I cant get the process to work with ring buffer although admitedly I havent really persevered.

Let the overnight process complete and the next day right click the event file and View Target Data, click on a record and change to Query Plan tab in the bottom half of your screen to view the query plan.