| Author |
Topic |
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
 Posted - 2008-10-12 : 04:46:01
|
| Hello,We just installed MS SQL 2008 a month back and Our log file D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Logis 13.5gb, how do we shrink this ?Or is it safe to delete it ?ThanksEhi |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-12 : 04:53:20
|
| http://www.sqlservercentral.com/Forums/Topic537056-146-1.aspxhttp://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic24290.aspx |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-12 : 05:10:54
|
| Ok thanks.REad through the article. But one question, why are the logs so HUGE ? Its been just over a month ??? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-12 : 05:13:53
|
quote:
Originally posted by afrika
Ok thanks.REad through the article. But one question, why are the logs so HUGE ? Its been just over a month ???
whats the recovery model used? |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-12 : 05:15:26
|
| Its on a remote dedicated server, and we have a 3rd party software, that was newly installed. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-12 : 05:24:00
|
quote:
Originally posted by afrika
Its on a remote dedicated server, and we have a 3rd party software, that was newly installed.
mostly transaction log grows when you've some intensive insert/delete/update operations happen on your db or even some index defragmenting or reorganising may result in lots of transactions which will be logged. Also how frequently are you backing up the transaction log? |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-12 : 05:39:39
|
| Backup ? Dailywe just installed the db, about 6wks back. Only 3 databases are live, others are still in the testing phase.I just got a feedback from our db admin, who says its safe to delete them. So We will back them up locally and delete them probably tomorrow or during the week. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-12 : 05:43:24
|
quote:
Originally posted by afrika
Backup ? Dailywe just installed the db, about 6wks back. Only 3 databases are live, others are still in the testing phase.I just got a feedback from our db admin, who says its safe to delete them. So We will back them up locally and delete them probably tomorrow or during the week.
do you mean even after daily backing up log file and shrinking you get log file size to 13.5 gb next day? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-12 : 05:57:05
|
| Refer the below link for a good article on transaction logs.http://www.sqlservercentral.com/articles/Design+and+Theory/63350/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-12 : 08:20:18
|
quote:
Originally posted by afrika
Hello,We just installed MS SQL 2008 a month back and Our log file D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Logis 13.5gb, how do we shrink this ?DBCC shrinkfile after taking log backupis it safe to delete it ? You should backup transaction log frequently to avoid huge growthThanksEhi
|
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-12 : 09:10:32
|
quote:
Originally posted by visakh16
quote:
Originally posted by afrika
Backup ? Dailywe just installed the db, about 6wks back. Only 3 databases are live, others are still in the testing phase.I just got a feedback from our db admin, who says its safe to delete them. So We will back them up locally and delete them probably tomorrow or during the week.
do you mean even after daily backing up log file and shrinking you get log file size to 13.5 gb next day?
no this was in answer to your question above, how often do we backup ? we backup daily |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-12 : 09:16:35
|
quote:
Originally posted by sodeep
quote:
Originally posted by afrika
Hello,We just installed MS SQL 2008 a month back and Our log file D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Logis 13.5gb, how do we shrink this ?DBCC shrinkfile after taking log backupis it safe to delete it ? You should backup transaction log frequently to avoid huge growthThanksEhi
thanks sodeep but we are told its safe to delete it. So shall delete it next week during office hours, after taking a local backup of the remote server. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-13 : 14:09:24
|
quote:
Originally posted by afrika
quote:
Originally posted by sodeep
quote:
Originally posted by afrika
Hello,We just installed MS SQL 2008 a month back and Our log file D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Logis 13.5gb, how do we shrink this ?DBCC shrinkfile after taking log backupis it safe to delete it ? You should backup transaction log frequently to avoid huge growthThanksEhi
thanks sodeep but we are told its safe to delete it. So shall delete it next week during office hours, after taking a local backup of the remote server.
It is very bad practice to delete Log file as your DB will be unavailable till you attach again. How critical is your data and what is company's policy regarding Recovery model? If it is urgent, Just change it to Simple Recovery Model and shrink the log file. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-13 : 14:11:48
|
| We just installed the db, and put 3 databases live from the old system.Thanks for your advice. WEll noted. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-14 : 02:01:41
|
| We have a contract with our hosting provider, who provide 247 support round the clock and has been the case for years now.Currently the local databases are being backed up in several ways. First there is a nightly scheduled backup in place that backs up your database with a stored procedure at about 12:30am, server time, each night. Then the 3rd party backup system will backup the live databases on the server and the .bak files created by the nightly stored procedure backup. Our Database recovery is done in several ways, we can use a .bak file to restore a database or we can use the copy of the live .mdf and .ldf files to restore a database, depending on how far back we need the database restored. A full backup is done of the entire server and twice a day and this backs up files that have changed since the last backup was taken.The transaction logs are backed up the same way. both by a 3rd party backup system and the nightly stored procedure.We also do our private backup of the entire server once a day and db, once at night and have an inhouse custom software |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-14 : 02:34:12
|
quote:
Originally posted by tkizer
When you say you backup daily, are you talking about the full backup or the transaction log backup? These are two different things and very important to this discussion. We run full backups daily and transaction log backups every 15 minutes. Please be sure to clarify this issue for us.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Just confirmed from our db admin. That "These log files are the SQL error logs. These files are safe to delete. They are not needed for any type of database recovery" |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-14 : 02:38:44
|
 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-10-14 : 11:47:02
|
| Why are you showing us these files?Also, your hosting provider must not understand the importance of backing up the transaction log files. Running them nightly is definitely not enough and thus the reason why you have this issue.It is unnecessary to backup the live mdf/ldf files. In fact, you might not be able to restore from them since they are live. You should only backup these files if the SQL Server service is stopped or if the databases are detached. This way the files aren't live. These are known as cold backups. But hot backups are best in SQL Server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Log file is 13.5 gb