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);

performance tuning note

December 8, 2015

dont forget pivots and recursive cte’s are great on small datasets but they dont scale well

  • pivots can be replaced with joins and case statements
  • recursive cte’s can be replaced with while loops and insert statements

Check if you have a disconnected remote desktop session

November 16, 2015

1. open up powershell
2. QUERY USER username /server:servername

“No user exists for username”

will displayed if you do not have a disconnected rdp

Error enumerating session names, the rpc server is unavailable will be displayed if the servername you entered doesnt exist

create partition scheme strings

November 13, 2015

SELECT
i.object_id,OBJECT_NAME(i.object_id) table_name,
QUOTENAME(s.name) + ‘(‘ + z1.name + ‘)’ partition_scheme
from sys.indexes i
join sys.partition_schemes s on i.data_space_id = s.data_space_id
JOIN sys.index_columns z ON z.index_id = i.index_id AND z.object_id = i.object_id
JOIN sys.columns z1 ON z1.object_id = z.object_id AND z1.column_id = z.column_id