Fair Questions

[angel216]

Hi All,

I have a few questions.

Our company's SQL databases were created by programmers, then left to run themselves without maintenance.

Our databases log files have swelled to fill the database server's 33 GB hard drive. This has prompted us to investigate the need to change the settings from Unrestricted Growth to setting some limit, and to create a maintenance plan to shrink the database data and log files as needed.

1. What limits should we set on the data files and log
files? What is the rule of thumb?

2. If either set limit is reached, does the database
simply cease to add new records? Or will it do some
self purging and compressing? What is the admin
supposed to do when the limit is reached?

3. What is typically done to offload the records one
wishes to purge, say, at the end of a fiscal year? Do
you copy the MDB onto other media, then delete old
records from the database?

4. What would one do to auto-purge the oldest
transaction , log records, to keep the records from
the last, say, 60 days, in the same way that the
Windows Event Viewer is set up? (Or is that not a
good thing to do?)

Any suggestions will be greatly appreciated.
Thank in advance.

-Angel

 

[MAK9974]

No rule of thumb.

It all depends on the environment

here is our environment.

1. Usually 20-30% log size of data files
2. if it reaches limit it gives error. it will not compress.
3. Usually we create Archive csv files and store it in media
4. we do autopurge through jobs every month


Mostly all development boxes the log is set to 'truncate log on checkpoint'

 

[tlbroadbent]

SQL Server has many self-management features but it cannot be left without administration. Who backs up databases? Who controls database size? Who repairs databases. Who monitors performance?

The best way to control Transaction Log size is to create and use a good backup strategy. There should also be a purge policy and procedure in place to control database size. I recommend that you read the following FAQs as well as other resources such as SQL BOL to help in developing a backup strategy.

faq183-1784- "How Do I Develop a Backup and Restore Strategy?"
faq183-1534- "Shrinking Databases and Logs - SQL 7 and Higher"

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLSister]

MAK9974;
Be wary of truncate log on checkpoint.
From Books online:
"Setting the TruncateLogOnCheckpoint property to TRUE implies that backup-maintained database integrity relies on backup of the database only. When TRUE, you cannot backup a database transaction log and backup strategies based on differential backup of the log are not available."

Running transaction log backups is much preferred in a production environment when you might need to recreate as much data as possible in a recovery.

 

[tlbroadbent]

SQLSister,

I was about to mention the same thing regarding Trunc. Log on Checkpt. (Simple recovery model in SQL 2000) but noted that MAK9974 did say "Mostly all development boxes the log is set to 'truncate log on checkpoint'." That is common, especially when development boxes have limited disk space. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLSister]

The only reason I mentioned it was because the original post reference what seemed to be a production database. Because it seems a simpler solution than what you propose, it needed to be noted that the solution has pitfalls.

 

[tlbroadbent]

I totally agree that we need to emphasize the point. I see the advice to set Truncate Log on Checkpt or just to truncate the log far too often. DBAs and developers must understand the ramifications of these actions. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.