Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ankit Mathur
Starting Member
24 Posts |
 Posted - 2008-01-11 : 02:27:57
|
| Hi,My Transcaction log is increasing manifold to upto 20GB which is almost twice the size of whole DB. This is threatening to be a possible DISK SPACE CRUNCH scenario on my system in another few months.I wish to reclaim the precious disk space also without taking my DB offline. I tried with transaction log backup but while it does take the backup doesn't let me reclaim the disk space.I want to know what are my options w.r.t Disk Space Reclaim.Please help.Ankit |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-12 : 13:01:02
|
| You can shrink it in em or with 'dbcc shrinkfile'. How often do you backup log? |
 |
|
|
Ankit Mathur
Starting Member
24 Posts |
Posted - 2008-01-13 : 23:09:45
|
| Hi,I have a Full DB Backup scheduled for every week & a Transaction Log backup scheduled for every midnight.With this job I'm hoping that I'll be able to curtail down the log growth but I also wish to reclaim the space from log file.Would Shrinkfile let me reclaim space? Is there any side-effects that I need to watch out for?I remember trying shrinkfile once but there wasn't any immediate effect I could see w.r.t the log space being utilised of the disk. Does it take some time to reflect?Thanks for your help.Ankit |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-13 : 23:36:59
|
| Should backup log more often, hourly at least. |
|
|
|
Ankit Mathur
Starting Member
24 Posts |
Posted - 2008-01-13 : 23:55:24
|
| I'll consider that option too but coming back to the topic I'd like to take your attention to some of the questions I posed w.r.t shrinkfile in my earlier post.If possible can you apprise me about that.Thanks for your time.Ankit |
|
|
|
itsonlyme4
Posting Yak Master
109 Posts |
Posted - 2008-01-14 : 11:21:43
|
| try this to shrink the log file and reclaim the space. I use it all the time: --sp_helpdb to verify log size, FileID and recovery Mode of the logsp_helpdb [database]--If necessary, change recovery mode to SimpleUse [database]alter database [database] set recovery simple--Backup LOG with NO_LOGUse [database]BACKUP LOG [database] WITH NO_LOG--Shrink log file. The number in ( ) is your fileidUse [database]DBCC SHRINKFILE (2)--If necessary, change recovery mode back to FULLUse [database]alter database [database] set recovery FULL--be sure to perform a FULL database backup when completed |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-01-14 : 11:40:16
|
You should setup transaction log backups to run much more often; a good starting point is ever 15 minutes, 24x7. This will help to prevent growth of the transaction log from getting out of hand.To shrink a transaction log file, use dbcc shrinkfileExample:dbcc shrinkfile(MYDBLog, 1000) CODO ERGO SUM |
|
|
|
|
|
|
Reclaim Transaction Log Space