- Details
- Written by: po3dno
- Category: MSSQL
- Hits: 866
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
- Hits: 890
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 |Set-ClusterParameter -Name RegisterAllProvidersIP -Value 0
Get-ClusterResource sql01_sme_AG_sql01-sme1c | Update-ClusterNetworkNameResource
- Details
- Written by: po3dno
- Category: MSSQL
- Hits: 930
ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [DBName] set multi_user
GO
- Details
- Written by: po3dno
- Category: MSSQL
- Hits: 1012
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. ******************************************************************************/
- Details
- Written by: po3dno
- Category: MSSQL
- Hits: 845
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"