Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

Large Transaction log files

Hi Experts,

I got a Project server solution where some of the databases has a large transaction log files:
I manually made a full backup of all the databases (shouldent that schrink the Transaction log files?)
etc. the ProjectServer_Draft =  1.967.300 kb and the ProjectServer_Draft_Log = 61.152.320 kb.

When the log file did not schrink when backing up the database, how do i then schrink the log file?

Best regards
Torsleff
0
Torsleff-Experten
Asked:
Torsleff-Experten
  • 6
  • 3
3 Solutions
 
spiderwilk007Commented:
You need to change the recovery model to simple, in SQL manager right click the databases that you need to change => properties => "options tab" Change "Recovery Model" to Simple. Then take the database offline then bring it back online and the log file will shrink to almost nothing. That is the only way to keep the log files small otherwise they continue to grow.
0
 
Ephraim WangoyaCommented:

you can use  DBCC SHRINKFILE
http://msdn.microsoft.com/en-us/library/ms189493.aspx

Avoid the 'with truncateonly' option

You could also set your database recovery model to SIMPLE
0
 
Torsleff-ExpertenAuthor Commented:
ok, i just releast unused space on the log file, that gave about 5 gb, but the file i still over 56 gb can i just schrink it firther etc. to 1 gb or lower?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
spiderwilk007Commented:
Yes, but only if you change recovery to simple like I explained above. Otherwise the Log file will just continue to grow. If you do a nightly backup on the database and are not worried about a partial days worth of transactions you should be just fine to do the simple recovery model.
0
 
Torsleff-ExpertenAuthor Commented:
ok ewanqoya,

i will set it to simple, when i have a full backup i will not risk anything by schrinking the log file?

even if i schrink it to the minimum size?
0
 
spiderwilk007Commented:
Alternatively, you can change the recovery model to simple, take the database offline and then bring it back online. You will see the log file shrink almost to nothing, and then change recovery back to "full" take the database offline then bring it back online and you will have successfully shrank the file, but it will again start to grow because you have selected to use the "Full" recovery model. If that makes sense.
0
 
spiderwilk007Commented:
No you will not risk anything, if you have a full backup. The only thing the log file is for is incase your server crashes mid day and you want to restore transactions that took place since the last database backup.
0
 
Torsleff-ExpertenAuthor Commented:
ok, taking a db with simple recoverymode offline will schrink it to the minimumt? nice..

simple mode is fine with a full backup once or twice a day is ok.
0
 
spiderwilk007Commented:
YEP, I do it on our SQL databases and just have them backup each night.
0
 
spiderwilk007Commented:
How is it going?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now