July 15, 2009 at 3:08 pm
Hi:
can any one please help me managing my modellog.ldf file. it grows surprisingly toooo large(more than 20 GB where my data.dbf file is 1.5 GB).
my server space now almost runout of space because of that.
What should I do now?
Thanks in advance
Maksuda
July 15, 2009 at 3:12 pm
My guess is your database is in the full recovery model and you are not making log backups.
The short answer is that you should:
1. Backup the transaction log with truncate_only
2. Make a full backup immediately
3. shrink the log file with dbcc shrinkfile back to a reasonable level
4. Set up log backups every hour or two.
You ought to read about how to manage transaction logs as well: http://www.sqlservercentral.com/articles/64582/
July 15, 2009 at 3:14 pm
h_maksuda (7/15/2009)
can any one please help me managing my modellog.ldf file.
Is that the model database?... is an application running on model database?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 15, 2009 at 5:06 pm
Steve Jones - Editor (7/15/2009)
The short answer is that you should:
1. Backup the transaction log with truncate_only
2. Make a full backup immediately
3. shrink the log file with dbcc shrinkfile back to a reasonable level
4. Set up log backups every hour or two.
You ought to read about how to manage transaction logs as well: http://www.sqlservercentral.com/articles/64582/%5B/quote%5D
Steve, you really should not give the advice to use truncate_only anymore. Remember, that has been deprecated in 2005 and is no longer functional in 2008.
The recommended procedure now is:
1. ALTER DATABASE {your database} SET RECOVERY SIMPLE;
2. CHECKPOINT -- possibly do this a couple of times to roll the VLF to beginning of file
3. Shrink the log file using shrinkfile to a reasonable size
4. ALTER DATABASE {your database} SET RECOVERY FULL;
5. Perform a full backup now to reset the log chain and allow for transaction log backups
6. Set up frequent log backups (every hour or two, or more often)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 6:27 pm
Thank you very much guys for the helps.
I'm almost new in SQL 2005 (DB) management. So I’m not familiar about db back up-recovery processes.
PaulB, yes it is model database and no application is running on model database.
Here are the features that my database has:
Recovery model: Simple.
Also there was a full back up process (SQL job) which took backup once in a day. Two days before I turned it off (as server was running out of space and size of back file was 10 GB) and removed the back file to another machine to make room for the server.
Our new production server is on the way to set up and I have to move my db (with data) to that server. Still I need 5/8 days and I'm afraid by this time my server may corrupt.
Also after moving to the new server if modellog file grows the same way, I'll be in the same problem.
Jeffrey I have few concerns (perdon me if my concern upset you). The procedures that you gave is ok even I move to new server? If I shrink the log file still I could move to the new server without any problem?
Now few things are not clear to me Jeffrey( Sorry I'm very new):
1. My database is already in Simple recovery mode - so it's ok.
2. Checkpoint - is it a command or part of command.
3. Shrink the log file - Would you please write down the command for that.
4. Alter database- It’s ok
5. Perform a full backup to reset the log chain and allow transaction log backup - Jest taking the full backup will reset the log chain or I need to do something else. Also allowing transaction log backup what I have to do?
6. Set up frequent log backup - You mean I should set a back up only for log file(s)? Is it possible to set for log file only? I set up a back up which was by SQL job (I mentioned before).
Thanks again in advance for the great help.
I need help badly to solve the problem.
Maksuda...
July 15, 2009 at 7:29 pm
The model database is a system database in SQL Server. It is the database that is used as the 'model' for a new database when you issue a create database statement.
If that database has a log file that is 20GB - your new databases will also have a log file that is 20GB. If the size of that database is 1.5GB - your new databases will also have that size.
The default settings for that database are set to full recovery model. If the recovery model for that system database has been changed, then all future databases created on that instance will be created with that recovery model.
This is a database that should never be used - for anything other than setting default values for new database creation. For example, if you want all new databases to have certain stored procedures and/or tables with data - you would load them into this database. Then, new databases will have those objects.
I was specifically responding to Steve's message - and warning that using BACKUP LOG {database} WITH TRUNCATE_ONLY is no longer supported (deprecated in 2005 and does not work in 2008). Once that command has been issued, or you modify the recovery model to simple - the log chain is broken. At this point, you can no longer perform transaction log backups (different than a full database backup).
You can review the article I link to in my signature to find out how to manage your transaction logs.
CHECKPOINT is a command that you can issue. Normally, checkpoints are issued regularly by SQL Server and that process writes data to disk. Manually issuing the checkpoint clears open transactions from the transaction log. You cannot shrink the file until SQL Server is writing to the beginning of the file, and the only way to get there is to either perform regular transaction log backups or put the database in simple recovery model and issue a few checkpoints.
Once that has been done - you should be able to shrink the file (DBCC SHRINKFILE(logical_file_name, size). You should lookup the command in help 'DBCC SHRINKFILE'.
I think everyone here is concerned that you somehow have used the model database - which has caused the database to grow as well as have the transaction log grow as much as it has.
And finally, when you move a database from one server to another you can either detach/attach/copy the mdf/ldf files or perform a backup/restore. The files and the recovery model will stay the same until you change them.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 16, 2009 at 8:29 am
Could be me, but why do I get the feeling that it isn't the transaction log that was growing, but the backup file. Could it be that the daily backup was appending to the same file?
July 16, 2009 at 8:49 am
Thank you very much Jeffrey for the reply. My production db log file is not a problem at all right now.
I'm worried about my model db log file. And I'm not using this bd for anything still it's growing faster.
So I'm worried. What causing my model log file growing so faster, I have no idea.
Is there any way to fix the problem? If yes, please let me know.
There (in model db) is no transaction , still I need to run the command CHECKPOINT for model db?
I'll follow your steps that you described.
Thank you very much again for the support.
Maksuda...
July 16, 2009 at 9:01 am
h_maksuda (7/16/2009)
I'm worried about my model db log file. And I'm not using this bd for anything still it's growing faster.So I'm worried. What causing my model log file growing so faster, I have no idea.
If your log file is growing, something must be doing something to it. If you open it up in SSMS, are there tables with data in them in the model database?
Are there any 3rd partry applictions involved that could possibly be using it? Since this is a test/dev box, could any of your developers be putting something in it?
I'd fire up profiler and see if you can figure out who/what is connecting to it and what they are up to.
The Redneck DBA
July 16, 2009 at 9:47 am
sorry, haven't done enough production work with 2008. The Truncate_only option isn't in 2008.
As noted here (http://msdn.microsoft.com/en-us/library/ms144262.aspx), when you switch to simple mode, the log is truncated, so I'd follow jeffrey's advice.
Also, as noted, you must be performing transactions in model for the log to grow at all. NOTHING will make the log grow except activity in the databases.
If you've changed the model database to the simple mode, that's not necessarily a bad thing. I've actually submitted a Connect item to make this the default. So many people have log issues that I think if you're not knowledgeable about how to make log backups and recovery to an interval, the simple mode makes sense.
July 16, 2009 at 10:27 am
Thanks Jason for the reply. I already opened model db in SSMS to check if there is any table or any activities. No table is there and no other process is running on it.
I'm the only user of the server and only my application is running on it. So what else could be for faster growing model log file?
I need to figure it out as when I'll move to my production server I could manage it from the begning.
Thanks again
Maksuda...
July 16, 2009 at 10:29 am
Run Profiler, set to filter to the model database and look for activity. Something is occurring in there.
July 16, 2009 at 11:17 am
h_maksuda (7/16/2009)
No table is there and no other process is running on it.
:pinch: if TLog is growing something is running there.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 16, 2009 at 1:24 pm
Since this is a dev/test box, might try making the model database read-only and see what breaks.
The Redneck DBA
July 16, 2009 at 4:59 pm
Lynn Pettis (7/16/2009)
Could be me, but why do I get the feeling that it isn't the transaction log that was growing, but the backup file. Could it be that the daily backup was appending to the same file?
Hello....
This is the part from one of the OP's posts that has me wondering:
Two days before I turned it off (as server was running out of space and size of back file was 10 GB) and removed the back file to another machine to make room for the server.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply