| Author |
Topic |
|
dean.c4
Starting Member
14 Posts |
 Posted - 2011-12-06 : 17:22:48
|
| We have about 15 databases. They have logs that range in size from 768KB to 97MB. But one of them is 26GB. I have a weekly maintenance plan that does the following for these databases including the one that is so large:Check DB integrityReorganize IndexRebuild IndexUpdate StatisticsHistory CleanupBackup Database - FullBackup Database - Transaction LogShrink DatabaseDelete old bak filesDelete old trn filesThe maintenance plan is running successfully.Why is this one log so much larger than the rest and how do I get it shrunk down to a similar a size as the others? It's running my log file drive out of space. All DB's use Full recovery model. SQL 2005.I tried a BACKUP LOG (db name) WITH TRUNCATE_ONLY but it made no difference. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-06 : 17:35:46
|
| Please stop shrinking your database, it's a very bad thing to do regularly and it completely undoes any good that your index rebuild did (btw, reorganise and then rebuild is a complete waste of time. Reorganise shuffles the index back into order, then rebuild drops the index and recreates it)Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.See - [url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]A weekly log backup is by no means adequate. I hope you have more frequent log backups too. Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url] and this http://www.sqlservercentral.com/articles/Transaction+Log/72488/--Gail ShawSQL Server MVP |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-06 : 18:24:41
|
| Ok, I will make those changes, but back to my original question. How do I trim this log file down to size? I thought logs were supposed to get truncated after a db backup? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-07 : 02:29:04
|
quote:
Originally posted by dean.c4
I thought logs were supposed to get truncated after a db backup?
Log backup yes, not full backup.Please read the last of the links I provided. It's an article 'Why is my transaction log full' and it addresses the various reasons why the log might not shrink.--Gail ShawSQL Server MVP |
|
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-13 : 16:21:47
|
| Ok, I changed my weekly maintenance plan to only perform the following steps:Check DB IntegrityReorganize IndexUpdate statsHistory Cleanup.I created a new maintenance task to make a full DB backup once daily (this entire machine is backed up once daily and replicated offsite throughout the day).I created another new maintenance task to perform log file backups every 15 minutes.But this one database is less than 140Mb but it's log file is 29Gb. How do I cut that log file down to size? We have a very small LUN created on a LUN10 RAID with 15,000 rpm fiber drives just for these SQL logs and that one log file is taking over half of the space and is 200x the size of the database itself. Why isn't it shrinking? Can it be truncated? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-13 : 17:16:05
|
| Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/--Gail ShawSQL Server MVP |
|
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-13 : 17:29:35
|
| I ran the DBCC Shrinkfile and it only shrunk to 26Gb. This is in fact an old database that isn't used anymore but I have to keep it for archive purposes. Can I just delete that log file?I have read the article that GilaMonster recommended. In my log_reuse_wait_desc column for that database it says "NOTHING". So there's no reason why it wouldn't shrink. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-13 : 17:50:37
|
| Yes. It did shrink, but only from 29GB to 26GB. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-13 : 18:16:11
|
| use MyDatabasegoDBCC SHRINKFILE (MyDatabase_log, 2) WITH NO_INFOMSGSgo |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-12-13 : 20:46:18
|
| Run this: Select * From sys.databases and look at the row for that database. Find the log_reuse_wait_desc column and that will tell you why the log file cannot be truncated.You can also run DBCC LOGINFO. Look at the status column - any rows with a value of 2 are active portions of the log. The log file will not shrink any more than the latest active portion of the log. If you have only a couple of rows with a 2 at the end of the list, then you can run another log backup to roll it over to the beginning of the file and shrink again (note: if the database is in simple recovery, you cannot perform a log backup - issue a CHECKPOINT instead.Jeff |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-14 : 04:06:26
|
quote:
Originally posted by dean.c4
I ran the DBCC Shrinkfile and it only shrunk to 26Gb. This is in fact an old database that isn't used anymore but I have to keep it for archive purposes. Can I just delete that log file?I have read the article that GilaMonster recommended. In my log_reuse_wait_desc column for that database it says "NOTHING". So there's no reason why it wouldn't shrink.
You can't delete a log without potentially destroying your database.What's probably happened is that the active portion of the log is at the end of the file. Nothing can move log records around.Try this:Shrinkfile on the log filebackup log to disk = ....shrinkfile on the log fileThat's a pattern that SQL recognises as an attempt to shrink the log and it'll try and make the next active VLF the beginning of the file.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-14 : 08:26:31
|
Probably an obvious point, but in case not:If you were, previously, backing up your Transaction log infrequently (e.g. once a week) then the Log File would grow to hold a whole week's worth of transactions.If you change the log backup frequency to hourly (or better still to every 15 minutes) then the log file only needs to be large enough to hold 15 minutes worth of log data. Thus it will be MUCH smaller  You will have a lot more log backup files, but the total size will be the same (except for some overhead on each file).You will need to do a one-time shrink of Log file to get it back to a reasonable size (taking the advice from above because you probably have transactions at the end of the log file which have not yet been released, so you may have to "fiddle about a bit" to get it to shrink).Re: "how often to backup the log file"If you backup every 15 minutes then if you have a total-loss of your database you will lose, at most, 15 minutes data. If you are using Full Recovery Model it makes sense, I think, to backup as often as possible to take advantage of this feature and have minimum data loss if you have a disaster. The general consensus is that 15 minutes is a good trade off between very-frequent-backups and a tolerable-loss. (If you cannot afford a 15 minute data loss you should probably be looking at High Available Systems, rather than more frequent backups ) |
|
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-14 : 11:43:08
|
quote:
Originally posted by jeffw8713
Run this: Select * From sys.databases and look at the row for that database. Find the log_reuse_wait_desc column and that will tell you why the log file cannot be truncated.
I already did that. My post above states that column says "NOTHING"quote:
Originally posted by jeffw8713You can also run DBCC LOGINFO. Look at the status column - any rows with a value of 2 are active portions of the log. The log file will not shrink any more than the latest active portion of the log. If you have only a couple of rows with a 2 at the end of the list, then you can run another log backup to roll it over to the beginning of the file and shrink again (note: if the database is in simple recovery, you cannot perform a log backup - issue a CHECKPOINT instead.Jeff
When I did this it returned 584 rows with 2's (all rows).As I said, this DB is no longer used. The machine it runs on had a hard drive failure and we had to replace the drive and start a new DB to reinstall the software. It's a fax server. Is there any way to commit all these open transactions? |
|
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-14 : 11:46:29
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by dean.c4
I ran the DBCC Shrinkfile and it only shrunk to 26Gb. This is in fact an old database that isn't used anymore but I have to keep it for archive purposes. Can I just delete that log file?I have read the article that GilaMonster recommended. In my log_reuse_wait_desc column for that database it says "NOTHING". So there's no reason why it wouldn't shrink.
You can't delete a log without potentially destroying your database.What's probably happened is that the active portion of the log is at the end of the file. Nothing can move log records around.Try this:Shrinkfile on the log filebackup log to disk = ....shrinkfile on the log fileThat's a pattern that SQL recognises as an attempt to shrink the log and it'll try and make the next active VLF the beginning of the file.--Gail ShawSQL Server MVP
I just tried this and it shrunk it from 26GB down to 5GB. That's a little more like it. Now if I could just get it down to a couple of MB that would be great, but at least there's a little breathing room on that LUN now. Thanks. |
|
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-14 : 11:47:54
|
| W00t! Just repeated the above Shrink, Backup, Shrink routine and now it's down to 2MB! |
|
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-12-14 : 11:48:33
|
| Thanks for all the help everyone! |
|
|
|
Next Page
|
SQL logs aren't shrinking