Отключчение режима Restricted User в SQL Server

Use DatabaseName
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO

http://blog.lekevin.com/computer/remove-restricted-user-in-sql-server-2008/

he DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'. The statement has been terminated. (.Net SqlClient Data Provider)

"The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (.Net SqlClient Data Provider)"


SCRIPT

declare @jobname varchar(100)

select @jobname='PASS YOUR NAME OF THE JOB'

delete from msdb.dbo.sysmaintplan_log
where plan_id in(
select plan_id from msdb..sysmaintplan_subplans
where job_id in(
select job_id from sysjobs where name =@jobname))

delete from sysmaintplan_subplans
where job_id in(
select job_id from sysjobs where name =@jobname)


http://sugeshkr.blogspot.com/2008/05/sql-server-job-deletion-error.html

Как отключить Auto Shrink во всех базах SQL

/* This will script output the needed commands for you */
/* Execute it in query analyzer, which will generates all the commands */
/* do a Select All, Copy, then Paste into query analyzer and execute */
SELECT 'ALTER DATABASE'  + QUOTENAME(name) + 'SET AUTO_SHRINK OFF WITH NO_WAIT;' AS [To Execute]
FROM sys.databases
WHERE is_auto_shrink_on = 1;
GO


https://enterpriseit.co/ms-sql/disable-auto-shrink-all-databases/

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{806835AE-FD04-4870-A1E8-D65535358293}
 and APPID
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
 to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.


1. Запускаем консоль comexp.msc (Component Services management console).
2.  Разворачиваем Component services> My computer> DCOM Config> Находим "Microsoft SQL Server Integration Services 12.0"

3. Нажимаем правой кнопкой и выбираем Properties

4.Даем полные права  (Launch and Activation, Access, and Configuration) учетной записи NT SERVICE\SQLSERVERAGENT на вкладке Security.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c7d6f25d-bd5c-45f0-892e-5e02164bf74c/dcom-errors-on-sql-2012-on-server-2012-r2-new-clean-install?forum=sqlgetstarted

How to Check Index Fragmentation on Indexes in a Database

ELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

This query can be modified to focus on specific tables by append the table name to the 'where' clause:

WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'




The following table summarizes when to use each one:

Reference Values (in %)	Action	SQL statement
avg_fragmentation_in_percent > 5 AND < 30	Reorganize Index	ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30	Rebuild Index	ALTER INDEX REBUILD

https://www.schneider-electric.com/en/faqs/FA234246/

Присоединение БД без журнала транзакций / Attaching SQL Server database without log file

1. Создаем БЗ даных аналогично названию mdf файла.
2. Останавливаем SQL Server и заменяем файл MDF.
3. Запускаем SQL Server. База данных будет находится в состоянии Recovery Pending

4. Выполняем скрипт:

USE [master] 
GO 
ALTER DATABASE [MyDatabase] SET EMERGENCY 
GO 
ALTER DATABASE [MyDatabase] SET SINGLE_USER 
GO 
DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS) 
GO 
ALTER DATABASE [MyDatabase] SET MULTI_USER 
GO 
ALTER DATABASE [MyDatabase] SET ONLINE 
GO

На SQL Server 2014 все прошло успешно.

https://weblogs.asp.net/gunnarpeipman/attaching-sql-server-database-without-log-file

Выход из режима single-user в MS sql-server + 1С

1. Останавливаем сервер приложений 1С
2. Смотрим кто использует базу T-sql sp_who2
3. Убиваем всех пользователей использующих БД 1С KILL { session ID } 
4. Переводим базу в режим Multi_user

-- Start in master
USE MASTER;

-- Add users
ALTER DATABASE [my_db] SET MULTI_USER
GO

https://stackoverflow.com/questions/18965980/exit-single-user-mode

How do I shrink all files quickly for all databases?

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4;

https://dba.stackexchange.com/questions/358/how-do-i-shrink-all-files-quickly-for-all-databases

Как восстановить backup с SQL Server 2012 на SQL Server 2008 R2

Ошибка

Msg 1813, Level 16, State 2, Line 1 
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted. 
Msg 948, Level 20, State 1, Line 1 
The database 'DatabaseName' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

Решение
Нажимаем правой кнопкой по базе OUTLANDER в SQL Server 2012 выбираем в контекстном меню Task> Generate Scripts

В мастере  Generate and Publish нажимаем далее.

Выбираем "Script entire database and all database objects"

На шаге Set Scripting Options, указываем место куда будет сохранятся скрипт, и нажимаем кнопку Advanced.

В Advanced Scripting Options указываем параметры:

Script Triggers True

Script Indexes True

Script Primary Key True

Script for Server Version SQL Server 2008 R2

Types of data to script Schema and Data

Нажимаем Далее

Ждем генерацию скрипта и нажимае финиш.

Восстанавливаем БД на SQL Server 2008 R2

Указыаем расположение скрипта

Указываем располжожение журнала транзакций и  расположение лог файлов. Нажимаем кнопку Execute

https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/