| Author |
Topic |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2006-04-27 : 11:47:40
|
| I have log file is 21 gig...i cannot shrink thisMode is FullShould i just do a detach and then delete log file then reattach.Is there anything i can do without having to do detach.can i do thisBACKUP LOG database WITH TRUNCATE_ONLYWill this help |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-27 : 12:02:32
|
| You should be running database backups and transaction log backups if your database is in full recovery mode. Your transaction logs will continue to grow until you run the transaction log backup.I recommend a daily full backup, and transaction log backups scheduled every 15 minutes. You can set this up with the Database maintenance Wizard.After you run the backups, then you can run DBCC SHRINKFILE to reduce the size of your transaction log file to a reasonable size.CODO ERGO SUM |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-27 : 12:58:46
|
| Do you need the recovery model to be FULL? Are you performing regular transaction log backups? If no and no, then set the recovery model to SIMPLE then shrink the file.Tara Kizeraka tduggan |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2006-04-27 : 15:06:03
|
| Maybe there's an open transaction that never got committed |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-27 : 15:10:33
|
| It's most likely due to DBCC DBREINDEX (or optimizations job from a maintenance plan) that caused this. This is normal for your tlog to grow if you have it running. If you do have it, you should NOT shrink your transaction log as you will receive a huge performance penalty the next time the job kicks off as it will need to expand the LDF file. You should add more disk space to accomodate the job if you are insistent on running it, which you shouldn't be. Check this out for details:http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspxHow big is your MDF?Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-27 : 16:29:37
|
| Tara: If its only DBCC DBREINDEX that is causing the problem would switching to DEFRAG instead use less log space?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-27 : 16:34:47
|
| It logs less, so yes. But switching should be based upon one's analysis of the MS article mentioned in my weblog along with what Paul mentioned. Depending on the situation, you may not need to run either.Tara Kizeraka tduggan |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-27 : 17:39:53
|
| Based on the questions asked in the original post, my guess of the situation would be:1. No tran log backups are running, and likely no backups.2. If there are no backups, why would there be reindex jobs?3. It has probably been running this way since the DB was setup, and the transaction log file finally filled up the disk.It would be nice if the Create Datebase Wizard in Enterprise Manager created a daily backup and hourly transaction log backup by default. The people that really don't know what they are doing would have at least some protection from self-inflicted injury.We seem to get a post like this every day or so, along with the "I deleted my table/database. I don't have a backup. Please tell me how to get my data back."CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-28 : 02:24:17
|
| "a daily backup and hourly transaction log backup by default"A Maintenance Plan can be set up for All User Databases - so that could be an option on Install of SQL Server ...Kristen |
|
|
|
|
log file is 21 gig