SQL Server Search for index usage

 SELECT 

OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 

i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,

s.user_updates AS [Total Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],

s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND i.name='IX_WIPIN' -- your index name here

AND i.index_id > 1 AND i.[type_desc] = N'NONCLUSTERED'

AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

SQL Server Anonymous Block

 set nocount off;

declare @row as bigint;

declare @qty as int;

declare @yr as int;

set @qty=100;

set @yr=2017;

begin

delete from sample_table

insert into dx_rowcounter(modulename,rowcountt) values(concat(@yr,'_',@qty),@@rowcount)

end

SQL SERVER Last Restart

 SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Cannot connect to a newly installed SQL SERVER

 https://knowledgebase.apexsql.com/configure-remote-access-connect-remote-sql-server-instance-apexsql-tools/

Change Always on Availability group ownership

 USE [master]

GO

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB3] TO [ECP\etrace-lg-svc];

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB4] TO [ECP\etrace-lg-svc];

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB5] TO [ECP\etrace-lg-svc];

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB6] TO [ECP\etrace-lg-svc];

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB7] TO [ECP\etrace-lg-svc];

GO

RESTORE FILELISTONLY FROM DISK = ‘E:\Backup\PeterDatabase.bak’

RESTORE DATABASE ITAMS2 FROM DISK ='G:\SQL_BKP\WINDRUNNER_ITAMS_FULL_20201109_200001.bak'

WITH MOVE 'ITAMS_new.mdf' TO 'H:\SQL_DB\ITAMS_new2.mdf',

MOVE 'ITAMS_new_log.ldf' TO 'E:\SQL_LOG\ITAMS_new_log2.ldf’

How to Move user login between SQL Servers

 move user to new instance

https://docs.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances 

Cannot save sql server database diagram

 For example the database is adventureworks and you want to save the diagram there.

Connect to adventureworks db and then issue this command.

DROP TABLE dbo.sysdiagrams;
GO
CREATE TABLE [dbo].[sysdiagrams]
(
    [name] [sysname] NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] IDENTITY(1,1) PRIMARY KEY,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL,
    CONSTRAINT [UK_principal_name] UNIQUE ([principal_id],[name])
);

GO
EXEC sys.sp_addextendedproperty 
  @name=N'microsoft_database_tools_support', 
  @value=1 , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'sysdiagrams';
GO

Then save it again