iksrv.work - MSSQL

Details: Written by: po3dno; Category: MSSQL; Created: 06 May 2019; Hits: 1009

CREATE PROCEDURE dbo.AddNewDBsToGroup
@group SYSNAME = N'sql01_sme_AG', -- *** SPECIFY YOUR GROUP NAME HERE ***
@debug BIT = 1
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@sql NVARCHAR(MAX) = N'';

DECLARE @t TABLE(db SYSNAME);

INSERT @t SELECT name FROM sys.databases
WHERE replica_id IS NULL AND database_id > 4;

-- add the database to the group on the primary:

SELECT @sql += N'ALTER AVAILABILITY GROUP '
+ QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(db) + ';'
FROM @t;

IF @debug = 1
BEGIN
PRINT @sql;
END
ELSE
BEGIN
EXEC master..sp_executesql @sql;
END
END
GO

EXEC dbo.AddNewDBsToGroup @debug = 0;

Details: Written by: po3dno; Category: MSSQL; Created: 28 December 2018; Hits: 1083

sql01_sme_AG_sql01-sme1c Online sql01_sme_AG Network Name

Get-ClusterResource sql01_sme_AG_sql01-sme1c |Set-ClusterParameter -Name RegisterAllProvidersIP -Value 0

Get-ClusterResource sql01_sme_AG_sql01-sme1c | Update-ClusterNetworkNameResource

Details: Written by: po3dno; Category: MSSQL; Created: 07 December 2018; Hits: 1109

ALTER DATABASE [DBName] SET EMERGENCY;

ALTER DATABASE [DBName] set single_user

DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

ALTER DATABASE [DBName] set multi_user

Details: Written by: po3dno; Category: MSSQL; Created: 25 October 2017; Hits: 1192

USE SUSDB
GO
DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389')

EXEC SUSDB.dbo.spDeclineExpiredUpdates;1

EXEC SUSDB.dbo.spDeclineSupersededUpdates;1

Для команды spCompressUpdate используется «обёртка»:

USE SUSDB
DECLARE @var1 INT, @curitem INT, @totaltodelete INT
DECLARE @msg nvarchar(200)
CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)
EXEC spGetUpdatesToCompress
SET @totaltodelete = (SELECT COUNT(*) FROM #results)
SELECT @curitem=1
DECLARE WC Cursor FOR SELECT Col1 FROM #results
OPEN WC
FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
BEGIN SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltodelete as varchar(5)) + ': Compressing ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
RAISERROR(@msg,0,1) WITH NOWAIT
EXEC spCompressUpdate @localUpdateID=@var1
SET @curitem = @curitem +1
FETCH NEXT FROM WC INTO @var1
END
CLOSE WC
DEALLOCATE WC
DROP TABLE #results


Такая же обертка для spDeleteUpdate:

USE SUSDB
DECLARE @var1 INT, @curitem INT, @totaltodelete INT
DECLARE @msg nvarchar(200)
CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)
EXEC spGetObsoleteUpdatesToCleanup
SET @totaltodelete = (SELECT COUNT(*) FROM #results)
SELECT @curitem=1
DECLARE WC Cursor FOR SELECT Col1 FROM #results
OPEN WC
FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
BEGIN SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltodelete as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
RAISERROR(@msg,0,1) WITH NOWAIT
EXEC spDeleteUpdate @localUpdateID=@var1
SET @curitem = @curitem +1
FETCH NEXT FROM WC INTO @var1
END
CLOSE WC
DEALLOCATE WC
DROP TABLE #results



/******************************************************************************  This sample T-SQL script performs basic maintenance tasks on SUSDB  1. Identifies indexes that are fragmented and defragments them. For certain     tables, a fill-factor is set in order to improve insert performance.     Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx     and tailored for SUSDB requirements  2. Updates potentially out-of-date table statistics.  ******************************************************************************/

AddNewDBsToGroup

SQL AG config multisubnets

Fix Recovery Pending State in SQL Server Database

Re-index the WSUS 3.0 Database

Удалить все таблицы из БД

Login Form

Statistics