SQL Server 2012 Express – Limit Database Size and Retention Policy

sql serversql-server-2012

I don't work with databases very often and I want to make sure I am going about this the right way.

I want to configure a database's file size to not exceed 9 gigs. At the point the database reaches this file size limit I would like it to purge 25% percent of its oldest records to accommodate new records.

Is this something I could/should configure on the database server itself, like a one time maximum setting config, or does this have to be handled by the program that is updating the database (it would check the capacity and tell it when to purge records).

I'm not a DBA so I'm unsure of how this is usually handled.

Best Answer

You can set the database's max file size to 9GB, but you will need to do the purging yourself. This is not something that SQL Server supports. You could get notified of the limit being reached using extended events, but there is no way for SQL Server to know what records to delete without you telling it. SQL Server does not log or otherwise keep track of how old a row in a table is. You have to build it into the table schema and maintain your own aging logic.

To set the file size in SQL Server Management Studio:

  • Right-click the database and get properties
  • Click the Files link in the menu on the left
  • Click the ... button under Autogrowth / maxsize and set the properties accordingly

T-SQL to set the file size:

ALTER DATABASE MyDb
MODIFY FILE
(
    NAME=N'MyDataFile',
    MAXSIZE=9000MB
)

Note: MyDataFile is the logical name of your data file, not the physical file name on the filesystem. You can find the logical file names of your database files with the sys.database_files system view:

USE MyDb
SELECT name
FROM sys.database_files
WHERE type_desc = 'ROWS'

The value in the name column is the logical name of the file.

Related Question