Archive for February, 2017

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.