| Author |
Topic |
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
 Posted - 2004-09-24 : 14:34:21
|
| When you detach a DB and then rename the transaction log file so that it will create a new one (in the case where the xaction log file has grown huge) , Should I check the 'update statistics prior to detach' in order to make sure that any uncommitted transactions getting written to the DB? Also, do the DB options or recovery model have to be set a certain way in order for this to work properly? Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 14:38:21
|
| update statistics prior to detach doesn't have anything to do with uncommitted transactions. It has to do with statistics. No you don't have to have any special settings for this to work.But why has your transaction log grown huge? Are you sure that you won't need that size again? It is often a mistake to shrink it down since it might need it later anyway. You receive a performance hit when it has to expand its, so why shrink it down in the first place. Optimizations job can cause the tlog to grow larger than you expect. I don't agree with having any regularly scheduled shrinks.Tara |
 |
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2004-09-24 : 14:48:29
|
| Thank you!! I am just trying to figure out what to do with a 6gb DB that has a 67gb log file!!! I think it grew to this size as a result of the 'reorganize data and index pages' switch which was running once a week in the maintenance plan which I have since turned OFF. I know I can detach and let it create a new xaction log, put it's production so..... someone also suggested: USE MYDatabaseGOBACKUP LOG MYDatabase (MYDatabas_log,1)GO with a full backup before and after....just trying to figure out what is the best thing to do. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 14:50:14
|
| What is your database recovery model set to? If FULL, are you performing regular tlog backups?67GB is rather large for a 6GB db even with optimization stuff turned on.Tara |
|
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2004-09-24 : 14:58:04
|
| my recovery model is set to full with torn page detection and autoshrink is off. I do a nightly FULL and Transaction log backups through the day. I expect this beast to keep growing.. The Application is for Document Retention.. contracts, proposals.. and from what I understand, they are scanning in several years worth of documents.. Do you use the 'reorganize data and index pages' switch?? I turned it off after I saw what it did to my xaction log!!!! |
|
|
|
zipman1952
Starting Member
14 Posts |
Posted - 2004-09-24 : 15:00:50
|
| >>>I don't agree with having any regularly scheduled shrinks.You have something against the psychiatric profession? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 15:03:20
|
| I don't use maintenance plans. I write my own custom ones:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxA transaction log backup once a day is not very much. We backup ours every 15 minutes. Do you even require FULL recovery model? Do you need the ability to restore to a point in time, if not then change the recovery model to SIMPLE and start backing up your tlog more often. The document management systems that I have worked on have all used SIMPLE recovery model so far. Full backups at night was always fine for us as the data could be recreated elsewhere.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 15:05:00
|
quote:
Originally posted by zipman1952
>>>I don't agree with having any regularly scheduled shrinks.You have something against the psychiatric profession?
 Tara |
|
|
|
zipman1952
Starting Member
14 Posts |
Posted - 2004-09-24 : 15:07:47
|
| Tara, we back up the db once a day, back up the tran log every hour, but truncate the log only once a day. I am arguing for truncating it every time we back it up. Generally, is my position a correct one?thankskevin |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 15:09:33
|
| Do NOT truncate the log! You are breaking the chain of transaction log backups. Truncating the tlog should only be done when you are out of disk and can't even backup the tlog. Backing up the tlog already truncates the log for you when it writes the info to the backup file. You don't need another command to do it. Check BOL for details.Tara |
|
|
|
zipman1952
Starting Member
14 Posts |
Posted - 2004-09-24 : 15:12:01
|
| Thanks for the info, I'll do some more reading. |
|
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2004-09-24 : 15:15:07
|
| psychiatric profession? gee thanks. My recovery model is set to FULL in order to be able to backup the transaction log - can't do that with simple I thought.. anyway. I do a full every night and backup the tlog every 15 minutes. I'm not trying to schedule any kind of regularly scheduled shrinks, just trying to figure out how best to do a one time shrink to get the transaction log filesize back on track so I don't end up with a space problem. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 15:17:09
|
| For a production environment, you should use DBCC SHRINKFILE. Do it a little at a time so that you don't impact your customers. I would do 2GB at a time. Each time I've tried to shrink it down 50GB or more at a time, the system has become unusable. Luckily, that was in a non-production environment and since then I've learned my lesson.Tara |
|
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2004-09-24 : 15:20:07
|
| Great. Thanks! I'll give it a shot this weekend.. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-25 : 05:59:30
|
| "67GB is rather large for a 6GB db even with optimization stuff turned on"Tara, at what point do you consider using "very" in place of "rather"? <g>Kristen |
|
|
|
|
Detach and Attach