Log file
-
I have my application creating a database on Sql Server 2k. My QA team always reports that the log file size is getting too huge and then they have to go in truncate the log.
I use a simple CREATE DATABASE statement to create the database. I cannot specify the filename etc because i want to leave all that as defaults.
But i would definitely want to set a limit on the log file as 300MB. how would i do that after running CREATE DATABASE.
Any help is appreciated...
-
One other thing to note is the application is simple but the log seems to grow to 58g even though the data file itself is not even 300mb.
Any ideas why this can happen. I checked and there are no open transactions.
-
Cursors, cross joins, bad table design, dozen of other reasons.
BTW, simple applications give most terrible load on database.
_____________
Code for TallyGenerator -
"cross joins" we do a lot of cross joins involving 5 different tables a lot of times.
-
A basic create databse command has the recovery model as full, if you are not doing regular transaction log backups then the log will bloat over time. Is the databse in full mode, are the logs being backed up, or is the database in simple and you are doing regular backups of the database?
-
If you want to easily keep log file to minimum, and can live with recoverability only to last full backup, change your recovery model to simple. That will truncate the log at each checkpoint (each recovery interval when SQL server thinks it should). You can also force the log to be truncated by executing Backup log with truncate_only.
If you want to keep the logs for recoverability, backup the logs more frequently. The more often you backup the log, the less likely the log will grow, and each backup will take less time. On high-volume databases, I have even set tlog backups to occur every 5 minutes.
Mark -
And those cross joins must be inside of cursors, right?
_____________
Code for TallyGenerator -
As some have correctly suggested, unless you do backups everyday, you should have the "recovery mode" set to SIMPLE.
DO NOT LIMIT THE LOG FILE!!! IF IT TRYS TO GROW BEYOND YOUR LIMIT... BOOOOMMM!!!!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Did you notice, it's not them who limited size of their log file.
It just reached the end of hard drive. Nowhere to go anyore.
And you were right, it's BOOOOMMM!!!!
_____________
Code for TallyGenerator -
THAT would certainly be a limit, huh?
Yeah, I know they reached the end but then they said they'd limit it to 300 MB... Death by SQL is what I'm thinking....--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply