c# - 创建具有定义文件大小的 SQL Server 数据库

Question

我想创建一个定义文件大小为 512 KB 的 .mdf SQL Server 数据库文件:

using System.Data.SqlClient;

SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;Database=master");

if (connection.State == ConnectionState.Closed) 
     connection.Open();

var command = connection.CreateCommand();
command.CommandText = "CREATE DATABASE thedb ON PRIMARY (NAME='thedb', FILENAME='r:\thedb.mdf', SIZE = 512KB, " + 
     "MAXSIZE = UNLIMITED, FILEGROWTH = 500KB) LOG ON (NAME ='thedb_log', FILENAME = 'r:\thedb_log.ldf', SIZE = 512KB)";

command.ExecuteNonQuery();

这是创建一个大小为 8 MB 的 .mdf 文件和一个大小为 64.5 MB (!) 的 .ldf 文件...

如果我省略 LOG ON，那么 .ldf 文件的大小也是 8 MB。

Answer 1

来自MSDN reference :

When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. The default size of model is 8 MB (beginning with SQL Server 2016 (13.x)) or 1 MB (for earlier versions). When a secondary data file or log file is specified, but size is not specified for the file, the Database Engine makes the file 8 MB (beginning with SQL Server 2016 (13.x)) or 1 MB (for earlier versions). The size specified for the primary file must be at least as large as the primary file of the model database.

检查模型数据库 mdf 文件的大小。您的新数据库不能小于该值。如果初始文件大小的数字太小，看起来默认为 8MB。您可以减小模型数据库的大小，使数据库小于 8MB。

您创建的文件可能太小以至于 SQL Server 无法正常运行。如果您正在寻找小型单文件数据库，您可以查看 SQLLite .