sql server2008一直提示starting up database

sql server2008 数据库不停重启,但日志里没报error:

2018-05-09 12:55:38.07 spid1s      Server resumed execution after being idle 1792 seconds. Reason: timer event.
2018-05-09 13:29:49.18 服务器         Server resumed execution after being idle 1133 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2018-05-09 13:29:49.24 spid51      Starting up database 'dz'.
2018-05-09 13:30:15.90 spid51      Starting up database 'dz'.
2018-05-09 13:32:16.92 spid51      Starting up database 'dz'.
2018-05-09 13:37:48.87 spid51      Starting up database 'dz'.
2018-05-09 13:41:37.23 spid51      Starting up database 'dz'.
2018-05-09 14:04:37.98 spid51      Starting up database 'dz'.
2018-05-09 14:12:25.29 spid51      Starting up database 'dz'.
2018-05-09 14:16:54.54 spid51      Starting up database 'dz'.
2018-05-09 14:18:02.79 spid51      Starting up database 'dz'.

2018-05-09 14:20:05.81 spid51      Starting up database 'dz'.

 

解决方法:右键点击数据库'dz',选项-->自动关闭的 "true"改为"false"

 

修改后问题未重现

参考文章:https://www.wanweiwang.cn/FAQ/view/599.html

2025-06-19 17:34:48.74 Server Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 26100: ) (Hypervisor) 2025-06-19 17:34:48.75 Server UTC adjustment: 8:00 2025-06-19 17:34:48.75 Server (c) Microsoft Corporation. 2025-06-19 17:34:48.75 Server All rights reserved. 2025-06-19 17:34:48.75 Server Server process ID is 14252. 2025-06-19 17:34:48.75 Server System Manufacturer: 'LENOVO', System Model: '82YX'. 2025-06-19 17:34:48.75 Server Authentication mode is MIXED. 2025-06-19 17:34:48.75 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG'. 2025-06-19 17:34:48.75 Server The service account is 'NT Service\MSSQLSERVER'. This is an informational message; no user action is required. 2025-06-19 17:34:48.75 Server Registry startup parameters: -d C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf -e C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG -l C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mastlog.ldf 2025-06-19 17:34:48.75 Server Command Line Startup Parameters: -s "MSSQLSERVER" 2025-06-19 17:34:48.75 Server SQL Server detected 1 sockets with 8 cores per socket and 16 logical processors per socket, 16 total logical processors; using 16 logical processors based on SQL Server licensing. This is an informational message; no user action is required. 2025-06-19 17:34:48.75 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 2025-06-19 17:34:48.75 Server Detected 38749 MB of RAM. This is an informational message; no user action is required. 2025-06-19 17:34:48.75 Server Using conventional memory in the memory manager. 2025-06-19 17:34:48.75 Server Page exclusion bitmap is enabled. 2025-06-19 17:34:48.87 Server Buffer Pool: Allocating 8388608 bytes for 4917325 hashPages. 2025-06-19 17:34:48.88 Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033) 2025-06-19 17:34:48.90 Server Buffer pool extension is already disabled. No action is necessary. 2025-06-19 17:34:48.93 Server Query Store settings initialized with enabled = 1, 2025-06-19 17:34:48.93 Server The maximum number of dedicated administrator connections for this instance is '1' 2025-06-19 17:34:48.93 Server This instance of SQL Server last reported using a process ID of 7392 at 2025/6/19 17:34:17 (local) 2025/6/19 9:34:17 (UTC). This is an informational message only; no user action is required. 2025-06-19 17:34:48.94 Server Node configuration: node 0: CPU mask: 0x000000000000ffff:0 Active CPU mask: 0x000000000000ffff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 2025-06-19 17:34:48.95 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required. 2025-06-19 17:34:48.95 Server Lock partitioning is enabled. This is an informational message only. No user action is required. 2025-06-19 17:34:48.95 Server In-Memory OLTP initialized on standard machine. 2025-06-19 17:34:48.96 Server [INFO] Created Extended Events session 'hkenginexesession' 2025-06-19 17:34:48.96 Server Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required. 2025-06-19 17:34:48.96 Server Total Log Writer threads: 2. This is an informational message; no user action is required. 2025-06-19 17:34:48.98 Server clwb is selected for pmem flush operation. 2025-06-19 17:34:48.98 Server Software Usage Metrics is disabled. 2025-06-19 17:34:48.98 spid9s Starting up database 'master'. 2025-06-19 17:34:48.99 spid9s There have been 256 misaligned log IOs which required falling back to synchronous IO. The current IO is on file C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf. 2025-06-19 17:34:49.07 Server CLR version v4.0.30319 loaded. 2025-06-19 17:34:49.10 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
最新发布
06-20
### SQL Server 2019 启动日志分析 在SQL Server 2019的启动过程中,日志文件和数据文件的正确加载是至关重要的。如果出现类似“操作系统错误 2(系统找不到指定的文件)”的问题,则表明SQL Server无法找到或访问所需的数据库文件[^1]。以下是对启动日志中的关键信息和潜在问题的分析: #### 数据库文件路径问题 SQL Server在尝试创建或打开日志文件`MSDBLog.ldf`时遇到了错误。这通常是因为指定的路径`d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj`不存在,或者文件本身未被正确生成。需要检查该路径是否有效,并确保SQL Server服务账户具有对该路径的读写权限。 #### 错误代码解释 操作系统错误代码`2`明确指出系统无法找到指定的文件。此问题可能由以下原因之一引起: - 文件路径配置错误。 - 数据库文件损坏或丢失。 - SQL Server服务账户缺乏对文件系统的适当权限。 #### 异步IO与同步IO问题 启动日志中提到的`misaligned log IOs synchronous IO`可能与SQL Server的日志文件I/O操作有关。当SQL Server执行日志文件的I/O操作时,要求这些操作必须对齐(即以适当的扇区大小进行)。如果未对齐,则可能导致性能下降或错误。此外,同步I/O意味着每次I/O请求都需要等待完成,这会显著降低性能。建议检查磁盘分区的对齐情况以及SQL Server配置的I/O设置。 #### 主数据库(master.mdf)相关问题 主数据库`master.mdf`是SQL Server的核心组件之一。如果`master.mdf`文件出现问题(例如损坏或路径错误),则SQL Server将无法正常启动。需要验证以下内容: - `master.mdf`文件是否存在且未损坏。 - SQL Server配置文件中指定的路径是否正确。 - 是否存在硬件故障(如磁盘损坏)影响文件访问。 ```sql -- 检查数据库状态 SELECT name, state_desc FROM sys.databases WHERE name = 'master'; ``` #### 解决方案建议 1. **验证文件路径**:确认日志中提到的所有文件路径是否有效,并修复任何路径配置错误。 2. **检查权限**:确保SQL Server服务账户具有对数据库文件所在目录的完全控制权限。 3. **修复数据库文件**:如果怀疑文件损坏,可以尝试使用`DBCC CHECKDB`命令检查和修复`master.mdf`文件。 4. **磁盘对齐检查**:验证磁盘分区是否正确对齐,避免因未对齐的I/O操作导致性能问题。 5. **重新生成日志文件**:如果日志文件确实丢失,可以尝试通过附加数据库的方式重新生成日志文件。 ```sql -- 示例:修复数据库文件 DBCC CHECKDB('master', REPAIR_ALLOW_DATA_LOSS); ```
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值