Archive for the ‘Uncategorized’ Category

Shrinking database files

October 22, 2018

Prevent New Data from Being Written to a File

The database file will not shrink in a timely fasion while there is data in the file. So remove it. On a daily basis tables our tables are dropped and recreated. The fastest way to remove data is to let automatic process drop tables and not allow the processes to recreate the tables on the file we are trying to shrink.We can make a file readonly by using DBCC SHRINKFILE (‘filenname’,EMPTYFILE), as long as the command is running it will 1. not allow new data to be written to the file 2. slowly move data out of the file. The command frequently fails with a deadlock allowing data to be written to the file so by using the following work in procedure we can tell the proc to restart on failure.

CREATE PROC [dbo].[emptyfile_new]
@dbase VARCHAR(100) ,
@log_file_name VARCHAR(1000)
AS
SET DEADLOCK_PRIORITY 10;

BEGIN TRY

DECLARE @sql VARCHAR(MAX) = 'USE ' + @dbase + '; DBCC SHRINKFILE (N''' + @log_file_name + ''' , EMPTYFILE)';

EXEC (@sql);

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE();
EXEC test.dbo.emptyfile_new;

END CATCH;

Leave the procedure running for the file you want to empty, if you want to empty multiple file, run the procedure multiple times in different sessions using different parameters.

Columnstore Indexes

You can not use shrinkfile on a file that contains a table with a columnstore index on it, move any table to a new temporary filegroup. Make sure to transfer any compression setting, indexes, keys, constraints.

You can not use shrinkfile on a file that contains a columnstore index. Move any columnstore indexes to a new tempory filegroup.

How much Data is in your file

SELECT FILEPROPERTY(‘filename’,’SpaceUsed’)

This will return the number of pages in your file. Multiply by 8 to get the number of kb.

What data is contained in my file

We can see how many pages of data each table/index has in the database using the following code:

USE DATABASE_NAME

select object_id,index_id,allocation_unit_type_desc,COUNT(*) c
into #a
from sys.dm_db_database_page_allocations(DATABASE_ID,NULL,NULL,NULL,'limited') where allocated_page_file_id = DATABASE_FILE_NUMBER
group by object_id ,index_id,allocation_unit_type_desc

Select b.name,c.name,d.name
,CASE WHEN a.index_id IN (0,1) THEN 'alter table '+ c.name + '.' + b.name+ ' rebuild'
WHEN a.index_id > 1 THEN 'ALTER INDEX ' + d.name + ' on '+ c.name + '.' + b.name + ' rebuild'
END
INNER JOIN sys.objects b ON a.object_id = b.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
INNER JOIN sys.indexes d ON d.index_id = a.index_id AND d.object_id = a.object_id

Move Remaining Data

heaps and clustered indexes can be moved using ALTER TABLE X.X REBUILD

Other indexes can be moved using ALTER INDEX x ON x.x REBUILD

If these tables or indexes contain lob data then rebuilding will not move this instead copy data to a holding table, truncate the original table and move it back to the original table, while the emptyfile command is running.

You can identity Lob data on the file using the above SQL allocation_unit_type_desc field.

Check Page Contents

Occasionally you might get an error that where you are told a page cannot be moved, use the following to check the page contents

DBCC TRACEON (3604)
DBCC PAGE ('DATABASE_NAME',DATABASE_FILENUMBER,PAGE,1) WITH TABLERESULTS

Switch the final parameter for a 3 to actual data on the page

You may be told that a file can not be shrunk as it is being shrunk by another process – even though it is not. In this case increase the file size by a small amount such as 10MB and retry your shrink.

Advertisements

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.

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

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

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.

Detecting Database access via AD group membership

September 4, 2017

SET NOCOUNT on

SELECT id=IDENTITY (int, 1,1),’exec xp_logininfo ”’ +name +”’,”members”’ code

INTO #t

FROM sys.server_principals WHERE type = ‘G’

–SELECT * FROM #t

CREATE TABLE #r (account_name VARCHAR(100),type_desc VARCHAR(100),privilege VARCHAR(100),mapped_login_name VARCHAR(100),permission_path VARCHAR(100))

DECLARE @maxid INT = (SELECT MAX(id) FROM #t)

DECLARE @currentid INT = 1

WHILE @currentid<=@maxid

BEGIN

DECLARE @code VARCHAR(MAX) = (SELECT code FROM #t WHERE id = @currentid)

–PRINT @code

INSERT INTO #r

EXEC (@code)

SET @currentid = @currentid +1

END

IF OBJECT_id('database_name.dbo.permissions_table') IS NULL

SELECT *,GETDATE() date_captured

INTO ('database_name.dbo.permissions_table

FROM #r

IF OBJECT_id('('database_name.dbo.permissions_table_audit') IS NULL

SELECT TOP 0 *,GETDATE() date_captured, 'A' record_type

INTO ('database_name.dbo.permissions_table_audit

FROM #r

–detect additons

SELECT *,GETDATE() date_captured

INTO #additions

FROM

(

SELECT * FROM #r

EXCEPT

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path

FROM ('database_name.dbo.permissions_table

) a

INSERT INTO ('database_name.dbo.permissions_table_audit

SELECT *,'A' FROM #additions

INSERT INTO ('database_name.dbo.permissions_table

SELECT * FROM #additions

–detect deletions

SELECT *,GETDATE() date_captured

INTO #deletions

FROM

(

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path

FROM ('database_name.dbo.permissions_table

EXCEPT

SELECT * FROM #r

) a

INSERT INTO ('database_name.dbo.permissions_table_audit

SELECT *,'D' FROM #deletions

DELETE FROM ('database_name.dbo.permissions_table

FROM ('database_name.dbo.permissions_table

INNER JOIN (

SELECT * FROM

(SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path FROM #deletions

INTERSECT

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path FROM ('database_name.dbo.permissions_table) a ) a

ON a.account_name = permissions_table.account_name AND a.permission_path = permissions_table.permission_path