Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

My Modelog.lgd file is too large Expand / Collapse
Author
Message
Posted Wednesday, July 15, 2009 3:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 2:55 PM
Points: 28, Visits: 137
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
Post #753854
Posted Wednesday, July 15, 2009 3:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
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/







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #753857
Posted Wednesday, July 15, 2009 3:14 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #753861
Posted Wednesday, July 15, 2009 5:06 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
Steve Jones - Editor (7/15/2009)
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/


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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #753890
Posted Wednesday, July 15, 2009 6:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 2:55 PM
Points: 28, Visits: 137
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...
Post #753908
Posted Wednesday, July 15, 2009 7:29 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #753918
Posted Thursday, July 16, 2009 8:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 20,863, Visits: 32,901
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #754235
Posted Thursday, July 16, 2009 8:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 2:55 PM
Points: 28, Visits: 137
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...



Post #754260
Posted Thursday, July 16, 2009 9:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 1,544, Visits: 2,277
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
Post #754266
Posted Thursday, July 16, 2009 9:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #754300
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse