1

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.

Improve this question
2
  • 1
    I'm fairly certain this is not something that you can just get from the settings inside SQL Management Studio. This will require a SQL job or stored procedure to accomplish.
    – DanBig
    Dec 16, 2013 at 17:02
  • That's what I figured, just wanted to be sure I was thinking about this the right way.
    – red888
    Dec 16, 2013 at 17:12

1 Answer 1

Reset to default
5

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.

Improve this answer
2
  • Thanks for clarifying that for me. I already have a few columns I could use to figure out what rows to purge. You mention using extended events, but is it also acceptable for the program to just check the DB file size with T-SQL and proceed accordingly? Is it even possible to check the DB's size on disc with T-SQL?
    – red888
    Dec 17, 2013 at 1:20
  • 1
    @red888 Yes, it is absolutely acceptable for your program to just check the Db size. It's up to you how you want to manage it. Manual purges are even sometimes preferable to automated purges. You can use sp_spaceused and also sp_helpdb to get he Db size via T-SQL.
    – squillman
    Dec 17, 2013 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.