Link to home
Start Free TrialLog in
Avatar of mgudites1
mgudites1Flag for United States of America

asked on

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
Avatar of Justin Smith
Justin Smith
Flag of United States of America image

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.
Avatar of mgudites1

ASKER

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.
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?
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
ASKER CERTIFIED SOLUTION
Avatar of Justin Smith
Justin Smith
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Got it, thanks a lot!