CREATEPROCEDURE [<procedureName>] /* parameters go here */ WITHNATIVE_COMPILATION,SCHEMABINDING,EXECUTEASOWNER ASBEGINATOMICWITH ( DELAYED_DURABILITY=ON, TRANSACTIONISOLATIONLEVEL=SNAPSHOT, LANGUAGE=N'English' ) /* procedure body goes here */ END
Msg 3202, Level 16, State 1, Line 161 Write on 's3://<endpoint>:<port>/<bucket>/<path>/<db_name>.bak' failed: 87(The parameter is incorrect.) Msg 3013, Level 16, State 1, Line 161 BACKUP DATABASE is terminating abnormally.
SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL - 'https://.blob.core.windows.net//.bak', leaving 223 characters for account, container, and blob names put together'
Msg 3201, Level 16, State 1, Line 28 Cannot open backup device '<path>'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 28 BACKUP DATABASE is terminating abnormally.
Windows 에서SetFileValidData는단일메타데이터작업이다. VDL이설정되면쓰기(순차또는임의)는VDL == EOF로추가메타데이터업데이트가필요하지않다. Linux에서쓰기에는데이터쓰기및메타데이터쓰기가필요한익스텐트업데이트가필요하다. Linux 또는Windows에서가능한한빨리파일을쓰고확장할수있다. 그러나Linux에서처음쓰기를수행하면메타데이터가유지관리된다.
이번포스트에서는QPI 라이브러리를사용하여관리형인스턴의대기통계를쉽게분석하고있다.QPI 라이브러리를설치하려면아래링크로이동하여SQL Server 버전에대한SQL 스크립트를다운로드한다. QPI 라이브러리는Query Store 보기에의존하기때문에SQL Server 2016이상, Azure SQL 에서사용이가능하다.
Disclaimer: QPI library is open source library provided as-is and not maintained by Microsoft. There are not guarantees that the results are correct and that there are not bugs in calculations. This is a helper library that can help you to more easily analyze performance of your Managed Instance, but you can do the same job by looking directly at DMVs.
SQL Server 2008 이후로SQL Server Agent Job에서는 syspolicy_purge_history라는job이등록되어있다. 해당Job은MSDB에쌓여있는히스토리를정리하는작업을진행한다. 가끔해당Job 실행시아래와같은오류가발생할수있는데, 오류가무엇인지확인해보고해결하는방법에대해서살펴본다.
Job이실패하였을때, 해당로그를살펴보면아래와같은오류로그가발생하였다.
Date7/29/2019 1:23:41 PM
LogJob History (syspolicy_purge_history)
Step ID3
Server
Job Namesyspolicy_purge_history
Step NameErase Phantom System Health Records.
Duration00:00:00
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script:A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.'A job step received an error at line 1 in a PowerShell script. The corresponding line is 'SQLSERVER:'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'SQLSERVER:' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.'.Process Exit Code -1.The step failed.
SQL Server에서파티션테이블을사용하기위해서는SQL Server Enterprise Edition을사용해야한다. Standard Edition에서도파티션테이블처럼사용하기위해서는새로운테이블을생성하고sp_rename 함수를사용하여기존테이블과새로운테이블의이름을변경하는방식으로사용하였다.
ALTER TABLE SWITCH TO 명령은기존테이블을유지하면데이터세트를스와핑힌다. 이명령을사용하기위해서는스키마정보가호환가능해야한다. 컬럼이름, 컬럼순서, 조약조건등특성이동일해야한다.
-- Replace live with staging
BEGINTRAN
TRUNCATETABLE DataTable;
ALTERTABLE DataTable_Staging SWITCH TO DataTable;
COMMIT
-- Swap live and staging
/* Note: An extra table, DataTable_Old, is required to temporarily hold the data being replaced before it is moved into DataTable_Staging. The rename-based approach did not require this extra table. */
BEGINTRAN
ALTERTABLE DataTable SWITCH TO DataTable_Old;
ALTERTABLE DataTable_Staging SWITCH TO DataTable;
ALTERTABLE DataTable_Old SWITCH TO DataTable_Staging;
Need to be dropped before tables, as described below.
2
V
View
Should be dropped before tables, since they can have direct or indirect SCHEMABINDING.
FN
SQL scalar function
IF
SQL inline table-valued function
TF
SQL table-valued-function
3
P
Stored procedure
Need to be dropped before tables, but after views and functions (since procedures can reference views and functions, but can't be referenced by them except in rare cases using OPENQUERY()).
4
U
Table (user-defined)
Dropped after foreign keys and any type of module that can reference them with SCHEMABINDING.
5
TT
Table type
Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types.
Alias types
6
SO
Sequence object
Dropped after tables/modules because they can't be dropped if any table or module references them.
R
Rule (old-style, stand-alone)
D
Old-style CREATE DEFAULT
Partition functions
7
Partition schemes
Can't be dropped until after partition functions.
8
SN
Synonym
I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them).
9
Schemas
I drop these almost last because almost all entities above can belong to a certain schema. For any entities you want to keep but move to a different schema, you'll need to first use ALTER SCHEMA ... TRANSFER.
10
Roles and Users
For users you don't want to keep, you'll need to first remove them from user-defined role membership and ownership, as well as ownership of any schemas.
(For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.)
For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles.
SQL Server Enterprise Edition 에서SQL Server Standard Edition으로다운그레이드후확인해야할몇가지사항에대해서알아본다. SQL Server Enterprise Edition을SQL Server Standard Edition으로다운그레이드후일부구성이기본값으로다시설정된다.
[SQL Server 오류로그수]
SQL Server 오류로그파일의수가기본6개로재설정된다. 이설정을확인하고필요한수로설정한다. SSMS의GUI를사용할수도있으며T-SQL 코드를사용할수있다.
