Последние сборки SQL Server 2014

https://blogs.sentryone.com/team-posts/latest-builds-sql-server-2014/

Отключчение режима 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/

Как сменить пароль SA на SQL Server 2014?

1.  Если sql -server 2014 работает в смешанном режиме, то авторизуемся доменной учетной записью и открываем SQL Server Management Studio 
2. Разворачиваем "Security -> Logins"

3. Нажимаем правой кнопкой и выбираем Properties.  В свойствах переходим в раздел General.
в поле Password указываем новый пароль.

4. В разделе Status устанавливаем login -enabled

5. Снимаем галочку login is locked out

https://www.isumsoft.com/sql-server/how-to-change-sa-password-in-sql-server-2014.html

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