February 13, 2009 at 11:57 am
Hey guys,
I work for a software company, our web application uses a SQL database for its backend. I took our application and set it up on one of our sales guys laptops. The system was fine then today is computer was showing low on disk space, it seems the database file "databasename_log.ldf" grew to 180GB. Can I shrink this file, cause its causing some issues.
Thanks,
d
February 13, 2009 at 12:14 pm
do you make LOG backups ?
That's the only way to clear the log file entries.
Gail has posted a nice article on this matter.
http://qa.sqlservercentral.com/articles/64582/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 14, 2009 at 5:12 am
Before you try shrinking, you need to work out why it grew in the first place. Otherwise it may well grow again and cause bigger problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2009 at 6:19 am
dharris (2/13/2009)
Hey guys,I work for a software company, our web application uses a SQL database for its backend. I took our application and set it up on one of our sales guys laptops. The system was fine then today is computer was showing low on disk space, it seems the database file "databasename_log.ldf" grew to 180GB. Can I shrink this file, cause its causing some issues.
Thanks,
d
Have you performed any heavy transactions recently? like deleting a table, rebuilding indexes, Bulk loading?
Shrink the log file, change your recovery model to Simple or take regular Tran Log backups according to your business requirements to avoid this situation in the future:)
February 16, 2009 at 5:13 am
dharris (2/13/2009)
Hey guys,I work for a software company, our web application uses a SQL database for its backend. I took our application and set it up on one of our sales guys laptops. The system was fine then today is computer was showing low on disk space, it seems the database file "databasename_log.ldf" grew to 180GB. Can I shrink this file, cause its causing some issues.
Thanks,
d
I think DBCC SHRINKFILE can be used to acheive this.
February 16, 2009 at 5:19 am
pavan_crm (2/16/2009)
dharris (2/13/2009)
Hey guys,I work for a software company, our web application uses a SQL database for its backend. I took our application and set it up on one of our sales guys laptops. The system was fine then today is computer was showing low on disk space, it seems the database file "databasename_log.ldf" grew to 180GB. Can I shrink this file, cause its causing some issues.
Thanks,
d
I think DBCC SHRINKFILE can be used to acheive this.
Yep, that's true. But before doing this he has to consider how small he can shrink the file to?
February 16, 2009 at 6:08 am
Hi,
I suppose that you don't perform transactional log backup's 😉
That is reason why is your log file so big.
You need to do this:
Execute:
BACKUP LOG DatabaseName with truncate_only
DBCC SHRINKFILE (FILENAME, 1000)
Set recovery model to simple.
If your database must be in full recovery model then please run transactional log backup's 🙂
February 16, 2009 at 6:10 am
kladibeeto (2/16/2009)
Execute:
BACKUP LOG DatabaseName with truncate_only
DBCC SHRINKFILE (FILENAME, 1000)
Not at all. Truncate_Only option is disastrous. Please do not use this option as it breaks the log chain of the backupsets. It is not a good practice.
February 16, 2009 at 7:28 am
Hi,
Please explain me which log chain will he break if he don't work transactional log backups?
February 16, 2009 at 7:34 am
kladibeeto (2/16/2009)
Hi,Please explain me which log chain will he break if he don't work transactional log backups?
log chain for backups. LSN's will go out of order, which means you will never be able to restore to a point-in-time. If you need detailed explanation you should read this excellent article "Understanding Logging and Recovery in SQL Server" by Paul Randal:
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
February 16, 2009 at 7:47 am
It seems that You didn't understand my post.
You can't perform point in time backup without transactional log backup and if you don't have transactional log backup then you don't have log chain. If you don't have transactional log backup why do you have full recovery model??
It seems that you don't know difference between recovery model's and how to use transactional log backup.
Conclusion:
If You DON'T perform transactional log backup's
You need to do this:
Execute:
BACKUP LOG DatabaseName with truncate_only
DBCC SHRINKFILE (FILENAME, 1000)
Set recovery model to simple.
If you have transactional log backup's and your log grew to 180 gb then you should look for open transactions
February 16, 2009 at 7:58 am
kladibeeto (2/16/2009)
Please explain me which log chain will he break if he don't work transactional log backups?
Please read the article that ALZDBA linked to above. It's explained in there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2009 at 8:00 am
kladibeeto (2/16/2009)
It seems that You didn't understand my post.
I well understood your post and also OP's post
You can't perform point in time backup without transactional log backup and if you don't have transactional log backup then you don't have log chain.
Agreed
If you don't have transactional log backup why do you have full recovery model??
Read BOL
It seems that you don't know difference between recovery model's and how to use transactional log backup.
See your below command for backup and decide who does not know
Conclusion:
If You DON'T perform transactional log backup's
You need to do this:
Execute:
BACKUP LOG DatabaseName with truncate_only
DBCC SHRINKFILE (FILENAME, 1000)
Wrong!!!!! will break the log chain. Did you ever restore to point-in-time recovery after performing your backup command????
Set recovery model to simple.
OP has to decide according to his business requirements
If you have transactional log backup's and your log grew to 180 gb then you should look for open transactions
Agreed, one of the reasons.
February 16, 2009 at 8:08 am
kladibeeto (2/16/2009)
Hi,Please explain me which log chain will he break if he don't work transactional log backups?
Here a clean explanation from Gail's excellent article "Managing Transaction logs":
"Log chains
Log backups form a chain which starts with the first full backup done to the database (or the first full backup after switching to full recovery). To be able to restore to a point in time, the log chain must stretch unbroken from a full or diff backup to the point that the database needs to be recovered to. If the log chain is broken, either by a log truncation, a missing log backup file or a switch to simple recovery mode, then the database cannot be restored past that point.
For this reason it is very important not to truncate the transaction log of a database. Truncating the transaction log of a database that is in full or bulk-logged recovery means discarding log records that may be needed for database recovery. The same applies to changing the database to simple recovery and back to full/bulk-logged. Either way, the log chain is broken and a new full or differential backup of the database is needed to restart the log chain."
The above explanation is much better.
Click on this: http://qa.sqlservercentral.com/articles/64582/%5B/url%5D
February 16, 2009 at 8:09 am
kladibeeto (2/16/2009)
Conclusion:If You DON'T perform transactional log backup's
You need to do this:
Execute:
BACKUP LOG DatabaseName with truncate_only
DBCC SHRINKFILE (FILENAME, 1000)
Set recovery model to simple.
If you aren't doing log backups, set the DB to simple (providing you don't need to be doing log backup) and then shrink the file to a reasonable size. The log will auto-truncate in simple, no need to explicitly do it.
Also, truncate_only is deprecated in SQL 2005 and removed in SQL 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply