sample winscp script

June 22, 2017
option batch on
option confirm off
open USERNAME:PASSWORD@SERVER
option transfer binary
get *part_of_file_name* -filemask=>24h “output_path”
close
exit
* is a wildcard
output path must have the trailing \
to call:
“file_path\WinSCP.com” /script=”file_path”
no spaces apart from potentially in filepath and before /script

How to quickly shrink/remove a database file

March 10, 2017

How to quickly shrink/remove a database file

Quickly is a relative term and this could mean turning a 2 week task into a 2 day task.

Normally what you would do if you wanted to remove a datafile is clear it of data and the remove it. You would use the DBCC SHRINKFILE (LogicalFileName, EMPTYFILE) command to do this.

You might also instead of shrinking a file, delete it and then recreate it at the desired size

Depending on the size of the file this could take days, maybe even weeks. I have witnessed the empty file moving data at a rate of 1MB a minute so if your file is several TB it will take a very long time.

However there are things we can do to speed it up

First of all put your file into read only mode you can do this using DBCC SHRINKFILE (LogicalFileName, EMPTYFILE). From books online “Emptyfile assures you that no new data will be added to the file.” which is exactly what we want

We now want to move all the tables and indexes off the file and onto other files in the filegroup we do this by rebuilding all the heaps and indexes. Use the following code to generate scripts

select concat(‘exec (”alter table ‘,quotename(d.name),’.’,quotename(object_name(a.object_id)), ‘ rebuild”)’,’
GO’) a ,reserved_page_count ,b.index_id
from sys.indexes a with(NOLOCK)
inner join sys.dm_db_partition_stats b with(NOLOCK) on a.object_id = b.object_id
inner join sys.objects c with(NOLOCK) on c.object_id = a.object_id
inner join sys.schemas d with(NOLOCK) on d.schema_id = c.schema_id
where a.type_desc = ‘Heap’ and data_space_id = 1 and a.index_id = b.index_id and c.type = ‘U’
UNION ALL
select DISTINCT concat(‘exec(”alter INDEX ALL ON ‘,quotename(d.name),’.’,quotename(object_name(a.object_id)), ‘ rebuild”)’,’
GO’) a ,reserved_page_count ,1
from sys.indexes a with(NOLOCK)
inner join sys.dm_db_partition_stats b with(NOLOCK) on a.object_id = b.object_id
inner join sys.objects c with(NOLOCK) on c.object_id = a.object_id
inner join sys.schemas d with(NOLOCK) on d.schema_id = c.schema_id
where data_space_id = 1 and a.index_id = b.index_id and c.type = ‘U’
ORDER BY 2 DESC
Before running this code you will need to disable any columnstore indexes on your tables
select concat(‘ alter index ‘, a.name , ‘ on ‘,c.name,’.’,b.name , ‘ disable’) from sys.indexes a
inner join sys.objects b on a.object_id = b.object_id
inner join sys.schemas c on b.schema_id = c.schema_id
where a.type > 5

When this has completed you can check the amount of space left using the following code

IF (SELECT OBJECT_ID(‘tempdb..#a’)) IS NOT NULL DROP TABLE #a
IF (SELECT OBJECT_ID(‘tempdb..#b’)) IS NOT NULL DROP TABLE #b
IF (SELECT OBJECT_ID(‘tempdb..#c’)) IS NOT NULL DROP TABLE #c
SELECT DB_NAME(a.database_id) db_name ,
a.name ,
CAST(( 8.0 * size ) / 1024.0 AS DEC(10, 2)) size ,
growth ,
max_size ,
is_percent_growth ,
‘USE ‘ + QUOTENAME(DB_NAME(a.database_id))
+ ‘;SELECT (8.0*FILEPROPERTY(”’ + a.name
+ ”’,”SpaceUsed”))/1024.0,”’ + DB_NAME(a.database_id) + ”’,”’
+ a.name + ”” get_file_size
INTO #a
FROM sys.master_files a
INNER JOIN sys.databases b ON b.database_id = a.database_id
WHERE b.state_desc = ‘ONLINE’ AND b.name !=’tempdb’ –master files will not give accurate data for temp log;
AND b.user_access_desc = ‘MULTI_USER’
UNION ALL
SELECT ‘tempdb’ db_name ,
a.name ,
CAST(( 8.0 * size ) / 1024.0 AS DEC(10, 2)) size ,
growth ,
max_size ,
is_percent_growth ,
‘USE [tempdb]’
+ ‘;SELECT (8.0*FILEPROPERTY(”’ + a.name
+ ”’,”SpaceUsed”))/1024.0,”’ + ‘tempdb”,”’
+ a.name + ”” get_file_size
FROM tempdb.sys.database_files a
SELECT get_file_size INTO #b FROM #a

WHILE EXISTS (SELECT 1 FROM #b)
BEGIN
DECLARE @sql VARCHAR(MAX) = (SELECT TOP 1 get_file_size FROM #b ORDER BY 1)
DECLARE @a TABLE (size DEC(10,2),name_db VARCHAR(100),name_file VARCHAR(100))

INSERT INTO @a
EXEC(@Sql)

DELETE FROM #b WHERE get_file_size = @sql
END

SELECT name_db,name_file,a.size,a.size-b.size AS free_space,CAST(100*(a.size-b.size)/a.size AS DEC(10,2)) free_space_percentage,a.growth,a.is_percent_growth,GETDATE() collection_time
INTO #c
FROM #a a
INNER JOIN @a b ON a.name = b.name_file AND a.db_name = b.name_db
SELECT * FROM #c

If there is still a significant amount of space being used in the file you can check what it by using the following

select top 1 * from sys.dm_db_database_page_allocations(7,NULL,NULL,NULL,’limited’) where allocated_page_file_id = 8 and object_id > 100
Where 7 is the database id, I use top 1 because I find the code can take more than 24 to return a complete data set

you can then rebuild the heap and tables to shift the data off the file

Formatting HTML for send_dbmail proc

March 2, 2017

taken from: http://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email

Procedure

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO

Usage

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Foo',
    @recipients = 'bar@baz.com;',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

 

 

Shrinking a database

February 14, 2017
I used the following scripts to shrink down a database.

1 create a new filegroup + files
2. move all heaps to the new filegroup
3. move all indexes to the new filegroup
4. Get the files for the old filegroup right
5. Move the tables back and drop any clustered indexes created in (2)
6. Remove the new files and filegroup

--this will create a clustered index on heaps on the new filegroup, 167 refers to varchar 8000, which is none indexable

SELECT 'create clustered index jlc on ' + d.name + '.' + c.name+ '('+ QUOTENAME(e.name) + ') on [secondary]',* FROM sys.indexes a
INNER JOIN sys.data_spaces b ON b.data_space_id = a.data_space_id
INNER JOIN sys.objects c ON c.object_id = a.object_id
INNER JOIN sys.schemas d ON d.schema_id = c.schema_id
INNER JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY column_id) r FROM sys.columns WHERE system_type_id != 167 AND user_type_id !=167) e ON e.object_id = a.object_id AND e.r = 1
WHERE b.name = 'Primary' AND c.type = 'U' AND a.type_desc = 'HEAP'

--this will drop the above indexes

SELECT 'DROP INDEX jlc ON ' + c.name + '.' + b.name FROM sys.indexes a
INNER JOIN sys.objects b ON b.object_id = a.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
WHERE a.name = 'jlc'



--change the filegroup name in two separate places
--this will move indexes to the new filegroup 
--found on google, then modified

SELECT ' CREATE ' +
 CASE 
 WHEN I.is_unique = 1 THEN ' UNIQUE '
 ELSE ''
 END +
 I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
 QUOTENAME(I.name) + ' ON ' +
 SCHEMA_NAME(T.schema_id) + '.' + QUOTENAME(T.name) + ' ( ' +
 KeyColumns + ' ) ' +
 ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
 ISNULL(' WHERE ' + I.filter_definition, '') + ' WITH ( ' +
 CASE 
 WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
 ELSE ' PAD_INDEX = OFF '
 END + ',' +
 'FILLFACTOR = ' + CONVERT(
 CHAR(5),
 CASE 
 WHEN I.fill_factor = 0 THEN 100
 ELSE I.fill_factor
 END
 ) + ',' +
 -- default value 
 'SORT_IN_TEMPDB = OFF ' + ',' +
 CASE 
 WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
 ELSE ' IGNORE_DUP_KEY = OFF '
 END + ',' +
 CASE 
 WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
 ELSE ' STATISTICS_NORECOMPUTE = ON '
 END + ',' +
 ' ONLINE = OFF ' + ',' +
 CASE 
 WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
 ELSE ' ALLOW_ROW_LOCKS = OFF '
 END + ',' +
 CASE 
 WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
 ELSE ' ALLOW_PAGE_LOCKS = OFF '
 END + ',DROP_EXISTING = ON ) ON [' +
 'PRIMARY' + ' ] ' + CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM sys.indexes I
 JOIN sys.tables T
 ON T.object_id = I.object_id
 JOIN sys.sysindexes SI
 ON I.object_id = SI.id
 AND I.index_id = SI.indid
 JOIN (
 SELECT *
 FROM (
 SELECT IC2.object_id,
 IC2.index_id,
 STUFF(
 (
 SELECT ' , ' + QUOTENAME(C.name) + CASE 
 WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
 = 1 THEN 
 ' DESC '
 ELSE 
 ' ASC '
 END
 FROM sys.index_columns IC1
 JOIN sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 
 0
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY
 IC1.object_id,
 C.name,
 index_id
 ORDER BY
 MAX(IC1.key_ordinal) 
 FOR XML PATH('')
 ),
 1,
 2,
 ''
 ) KeyColumns
 FROM sys.index_columns IC2 
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY
 IC2.object_id,
 IC2.index_id
 ) tmp3
 )tmp4
 ON I.object_id = tmp4.object_id
 AND I.Index_id = tmp4.index_id
 JOIN sys.stats ST
 ON ST.object_id = I.object_id
 AND ST.stats_id = I.index_id
 JOIN sys.data_spaces DS
 ON I.data_space_id = DS.data_space_id
 JOIN sys.filegroups FG
 ON I.data_space_id = FG.data_space_id
 LEFT JOIN (
 SELECT *
 FROM (
 SELECT IC2.object_id,
 IC2.index_id,
 STUFF(
 (
 SELECT ' , ' + C.name
 FROM sys.index_columns IC1
 JOIN sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 
 1
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY
 IC1.object_id,
 C.name,
 index_id 
 FOR XML PATH('')
 ),
 1,
 2,
 ''
 ) IncludedColumns
 FROM sys.index_columns IC2 
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY
 IC2.object_id,
 IC2.index_id
 ) tmp1
 WHERE IncludedColumns IS NOT NULL
 ) tmp2
 ON tmp2.object_id = I.object_id
 AND tmp2.index_id = I.index_id
WHERE ds.name = 'Secondary'

Unable to start up Server Instance

February 6, 2017

So I had a little panic situation earlier today however it is now resolved and this is how I resolved it.

The Preprod instance was down and I could not get it back up again.

When I tried to start the instance I received an error message telling me that “The request failed or the service did not respond in a timely fashion”

I checked event viewer, but there nothing helpful there “The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.”

I then tried to start the service using services.msc again no good “This service on local computer started and then stopped. some service stop automatically if then are not in use by other service or programs ”

I then checked the log file “E:\Microsoft SQL Server\MSSQL11.PREPROD\MSSQL\Log\ERRORLOG” Also unhelpful “Failed allocate pages: FAIL_PAGE_ALLOCATION 1”

Finally I rememeber changing the memory setting on preprod earlier today and allocating all it’s to memory to the default instance and so I used the startup parameter -f , which starts the instance up in minimal config mode, to quote books online “This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.” This was successful I got the instance up and running in min config mode :>D

I now logged in on the DAC connection and ran EXEC sys.sp_configure. max server memory (MB) had a config value of 128 and a running value of 2147483647.

I ran the following code
EXEC sys.sp_configure N’max server memory (MB)’, N’10000′
GO
RECONFIGURE WITH OVERRIDE
GO

we now had a config value of 10000 and a run value of 10000

I stopped the server, removed the minimal config startup parameter and restarted the service. Success.

Looking through the logs there is no record from when I changed the maximum memory value earlier in the day so I assume something must have gone wrong and it took a stupidly large value in error which caused the troubles.

Anyway it is now fixed. Yay.

 

Change the SQL management studio program name

January 9, 2017

At connection time in the additional connection parameters box as an example specify

application name = testing 123

 

 

get the contents of a file system

November 28, 2016

create PROC procname

@drive CHAR(1)
AS
SET NOCOUNT ON

DECLARE @dynamic_sql VARCHAR(1000)

SET @dynamic_sql = CONCAT(‘EXEC xp_cmdshell ”dir ‘,@drive,’:\ /a /s”’)
PRINT @dynamic_sql

CREATE TABLE #t (id INT IDENTITY(1,1), output VARCHAR(MAX))

INSERT INTO #t
EXEC (@dynamic_sql)

DELETE FROM #t WHERE output IS NULL
DELETE FROM #t WHERE output LIKE ‘%<DIR>%’
DELETE FROM #t WHERE output LIKE ‘%file%bytes’
DELETE FROM #t WHERE output LIKE ‘%dir%bytes’
DELETE FROM #t WHERE output LIKE ‘%dir%bytes’
DELETE FROM #t WHERE output =’ Total Files Listed:’
DELETE FROM #t WHERE output LIKE ‘%Volume in drive%’
DELETE FROM #t WHERE output LIKE ‘%Volume Serial Number%’
DELETE FROM #t WHERE output LIKE ‘%Dir%bytes free’
DELETE FROM #t WHERE output LIKE ‘%The directory name%’
DELETE FROM #t WHERE output LIKE ‘%is too long%’
DELETE FROM #t WHERE output LIKE ‘%<JUNCTION>%’
DELETE FROM #t WHERE output LIKE ‘%<SYMLINKD>%’
ALTER TABLE #t ADD data_type int

UPDATE #t SET data_type = 0 WHERE output LIKE ‘ Directory%’
UPDATE #t SET data_type = 1 WHERE data_type IS NULL

UPDATE #t SET output = LTRIM(SUBSTRING(output,18,1000)) FROM #t WHERE data_type = 1

ALTER TABLE #t ADD first_space INT

UPDATE #t SET first_space = CHARINDEX(‘ ‘,output) FROM #t WHERE data_type = 1

DELETE FROM #t WHERE first_space = 0 AND data_type = 1

ALTER TABLE #t ADD size BIGINT,filename VARCHAR(1000)
UPDATE #t SET SIZE = REPLACE(SUBSTRING(output,1,first_space-1),’,’,”),filename = SUBSTRING(output,first_space+1,1000) FROM #t WHERE data_type =1

CREATE clustered INDEX i ON #t (id)

ALTER TABLE #t ADD directory VARCHAR(1000)

DECLARE @row BIGINT = 1,@max BIGINT

SELECT @max = MAX(id) FROM #t

WHILE @row <= @max
BEGIN

DECLARE @data_type INT, @outputvalue VARCHAR(1000), @directory VARCHAR(1000)

SELECT @data_type = data_type,@outputvalue = output FROM #t WHERE id = @row

IF @data_type = 0 SET @directory = @outputvalue

IF @data_type = 1
UPDATE #t SET directory = REPLACE(@directory,’ Directory of ‘,”) FROM #t WHERE id = @row

SET @row +=1
end

DELETE FROM #t WHERE data_type = 0

IF (SELECT OBJECT_ID(‘table’)) IS NULL
EXEC (‘SELECT TOP 0 filename,directory,size,GETDATE() capture_time INTO table FROM #t ‘)

INSERT INTO table
SELECT filename,directory,size,GETDATE() capture_time FROM #t ORDER BY size DESC

shrink db file script in increments

October 5, 2016
DECLARE @startvalue BIGINT = 250000
,@targetsize BIGINT = 50000
,@stepsize BIGINT = 1000
,@filename VARCHAR(MAX) = 'xxx'

WHILE @startvalue >=@targetsize
BEGIN

SET @startvalue = @startvalue-@stepsize

DECLARE @shrinkstatement VARCHAR(MAX) = 
 'DBCC SHRINKFILE(N''' + @filename + ''',' + CAST(@startvalue AS VARCHAR(100)) + ')'

PRINT @shrinkstatement
 EXEC (@shrinkstatement)

END

Backup all ssis packages to disk

April 21, 2016

ssis package backup – rename as dtsx

See attachment, change extension to dtsx

Querying the plan cache to discover which queries are using an index

March 8, 2016

DECLARE @IndexName AS NVARCHAR(128) = ‘name of index here’;

IF (LEFT(@IndexName, 1) <> ‘[‘ AND RIGHT(@IndexName, 1) <> ‘]’) SET @IndexName = QUOTENAME(@IndexName);

IF LEFT(@IndexName, 1) <> ‘[‘ SET @IndexName = ‘[‘+@IndexName;
IF RIGHT(@IndexName, 1) <> ‘]’ SET @IndexName = @IndexName + ‘]’;
;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan&#8217;)
SELECT
stmt.value(‘(@StatementText)[1]’, ‘varchar(max)’) AS SQL_Text,
obj.value(‘(@Database)[1]’, ‘varchar(128)’) AS DatabaseName,
obj.value(‘(@Schema)[1]’, ‘varchar(128)’) AS SchemaName,
obj.value(‘(@Table)[1]’, ‘varchar(128)’) AS TableName,
obj.value(‘(@Index)[1]’, ‘varchar(128)’) AS IndexName,
obj.value(‘(@IndexKind)[1]’, ‘varchar(128)’) AS IndexKind,
cp.plan_handle,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple’) AS batch(stmt)
CROSS APPLY stmt.nodes(‘.//IndexScan/Object[@Index=sql:variable(“@IndexName”)]’) AS idx(obj)
OPTION(MAXDOP 1, RECOMPILE);