| Author |
Topic |
|
dcarlin
Starting Member
10 Posts |
 Posted - 2004-04-20 : 10:00:27
|
| I am new to SQL Server. I have worked with Oracle for years. I am trying to understand how to administer transaction logs. I have one SQL Server database that is basically reloaded every night from an Oracle database. I am of the opinion that since I can recreate the SQL database from Oracle at anytime that it's not necessary to be in FULL RECOVERY MODE. I have chosen the SIMPLE RECOVERY MODE. However, when I first created the database it apparently defaulted to FULL and I have since changed it to SIMPLE. I have also limited the transaction log to 2gb in size. But, the log still grows when I run my Oracle to SQL DTS jobs and fills up. What is the deal? Why is the log still growing? How can I make it stop? Should I be issuing a commit/checkpoint in my DTS jobs? Is my problem due the fact that the database was originally in FULL RECOVERY MODE? Any information or links to information would be appreciated. |
|
|
MuadDBA
628 Posts |
Posted - 2004-04-20 : 10:25:38
|
| Make sure you use the fast copy method in DTS, which minimizes logging to the transaction log. |
 |
|
|
dcarlin
Starting Member
10 Posts |
Posted - 2004-04-20 : 11:05:20
|
| How do I configure the fast copy method? |
|
|
|
dcarlin
Starting Member
10 Posts |
Posted - 2004-04-20 : 11:15:42
|
| Nevermind, I found it. I was already using fast copy. ThanksIs the transaction log supposed to grow when in SIMPLE RECOVERY MODE? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-20 : 12:36:09
|
| Yes it will. But after the process is done, it will clear itself out. It needs to use the log in case the transaction is cancelled. FULL recovery model is for point in time recovery. But SIMPLE recovery model will still use the transaction log, it just won't save the stuff in there after the transaction has completed.Tara |
|
|
|
dcarlin
Starting Member
10 Posts |
Posted - 2004-04-20 : 13:00:06
|
| It sounds like what I need to do is figure out a more appropriate size from the transaction log. Apparently, 2gb isn't enough.On a side note. What would it take to truncate the log back to it's original size? I originally created it as a 1gb file. I'd like for it to be able to return to that size once my jobs are complete. Is that possible? If so, how? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-20 : 13:02:51
|
| Yes it is possible, but I wouldn't recommend it. It is possible with DBCC SHIRNKFILE. The reason why I wouldn't recommend it is that the next time that your process runs, it is going to receive a performance hit due to having to expand the file. Since the process needs a 2GB transaction log, leave it alone. If you are ok with the performance hit, then just run DBCC SHRINKFILE after it completes.Tara |
|
|
|
dcarlin
Starting Member
10 Posts |
Posted - 2004-04-20 : 13:35:06
|
| Ok, thanks for the info. One last question. In my situation, should I be backing up the transaction log? I am thinking no because I am in SIMPLE RECOVERY MODE. But, and this may not be a problem with a larger log, I have received errors when my DTS jobs fail that tell me that the log is full and to back it up. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-20 : 13:38:33
|
| You can not backup a transaction log while in SIMPLE recovery mode. For your DTS problem, I would expand the transaction log even more to accomodate what your DTS needs. Do you have the autogrow option turned off? If not, it should autogrow until it can't grow anymore due to the amount of free disk space. Typically, the transaction log is about 25% of the size of the MDF file.Tara |
|
|
|
dcarlin
Starting Member
10 Posts |
Posted - 2004-04-20 : 13:46:11
|
| Autogrow is on but is limited to 4gb (changed from 2gb). I hope that will be all I need. However, before I changed to SIMPLE mode and was in FULL mode, it had grown to 28gb!! And the database itself was only 6gb. That is when I started investigating different recovery modes and started asking questions. Thanks again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-20 : 13:53:22
|
| Well, while in FULL recovery model, transaction log backups would have had to occur in order to keep the file size down. We backup ours every 15 minutes due to the sensitivity of the data.With SIMPLE recovery model, you lose the ability to restore to a point in time. So this means that upon failure, you will only be able to restore up to the last full backup or last differential backup. If your organization requires less data loss, you should put the mode back to FULL and start backing up the transaction log.Tara |
|
|
|
|
Transaction log questions