主页 / user-241004

AAA's questions

Martin Hope
AAA
Asked: 2021-12-22 01:54:03 +0800 CST
  • 0

我使用的是sql server 2019，我根据微软的例子创建了全文索引，但是有一个问题，在文本中搜索部分单词时，没有结果

sql服务器版本：

Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64) 
    Sep 23 2021 16:47:49 
    Copyright (C) 2019 Microsoft Corporation
    Enterprise Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

例如 ：

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));  
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);  
INSERT Flags VALUES ('France', 'Blue and White and Red');  
INSERT Flags VALUES ('Italy', 'Green and White and Red');  
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');  
SELECT * FROM Flags;  
GO  
  
CREATE FULLTEXT CATALOG TestFTCat;  
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;  
GO

现在我搜索这个词blu，但尽管这个词没有结果blue（我需要一个模糊搜索）

像这样的查询：

SELECT * FROM FREETEXTTABLE (Flags, FlagColors, 'Blu');

是什么原因？

sql-server full-text-search
Martin Hope
AAA
Asked: 2021-11-09 07:05:59 +0800 CST
  • 0

我使用 sql server 2019 并在我的数据库中启用了跟踪标志 1117（平等地增长文件组中的所有文件）。我需要根据我的系统资源考虑主文件组的正确数据文件数量。为此，我需要使用性能监控软件检查此操作。但我不知道应该使用哪些计数器（例如每秒写入磁盘）

第一次测试：

CREATE DATABASE TestIO
ON PRIMARY 
    ( NAME = N'PRIMARY1',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY1.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB),
 FILEGROUP FG2 
    ( NAME = N'secondary',          FILENAME = N'D:\DB\Temp\TestIO_secondary.ndf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB)
 LOG ON 
    ( NAME = N'TestIO_log',     FILENAME = N'D:\DB\Temp\TEST_log.ldf' ,FILEGROWTH=2GB,MAXSIZE=2TB,SIZE=2GB)
GO

第二次测试：

USE master
GO
DROP DATABASE IF EXISTS TestIO
CREATE DATABASE TestIO
ON PRIMARY 
    ( NAME = N'PRIMARY1',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY1.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB),
    ( NAME = N'PRIMARY2',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY2.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
    ( NAME = N'PRIMARY3',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY3.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
    ( NAME = N'PRIMARY4',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY4.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
 FILEGROUP FG2 
    ( NAME = N'secondary',          FILENAME = N'D:\DB\Temp\TestIO_secondary.ndf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB)
 LOG ON 
    ( NAME = N'TestIO_log',     FILENAME = N'D:\DB\Temp\TEST_log.ldf' ,FILEGROWTH=2GB,MAXSIZE=2TB,SIZE=2GB)
GO
sql-server-2019 performance-tuning
Martin Hope
AAA
Asked: 2021-10-25 06:54:55 +0800 CST
  • 0

我使用 Sql Server 2019 并且需要备份和恢复文件组。

执行步骤：
1.创建数据库Two FilesGroup并[TestFileGroup]命名。
2.在两个表中插入数据，一个在 First FileGroup 中，另一个在第二个 FileGroup 中。
3.第一次完整备份[TestFileGroup]。
4.然后re-enter the data进行更改 5.BackUP FileGroup From [FG2] 。
6.Restore First FullBackup With [TestFileGroup_New]name。
7.对于恢复部分备份需要尾部备份。和来自 [TestFileGroup_New] 的尾日志备份。
8.恢复FILEGROUP备份。
9.restore Database [TestFileGroup_New] with RECOVERY
10.引发错误！

错误文本：

消息 4303，级别 16，状态 1，第 100 行 前滚起点现在位于日志序列号 (LSN) 37000000056800001。需要额外前滚超过 LSN 37000000058400001 才能完成恢复序列。消息 3013，级别 16，状态 1，第 100 行 RESTORE DATABASE 异常终止。

Sql 服务器查询：

USE master
GO
DROP DATABASE IF EXISTS TestFileGroup
GO 
DROP DATABASE IF EXISTS TestFileGroup_New
GO 
--================================================================= Create DataBase
CREATE DATABASE TestFileGroup
ON PRIMARY 
    ( NAME = N'PRIMARY',            FILENAME = N'D:\Temp\TestDB\TestFileGroup_PRIMARY.mdf'), 
 FILEGROUP FG2 
    ( NAME = N'secondary',          FILENAME = N'D:\Temp\TestDB\TestFileGroup_secondary.ndf')
 LOG ON 
    ( NAME = N'TestFileGroup_log',  FILENAME = N'D:\Temp\TestDB\FGRestoreTEST_log.ldf')
GO
--================================================================= Insert Data(Level 1)
USE TestFileGroup
GO 
CREATE TABLE T_primary
(
    ID INT PRIMARY KEY,
    Fname NVARCHAR(100),
    Lname NVARCHAR(100)
) ON [PRIMARY]
GO
CREATE TABLE T_fg2
(
    ID INT PRIMARY KEY,
    Fname NVARCHAR(100),
    Lname NVARCHAR(100)
) ON FG2
GO
--==================================================================
USE TestFileGroup
GO 
INSERT INTO T_primary (ID,Fname,Lname) VALUES 
    (1,N'Json',N'JJ'),
    (2,N'C#',N'CC'),
    (3,N'Python',N'PP'),
    (4,N'Test1',N'TT'),
    (5,N'C++',N'C')
GO
INSERT INTO T_fg2 (ID,Fname,Lname) VALUES 
    (10,N'MMMM',N'M'),
    (20,N'SSSS',N'S'),
    (30,N'PPPPP',N'P')
GO
SELECT * FROM T_primary
SELECT * FROM T_fg2
GO
--================================================================== First Full Backup IN First year 
BACKUP DATABASE TestFileGroup  TO  DISK = N'D:\Temp\TestDB\Backup\TestFileGroup_Full.bak' 
    WITH FORMAT,COMPRESSION
GO 
RESTORE FILELISTONLY FROM DISK = N'D:\Temp\TestDB\Backup\TestFileGroup_Full.bak' 

--================================================================== Change Data
USE TestFileGroup
GO 
INSERT T_primary 
SELECT 6 ID,'Java'Fname,'jv' Lname

INSERT T_fg2 
SELECT 40 ID,'HHHH'Fname,'H' Lname

SELECT * FROM T_primary
SELECT * FROM T_fg2
--================================================================== First FileGroup Backup
USE master
GO
BACKUP DATABASE TestFileGroup FILEGROUP = N'FG2' TO  DISK = N'D:\Temp\TestDB\Backup\TestFileGroup_FG2.bak' 
    WITH FORMAT,COMPRESSION
GO
RESTORE LABELONLY FROM DISK=N'D:\Temp\TestDB\Backup\TestFileGroup_FG2.bak' 
GO
RESTORE HEADERONLY FROM DISK=N'D:\Temp\TestDB\Backup\TestFileGroup_FG2.bak' 
GO
RESTORE FILELISTONLY FROM DISK=N'D:\Temp\TestDB\Backup\TestFileGroup_FG2.bak' 
GO


--================================================================== Restore Full Backup --->TestFileGroup_New
RESTORE DATABASE TestFileGroup_New FROM DISK=N'D:\Temp\TestDB\Backup\TestFileGroup_Full.bak' WITH
MOVE 'PRIMARY'              TO N'D:\Temp\TestDB\New\TestFileGroupNew_PRIMARY.mdf',
MOVE 'secondary'            TO N'D:\Temp\TestDB\New\TestFileGroupNew_secondary.ndf',
MOVE 'TestFileGroup_log'    TO N'D:\Temp\TestDB\New\TestFileGroupNew_log.ldf',
STATS=10 ,RECOVERY

--================================================================== For Restore Partial Backup Need Tail Backup
BACKUP LOG TestFileGroup_New 
    to  DISK = N'D:\Temp\TestDB\Backup\TestFileGroup_New_Tail.trn' ---- Not Real
WITH NORECOVERY,FORMAT,COMPRESSION
GO
--================================================================== Restore FileGroup Backup
RESTORE DATABASE TestFileGroup_New FILEGROUP = N'FG2' 
    FROM  DISK = N'D:\Temp\TestDB\BackUP\TestFileGroup_FG2.bak' WITH 
NORECOVERY,REPLACE
GO 
--================================================================== 
RESTORE DATABASE TestFileGroup_New  WITH RECOVERY
/*
Msg 4303, Level 16, State 1, Line 100
The roll forward start point is now at log sequence number (LSN) 37000000056800001. Additional roll forward past LSN 37000000058400001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 100
RESTORE DATABASE is terminating abnormally.
*/
sql-server restore