Server实例计算

一台数据库服务器的事情日志备份作业不常会产出两次备份退步的情状,具体的错误消息为:

小编们一个SQL
Server服务器在实施YourSQLDBa的作业YourSQLDba_FullBackups_And_Maintenance时遇到了不当:

后天遭受二个案例,YourSQLDba做专门的工作日志备份时失利,检查YourSQLDba输出的错误新闻如下:

 

 

 

 

在SQL
Server的数据库维护进程中,有的时候候在部分新鲜景况下要求在单客商方式下运维SQL Server实例。
下边总括一下单客户形式运维SQL Server的两种情势:

 

Exec YourSQLDba.Maint.ShowHistoryErrors @JobNo = 1227

<Exec>

 

DATE/TIME:    2018/7/30
12:10:52

<row>

<ctx>yMaint.backups</ctx>

1:命令格局(sqlservr.exe)运营

 

Server实例计算。<ctx>yMaint.IntegrityTesting</ctx>

<Sql>

 

DESCRIPTION:
BackupDiskFile::CreateMedia: Backup device
‘M:\DB_BACKUP\LOG_BACKUP\xxxx_[2018-07-30_06h03m10_Mon]_logs.TRN’
failed to create. Operating system error 32(failed to retrieve text for
this error. Reason: 15105).

<Sql>DBCC checkDb(‘xxxx’) </Sql>

backup log [gewem]

第一在命令窗口中切换来Binn目录(那个要视SQL
Server实际安装路线情状而定,别的,多实例情状下,必需切换成相应路线),若是您对sqlservr.exe命令不熟习,能够查阅相关帮衬新闻。如下所示:

 

<err>In case of non-completion of this command check SQLServer
Error Log at 2016-11-17 00:00:03.327 for Spid 67</err>

to disk =
‘M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN’

 

 

</row>

with noInit, checksum, name = ‘YourSQLDba:16h16:
M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN’

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.exe /?

usage: sqlservr

        [-a<L2 buffer pool directory>,<size in GB>]       (adding an L2 buffer pool file)

        [-c] (not as a service)

        [-d file] (alternative master data file)

        [-l file] (alternative master log file)

        [-e file] (alternate errorlog file)

        [-f] (minimal configuration mode)

        [-m] (single user admin mode)

        [-g number] (stack MB to reserve)

        [-k <decimal number>] (checkpoint speed in MB/sec)

        [-n] (do not use event logging)

        [-s name] (alternate registry key name)

        [-T <number>] (trace flag turned on at startup)

        [-x] (no statistics tracking)

        [-y number] (stack dump on this error)

        [-B] (breakpoint on error (used with -y))

        [-K] (force regeneration of service master key (if exists))

        [-v] (list version information)

 

See documentation for details.

2018-04-06 11:28:00.52             SQL Server shutdown has been initiated

COMMENT:  (None)

 

</Sql>

 

 

错误日志里面有上边一些错误音讯,如下所示:

<err>Error 3202, Severity 16, level 2 : Write on
“M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN”
failed: 112(error not found)

 

JOB
RUN: 
(None)

 

Error 3013, Severity 16, level 1 : BACKUP LOG is terminating abnormally.

sqlservr.ex运维时,当前情形有多实例,而你有未有内定参数-s,那么就能够唤醒类似如下消息,
须求你内定-s参数的SQL Server服务名称。

 

Date 2016/11/17 0:53:21

</err>

 

 

Log SQL Server (Archive #8 – 2016/11/18
0:00:00)

</Exec>

 

      关于Operating
system error 32,这一个错误代码对应的错误音信为:

 

 

C:\Program
Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.ex

 

Source spid67

本条“failed
:112”新闻一出现,意味着一定是磁盘空间相当不足,前天总结的那篇博客DBCC
CHECKDB 遭遇Operating system error 112(failed to retrieve text for this
error. Reason: 15105)
encountered实际蒙受的主题材料也是相仿的,Operating
System error 112 meaning There is not enough space on the disk.

e
-c -m

     The
process cannot access the file because it is being used by another
process.

 

 

2018-04-06
11:40:54.15 Server      Multiple
instances of SQL server are installe

 

Message

997755.com澳门葡京 1

d
on this computer. Renter the command, specifying the -s parameter with
the nam

 

DBCC CHECKDB (xxxx) executed by xxxxx found 0
errors and repaired 0 errors. Elapsed time: 0 hours 53 minutes 18
seconds. Internal database snapshot has split point LSN =
00623e60:00004a5b:0001 and first LSN = 00623e60:000048f3:0011. This is
an informational message only. No user action is required.

 

e
of the instance that you want to start.

   其实从前刚好蒙受过这么的案例,使用AplexSQL
Log去解析事情日志的备份文件,YourSQLDba的业务日志备份的时候,就能够遇上上边错误(YourSQLDba_LogBackups事务日志备份会追加到同一个备份文件),但是那么些怎么定位那些荒唐呢?其实出现那么些破绽百出,一定有连带进度在拜会专门的学业日志备份文件,那么只要排查这一个使用或程序就可以。刚好前段时间配备了AWS的DMS的(DMS简介如下,具体参照他事他说加以考察官方文书档案

 

即时检讨时,发掘磁盘空间还剩余十多G,然后自个儿手工业又将代码收取,手工业实践测量试验,依旧报那么些荒唐:

2018-04-06
11:40:54.16 Server      SQL Server
shutdown has been initiated

 

Date 2016/11/17 0:53:21

backup log [xxxx] 

to disk = 'M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN' 

with noInit, checksum, 

NAME = 'YourSQLDba:00h10: M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN'

 

 

Log SQL Server (Archive #8 – 2016/11/18
0:00:00)

997755.com澳门葡京 2

sqlservr.exe
-c -m 
-s{instancename}

在最宗旨等级上,AWS DMS 是 AWS
云中运维复制软件的服务器。您创设源和指标连接以报告 AWS DMS
要开展领取和加载的岗位。然后,安插在此服务器上运维以动员搬迁数据的任务。AWS DMS
会创设表和涉嫌的主键 (即便它们在对象上不设有)。假若您愿意,则能够事先手动创造指标表。只怕,也足以选拔 AWS SCT
成立部分或任何目的表、索引、视图、触发器等。

 

 

 

 

Source spid13s

前边防检查查开采那一个数据库的日记文件暴增,已经超(英文名:jīng chāo)过数据文件的大小。如下截图所示,所以固然磁盘空间还会有众多(19G),陈设的磁盘空间告警作业亦如常,未有出现告警,不过已经不可能丰裕补助事务日志备份完成。所以出现了那几个难点,难题不怎么掩饰,但是即便稳重,难点本质就能够水落石出。剔除多少个老旧备份以及已经上带的备份,腾出了一部分磁盘空间,重国民党的新生活运动行作业,作业实行成功!

 

 

 

 

 

997755.com澳门葡京 3

Message

997755.com澳门葡京 4

sqlservr.ex运维时,借使SQL
Server服务自己还在运行,就能够报“Operating
system error = 32(The process cannot access the file because it is being
used by another process.).

 

Write to sparse file
‘xxxxxxx.ndf:MSSQL_DBCC10’ failed due to lack of disk space.

 

 

 

 

因为DMS有时候会读取事务日志备份文件中的内容,通过DPA监察和控制,刚好找的了那么些DMS的账号,在事情日志备份的岁月点在进行上面SQL语句(如下所示)。那么就是因为那几个进度在读取事务日志备份中内容(跟平日的Replication有一些分歧),导致专门的学问日志备份出现Operating
system error 32荒谬(The
process cannot access the file because it is being used by another
process)

 

C:\Program
Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.ex

 

SQL Server

e
-c -m -sMSSQLSERVER

 

 SELECT /* top 50000 */

        [Current LSN] ,

        [Operation] ,

        [Context] ,

        [Transaction ID] ,

        [Transaction Name] ,

        [Begin Time] ,

        [End Time] ,

        [Flag Bits] ,

        [PartitionId] ,

        [Page ID] ,

        [Slot ID] ,

        [RowLog Contents 0] ,

        [Log Record] ,

        [RowLog Contents 1]

 FROM   sys.fn_dump_dblog(@P1, NULL, NULL, 11,

                          'M:\DB_BACKUP\LOG_BACKUP\xxxx_[2018-08-01_06h06m19_Wed]_logs.TRN',

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

 WHERE  [Current LSN] COLLATE SQL_Latin1_General_CP1_CI_AS >= @P2 COLLATE SQL_Latin1_General_CP1_CI_AS

        AND ( ( [Operation] IN ( 'LOP_COMMIT_XACT', 'LOP_ABORT_XACT' )

                OR ( [Operation] = 'LOP_BEGIN_XACT' /* and [Transaction SID]=SUSER_SID('') */ )

              )

              OR ( ( ( [Operation] IN ( 'LOP_INSERT_ROWS', 'LOP_DELETE_ROWS' )

                       AND [Context] IN ( 'LCX_HEAP', 'LCX_CLUSTERED',

                                          'LCX_MARK_AS_GHOST' )

                     )

                     OR ( [Operation] IN ( 'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW' )

                          AND [Context] = 'LCX_TEXT_MIX'

                          AND DATALENGTH([RowLog Contents 0]) IN ( 0, 14, 28 )

                        )

                   )

                   AND [PartitionId] IN ( 72057596166537216, 72057596166471680,

                                          72057596166406144, 72057596327559168,

                                          72057596160180224, 72057596361506816,

                                          72057596119613440, 72057596119744512,

                                          72057596119678976, 72057596120006656,

                                          72057596119941120, 72057596120137728,

                                          72057596545859584, 72057596119810048,

                                          72057596231417856, 72057596418523136,

                                          72057596441067520, 72057596381364224,

                                          72057594562543616, 72057596104671232,

                                          72057594585808896, 72057594585874432,

                                          72057596242952192, 72057596168962048,

                                          72057596054994944, 72057596055453696,

                                          72057596035072000, 72057596055126016,

                                          72057596091039744, 72057596381495296,

                                          72057596381560832, 72057596524298240,

                                          72057595958067200, 72057596469116928,

                                          72057596462628864, 72057596454699008,

                                          72057596241641472, 72057595958853632,

                                          72057594563526656, 72057594563657728,

                                          72057594563723264, 72057596502802432,

                                          72057596484845568, 72057596484911104,

                                          72057596575285248, 72057596554575872,

                                          72057596485107712, 72057596485238784,

                                          72057596485304320, 72057596485369856,

                                          72057596551430144, 72057596485500928,

                                          72057594601275392, 72057594621394944,

                                          72057596548677632, 72057596561719296,

                                          72057596218638336, 72057596376842240,

                                          72057596357443584, 72057596357509120,

                                          72057596358164480, 72057596112207872,

                                          72057596023865344, 72057595959902208,

                                          72057595961081856, 72057594588889088,

                                          72057596112142336, 72057596410003456,

                                          72057595938275328, 72057596192096256,

                                          72057596192161792, 72057596367208448,

                                          72057595961868288, 72057596245508096,

                                          72057596250554368, 72057594565689344,

                                          72057596192227328, 72057596192292864,

                                          72057596248326144, 72057596248391680,

                                          72057594565754880, 2465937948672,

                                          72057596374810624, 72057594565885952,

                                          72057596133113856, 72057596563357696,

                                          48281791823872, 116258596192256,

                                          72057596132851712, 72057595963179008,

                                          72057596541861888, 72057594599047168,

                                          53296886710272, 72057595944894464,

                                          72057594566934528, 72057594567000064,

                                          72057596526395392, 72057596526592000,

                                          72057595964030976, 72057595965079552,

                                          72057594582859776, 72057594567917568,

                                          72057596525936640, 72057595908128768,

                                          72057594568048640, 119223456956416,

                                          72057594896711680, 72057594588758016,

                                          72057596133179392, 72057596132786176,

                                          72057595965931520, 72057595966455808,

                                          72057596450177024, 72057595966849024,

                                          112563398705152, 72057596184363008,

                                          72057596184428544, 72057594681425920,

                                          72057596309733376, 72057596205334528,

                                          72057596184231936, 72057595458355200,

                                          107370836852736, 72057595852423168,

                                          72057595708702720, 72057596357312512,

                                          72057596357378048, 72057596550119424,

                                          72057596301279232, 72057596357050368,

                                          72057596356984832, 72057596362293248,

                                          72057596357574656, 72057596357246976,

                                          72057596357181440, 72057596301344768,

                                          72057595809890304, 72057595873591296,

                                          72057595969011712, 72057595969536000,

                                          72057596348858368, 72057595970191360,

                                          72057596217917440, 72057596217982976,

                                          72057596526329856, 72057596332212224,

                                          114660558176256, 72057596526460928,

                                          72057596331687936, 72057596331622400,

                                          72057595970781184, 72057596574826496,

                                          72057596317859840, 72057594572242944,

                                          72057594592952320, 72057595971502080,

                                          72057595991228416, 72057596324282368,

                                          72057596220538880, 72057595972943872,

                                          72057596512370688, 72057594640334848,

                                          72057595973730304, 72057596132655104,

                                          72057594573684736, 72057594574012416,

                                          72057594776649728, 72057595974320128,

                                          72057594574798848, 45591660199936,

                                          72057594590920704, 72057595974778880,

                                          72057594593738752, 72057595975106560,

                                          72057595975696384, 72057595937882112,

                                          140014173224960, 72057594576240640,

                                          72057594576502784, 72057596501819392,

                                          72057596188360704, 72057596526264320,

                                          61775963815936, 56442625916928,

                                          72057596350365696, 14271632703488,

                                          72057596268838912, 72057596575744000,

                                          72057596442836992, 72057596459089920,

                                          72057596440281088, 72057596469444608,

                                          72057596413935616, 72057596414001152,

                                          72057596532293632, 72057596413739008,

                                          72057596414066688, 72057596548743168,

                                          72057596548612096, 72057596195110912,

                                          72057596448014336, 72057594576961536,

                                          72057594581614592, 72057594577354752,

                                          72057596310061056, 72057596488384512,

                                          72057594577813504, 72057596309995520,

                                          72057596181151744, 72057594577551360 )

                 )

              OR ( [Operation] = 'LOP_HOBT_DDL' )

            ); 

Date 2016/11/17 0:53:21

2018-04-06
11:41:59.01 Server      Error: 17058,
Severity: 16, State: 1.

Log SQL Server (Archive #8 – 2016/11/18
0:00:00)

2018-04-06
11:41:59.01 Server      initerrlog:
Could not open error log file ‘C:

 

\Program
Files\997755.com澳门葡京 ,Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG’.
Ope

Source spid129

rating
system error = 32(The process cannot access the file because it is
being

 

used
by another process.).

Message

2018-04-06
11:41:59.32 Server      Error: 17058,
Severity: 16, State: 1.

D:\xxx\xxxx\xxxxx.ndf:MSSQL_DBCC10:
Operating system error 112(failed to retrieve text for this error.
Reason: 15105) encountered.

2018-04-06
11:41:59.32 Server      initerrlog:
Could not open error log file ‘C:

997755.com澳门葡京 5

\Program
Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG’.
Ope

 

rating
system error = 32(The process cannot access the file because it is
being

除此以外,我们的磁盘空间告警作业也吸取了大批量的磁盘空间告警邮件:

used
by another process.).

 

2018-04-06
11:42:02.04 Server      SQL Server
shutdown has been initiated

997755.com澳门葡京 6

 

 

 

实际在芸芸众生的时候,该服务器就曾经有磁盘空间告警邮件发出,由于系统管理员想在周日做存款和储蓄扩容。所以在晚间数据库做DBCC
CHECKDB时,就由于磁盘空间不足,蒙受了那个荒唐。DBCC
CHECKDB时会生成数据库快速照相,而数据库的快速照相文件是基于荒废文件(Sparse
File),而当快速照相创设后,随着对源数据库的更换渐渐增多,疏落文件也会慢慢滋长。所以,当磁盘空间不足时,就能遇上下边错误消息:

 

 

要是在sqlservr.exe当中退出单客户方式,直接使用CT昂科威L+C 或 CTQX56L +
Break,如下所示:

1:Write to sparse file ‘xxxxxxx.ndf:MSSQL_DBCC10’ failed due to lack
of disk

 

2:SSQL_DBCC10: Operating system error 112(failed to retrieve text for
this error. Reason: 15105) encountered

997755.com澳门葡京 7

 

 

那儿出于磁盘空间不足,就能够产生DBCC CHECKDB结束。关于DBCC
CHECKDB耗用多量非常磁盘空间,能够参见下边那篇博客。

 

CHECKDB From Every Angle: Why would CHECKDB run out of
space?

 

 

 

参照他事他说加以考察资料:

2:命令情势(net star)运维

 

 

C:\Users>net
stop mssqlserver

The
following services are dependent on the SQL Server (MSSQLSERVER)
service.

Stopping
the SQL Server (MSSQLSERVER) service will also stop these
services.

 

   SQL Server Agent
(MSSQLSERVER)

 

Do
you want to continue this operation? (Y/N) [N]: y

The
SQL Server Agent (MSSQLSERVER) service is stopping.

The
SQL Server Agent (MSSQLSERVER) service was stopped successfully.

 

The
SQL Server (MSSQLSERVER) service is stopping.

The
SQL Server (MSSQLSERVER) service was stopped successfully.

 

 

C:\Program
Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>net
start mssqlserver /m

The
SQL Server (MSSQLSERVER) service is starting.

The
SQL Server (MSSQLSERVER) service was started successfully.

 

997755.com澳门葡京 8

 

 

3:SQL
Server配置管理器运维

 

 

在SQL
Server配置管理器中,找到呼应实例,右键单击属性,在开发银行参数里面扩展参数-m,然后重启就能够。

 

997755.com澳门葡京 9

 

 

在单客商情势下运行SQL
Server实例时,请留意下列事项:

 

 

·        
独有叁个客商能够接连到服务器。

 

·        
不施行CHECKPOINT
进程。 默许处境下,运行时自动试行此进度。

 

 

                                                                                                                                                                                                                
在单客户形式下运转 SQL Server
可使计算机本地Administrators 组的其余成员作为 sysadmin
固定服务器剧中人物的积极分子连接到 SQL Server 实例。 有关详细音信,请参阅在系统管理员被锁定期连接受 SQL
Server。

 

 

在单客户形式下,
独有八个客商能够连续到服务器,那么这么难点就来了,很有相当的大希望当您须求报到的时候,那些独一的的顾客已经被其余客户捷足首先登场了。此时您却被拒绝在门外,是否分外抓狂。此时你恐怕境遇下边错误

 

 

 

C:\Users>sqlcmd

Sqlcmd:
Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user

xxxx’.
Reason: Server is in single user mode. Only one administrator c

an
connect at this time..

 

SSMS客商端一般遭遇上面那样的错误消息:

 

 

Login
failed for user ‘xxxx’. Reason: Server is in single user mode. Only one
administrator can connect at this time. (Microsoft SQL Server, Error:
18461)

 

 

谬误日志或指令里面输出的日志,你会看出类似如下音信:

 

 

2018-04-06
12:21:14.85 Logon       Error: 18461,
Severity: 14, State: 1.

2018-04-06
12:21:14.85 Logon       Login failed
for user ‘xxx’. Reason: Server is in single user mode. Only one
administrator can connect at this time. [CLIENT:
192.168.xxx.xxx]

 

 

在这种气象下,如何做呢?
难道要拼速度? 当然不是,你须要从下面那些方面注意:

 

 

在单客商情势下连接到SQL
Server实例从前,甘休SQL Server Agent 服务;不然 SQL Server Agent
服务将动用该连接,进而使其阻塞。

 

 

在单顾客格局下运行SQL Server实例时,SQL Server Management Studio
能够接连不断到 SQL
Server。 不过Management
Studio中的对象能源管理器大概会失败,因为在少数操作中它必要采纳三个三番五次。 若要在单顾客形式下管理 SQL
Server,能够实践Transact-SQL 语句(仅通过 Management Studio
中的查询编辑器连接)只怕选取 sqlcmd
实用工具。

 

当你将 -m 选项与 sqlcmd 或 Management Studio
结合使用时,能够将连接限制为钦赐的顾客端应用程序。 举个例子,-m”sqlcmd” 将连接限制为单个连接而且该连接必得将作者标志为 sqlcmd 客商端程序。 当您正在单客商情势下运行 SQL Server
何况未知的顾客端应用程序正在占用这么些独一的可用连接时,使用此选项。 若要经过 Management
Studio 中的查询编辑器举行连接,请使用 -m”Microsoft
SQL Server Management Studio – Query”。

 

 

一般来讲所示,若是你钦赐了单顾客只好以SQLCMD连接,那么此时,另外通过SSMS等别的方法连接数据库都会报上边错误,另外通进度序连接过来的连年就不会抢占那么些延续了。

 

C:\Windows\system32>net
stop mssqlserver

The
SQL Server (MSSQLSERVER) service is stopping.

The
SQL Server (MSSQLSERVER) service was stopped successfully.

 

 

C:\Windows\system32>net
start mssqlserver /m”SQLCMD”

The
SQL Server (MSSQLSERVER) service is starting.

The
SQL Server (MSSQLSERVER) service was started successfully.

 

 

C:\Windows\system32>

 

997755.com澳门葡京 10

 

 

若是你内定参数/m”Microsoft SQL Server
Management Studio – Query” 那么就能够阻止像应用程序或SQLCMD登入,如下所:

 

 

C:\Windows\system32>net
stop mssqlserver

The
SQL Server (MSSQLSERVER) service is stopping.

The
SQL Server (MSSQLSERVER) service was stopped successfully.

 

 

C:\Windows\system32>net
start mssqlserver /m”Microsoft SQL Server Management Studio –
Query”

The
SQL Server (MSSQLSERVER) service is starting.

The
SQL Server (MSSQLSERVER) service was started successfully.

 

 

 

C:\Users>sqlcmd

Sqlcmd:
Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user

xxx’.
Reason: Server is in single user mode. Only one administrator c

an
connect at this time..

 

997755.com澳门葡京 11

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website