Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Dec 17 '13 at 2:44

This question came from our site for professional system and network administrators.

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 '13 at 17:02
    
That's what I figured, just wanted to be sure I was thinking about this the right way. –  red888 Dec 16 '13 at 17:12

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
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 '13 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 '13 at 19:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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