• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

Can't get Sharepoint 2010 SQL logs to reduce in size

These two log files in particular are taking 80+ gigs.  The databases themselves are really small in comparison.  How do I get these to shrink?  I've done all I know to do.  I performed a full backup of each database, which as I understand it should trigger SQL to reduce the log files.  I've gone into Management Studio and on each database, Tasks -> Shrink -> Files -> Log.  It'll reduce it by a couple gigs, but then usually goes right back up.

I'm also not sure what all those "aggregation" files in the screen shot are.  I don't remember those always being there.

(see attached screenshot)
splogs.png
0
mgudites1
Asked:
mgudites1
  • 3
  • 3
1 Solution
 
Justin SmithSr. System EngineerCommented:
You need to backup the Transaction Log, not the database.  Run a Transaction Log backup on both logs.  THen run a query on each to shrink....

dbcc shrinkfile ('databasename_log',2000)

Change the database name above.  Typically the log file name is suffixed with _log.  The above will shrink to 2 GB.
0
 
mgudites1Author Commented:
I backed up the log, and now when I go into the "File Shrink" area, it says 99% of it is recoverable free space, which sounds promising.  But, after running that query, though it says it is successful, does nothing.  There's no reduction in hard drive space.
0
 
Justin SmithSr. System EngineerCommented:
Did you actually run a New Query against your database, with the above command?  Or were you using the "Shrink" option?

If you used my command, are you sure you suffixed _Log at the end of the db name?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
mgudites1Author Commented:
I actually ran a query, not the "Shrink" option in the GUI.  Yes, I appended "Log" at the end.  

It finishes with:
DbId      FileId      CurrentSize      MinimumSize      UsedPages      EstimatedPages
24      2      5417264      63      5417264      56
0
 
Justin SmithSr. System EngineerCommented:
It may take a bit for SQL to commit all those transactions, since I'm guessing this is the first time the log has been backed up.  I would try again in about 10 minutes.

Another thing you can do, you can go into the Properties of the database, Options, change the recovery model to SIMPLE.  Wait a few minutes then do the query again.

Also, to prevent this from happening again, you either set the databases to SIMPLE recovery, or you need to regularly back the log up.
0
 
mgudites1Author Commented:
Got it, thanks a lot!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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