| Author |
Topic |
|
csaha
Yak Posting Veteran
52 Posts |
 Posted - 2010-10-26 : 17:22:54
|
| I did not backup the transaction log so my ldf file grew big. My ldf files in Play database are 13 gig. Can I shrink the ldf file? Will it cause any issues? Will I loose any data? I am now backing up the transactional log.I ran the following statementuse[Play] go sp_helpfile go File name = IMSA2 and IMSA2_log( this is 13 g) can I shrink this using the following commandBACKUP LOG [PLAY] WITH TRUNCATE_ONLYThen shrink the file DBCC SHRINKFILE(IMSA2_LOG, 500) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-27 : 01:44:40
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
 |
|
|
psychotic_savage
Starting Member
25 Posts |
Posted - 2010-10-27 : 03:31:09
|
| This is what I use in the event of a run away log file. Once I've resolved the run away query of course.USE [<DBName>]GOCHECKPOINTGOBACKUP LOG [<DBName>] TO disk='<Backup Location>'GODBCC SHRINKFILE(<DBLogfileName>, <size>)GO<DBName> = Name of the database you looking to shrink.<Backup Location> = location for the transaction backup. ie 'D:\SQLBackup\databaseLogBackup.trn'<DBLogfileName> = Name of the log file (select name from sys.database_files where type_desc = 'log')<size> = Size you want to shrink the file to.You may have to run the statement twice to get it down to the desired size. Remember to change the name of the backup or you will over write the file and have an issue if you need to restore the logs. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-27 : 04:12:13
|
| "You may have to run the statement twice to get it down to the desired size"More likely, I think, you will have to create additional transactions after the first Tlog Backup and before you make a second TLog backup. This is to fill-up the last used page in the TLog file, and then resume adding transactions to the (now-backed-up) beginning of the Tlog file. This then allows the last page to be backed up, and flagged as "available for reuse", at which point Shrink can then delete the page.Otherwise, if the last page in the file is still marked as "In use", Shrink won't be able to release that page, and thus won't be able to shrink the file smaller than that page. |
|
|
|
psychotic_savage
Starting Member
25 Posts |
Posted - 2010-10-27 : 07:01:23
|
| I always had a suspicion that that was the case. But always wandered why :). Question though why would a portion of the log file get shrunk on the first run. ie. Log file is 15gigs and on the first run it backs up and has around 1mb used space. Then on the shrink it shrinks to around 7gigs(random amount).Would it only shrink to the time before size pre-backup? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-27 : 07:05:17
|
| Might be that the file once grew to 15GB, and then was backed up / hacked about with, and then grew back to 7GB before you came to shrink it?But I too have had some rather random experiences with run-away log files. |
|
|
|
csaha
Yak Posting Veteran
52 Posts |
Posted - 2010-10-27 : 14:48:47
|
| Thank you all! |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-10-27 : 17:10:43
|
| The reason why it shrinks to some random size is due to how the VLF's are currently being used. Let's say the log file grew out to 15GB at 1GB intervals. For each 1GB growth, 16 VLF's would have been created - so, you would have quite a few VLF's in the log file.At the time of your shrink command, the shrink operation can only shrink to the latest VLF file that is marked as active. If that VLF happens to be in the middle of the log file - then it will only shrink to that point (around 7GB). If the last VLF that is marked as active happens to be at the end of the log - then the shrink would not remove that much space.After the first shrinkfile - the active VLF is now at the end of the file. Once that VLF is full and the usage has rolled back to the beginning of the log, you can perform a log backup - marking that VLF at the end of the file as no longer in use and the file can be reduced further.Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-28 : 03:09:25
|
| Ah, Good point Jeff, and why we go through an initial Create and Expand process to ensure we have as few VLFs as possible. Can't remember where we got it from now, Tara's blog I think ... |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-10-28 : 22:30:00
|
| Kimberly Tripp has blogged about this here: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx#p2And, this one is also very interesting: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx#p3 |
|
|
|
csaha
Yak Posting Veteran
52 Posts |
Posted - 2010-10-29 : 10:16:42
|
| Do I need to use the following command to shrink the logfile DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)? Or can I use SHRINKFILE(<DBLogfileName>, <size>), I am trying to shrink the log file after I have started generating the transactional log. |
|
|
|
csaha
Yak Posting Veteran
52 Posts |
Posted - 2010-10-29 : 10:59:46
|
quote:
Originally posted by jeffw8713
Kimberly Tripp has blogged about this here: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx#p2And, this one is also very interesting: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx#p3
Do I need to use the following command to shrink the logfile DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)? Or can I use SHRINKFILE(<DBLogfileName>, <size>), I am trying to shrink the log file after I have started generating the transactional log. Thank you. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-29 : 11:58:41
|
| As per Books Online, Truncateonly is an option only for the data file.--Gail ShawSQL Server MVP |
|
|
|
|
Shrink ldf file