Solved

Setting a SQL database size limit

Posted on 2010-11-23
5
692 Views
Last Modified: 2012-05-10
I am trying to set a database SIZE limit for a SQL database on my SQL 2005 Standard server.  I want a specific database (a SYSLOG database) to limit itself to (1) GB.  I have set the maximum database size for this database in the database properties in SQL Enterprise Manager and indeed, the database is NOT growing larger than 1GB.

However, I do see the APPLICATION LOG of the SQL server filling up with THOUSANDS of messages indicating that the database is full and unable to be written to.

What I WANT to happen is for 1GB of data to be retained, with NEWER data pushing OLDER data out.  I thought this is what would be happening when I set a database size limit, but looking at the APPLICATION log, it does not seem that this is the case.  Can anyone tell me if I misunderstood what LIMIT DATABASE SIZE meant, and if this is NOT what I've configured, can it be done?
0
Comment
Question by:jkeegan123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34203092
Hi,

What you need here is an archival process.

What you're trying to do cannot be done as part of a standard sql configuration.
Setting the size limit of a database will not truncate out old data and push new data in, so the messages you are getting are correct.

You will need some type of script to push out data to an archived table or database. Probably based by date rather than size I'd image.
So maybe every day you could have a job to say copy rows from your table which are older than 30 days for example. Once the rows are copied, you can delete them from your original table.
0
 
LVL 5

Author Comment

by:jkeegan123
ID: 34206137
I opened up the one table in my database (SYSLOG table) and looked at the actual DATA in the database, and it really truly IS only recent data, with older data being pushed out, it seems.  It's hard to EXACTLY tell because it's 1GB of SYSLOG data (text) from a firewall, so the Query seemed to go on FOREVER.  Any idea on the best way to query this data to see if its actually doing this?  I'm thinking a query that would be:  The fist 20 rows and the last 20 rows of data displayed from that table.  How could I show that?  Looking at this report several times would be a good indicator as to whether what I INTENDED to happen is ACTUALLY happening.

If it IS happening, can I suppress that event from logging?  I don't mean filtering it out, I mean setting SQL server to not log that event?
0
 
LVL 10

Accepted Solution

by:
Humpdy earned 500 total points
ID: 34206190
Is there date column on the table, or an auto increment int.
you can do
select top 20 * from table order by column
select top 20 * from table order by column desc
0
 
LVL 5

Author Comment

by:jkeegan123
ID: 34258079
I can't seem to get the results to work on this query, how would I select the database in the query given that it's configured as :  SQL SERVER --> master,model,etc...MY DATABASE --> MY DATABASE --> TABLE (syslog) --> Data that's taking up 1 GB
0
 
LVL 5

Author Closing Comment

by:jkeegan123
ID: 34294835
Comment was accurate but answer to question was incorrect, as we do NOT need an archival process.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dual boot with Windows 7 on both partition 11 53
debug user defined function in visual studio 2015 2 42
grouping by date only 6 22
ms sql + help with query 2 43
When you try to share a printer , you may receive one of the following error messages. Error message when you use the Add Printer Wizard to share a printer: Windows could not share your printer. Operation could not be completed (Error 0x000006…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
This Micro Tutorial will teach you how to change your appearance and customize your Windows 7 interface to your unique preference. This will be demonstrated using Windows 7 operating system.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question