CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005
Harvey Kwok
Harvey Kwok

Reputation: 11883

Max database name length in SQL Server

I am trying to do some UI validation on a database name. I was told from different places that the SQL Server database name is stored as sysname inside SQL Server. I also verified that by checking the sys.Databases.

So, I make my textbox in the UI to have maxlength 128 characters long. This is the max length of sysname type.

However, I found that in my SQL Server 2005 Express edition, if I enter a database name with 128 characters long, it always complains

Could not create default log file because the name was too long

To make sure I can successfully create the database, I found that I need to set the max limit to 124 characters.

My question now is whether 124 characters limit is true for all versions of SQL Server on all different Windows OS?

Upvotes: 18

Views: 34694

Answers (2)

Anders Zommarin
Anders Zommarin

Reputation: 7274

Are you not running into the problem of the file names for the data and log files. The underlying operating system (Windows) places restrictions on it and therefore the database cannot be created. The database name may be 128 characters long and works given that the file names used are not being to long (when using the SQL Server Management Studio it automatically generates file names based on the database names, and they therefore end up being long).

Upvotes: 6

Martin Smith
Martin Smith

Reputation: 453920

The log file name does not need to include the database name.

This works fine for me for example.

CREATE DATABASE [AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA] ON  PRIMARY 
( 
NAME = N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\A128.mdf')
 LOG ON 
( 
NAME = N'A128_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\A128_log.LDF')

Upvotes: 16

Related Questions