Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

shrinking the log file Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 3:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:29 AM
Points: 13, Visits: 151
Hi,

we have a problem at work and the DBA has just gone on holiday (on an airplan right now!)

We have a number of database, with log files on the G:\ of the db server. Note G:\ is 750Gig

The G:\ is now full and we are struggling to regain space.

Looking at the log files, the on for database ReportServerTempDB is over 400Gig in size.

Would shrinking this database file resolve our issue?

Any help would be greatly appreciated, also please bear in mind, we are not DBA's.

Many thanks,
Post #1346422
Posted Friday, August 17, 2012 3:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 42,826, Visits: 35,956
Shrinking the DB is not only not the solution, it'll potentially make things worse

Please read through this - Managing Transaction Logs and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1346433
Posted Friday, August 17, 2012 3:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:19 AM
Points: 728, Visits: 774
depending on what access you have to the SQL instance you may or may not be able to do this

Run SQL Server Management Studio and check to see if the ReportServerTempDB is in Simple recovery mode (right click properties, options), if its not set it to simple recovery. This won't recover the disk space but will clear the transaction which i'm presuming is not getting backed up. To get the space back run this in a new query, replace the 1000 to whatever you want the new size to be in MB.

USE [ReportServerTempDB]
GO
DBCC SHRINKFILE ('ReportServerTempDB_log' , 1000)
GO

you can set the DB to simple recovery using this as well

USE MASTER
GO
ALTER DATABASE [ReportServerTempDB] SET RECOVERY SIMPLE
GO
Post #1346440
Posted Friday, August 17, 2012 4:01 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:19 AM
Points: 728, Visits: 774
GilaMonster (8/17/2012)
Shrinking the DB is not only not the solution, it'll potentially make things worse

Please read through this - Managing Transaction Logs and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/


On this particular database I personally would shrink it because it doesn't appear to be setup corectly, but on the whole your advice is spot on
Post #1346443
Posted Friday, August 17, 2012 4:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 42,826, Visits: 35,956
SQLDBA360 (8/17/2012)
On this particular database I personally would shrink it because it doesn't appear to be setup corectly, but on the whole your advice is spot on


You'd shrink without knowing why the log is full, what's preventing it from being reused?

The first thing to do with a full log is figure out why it's not being reused, not knee-jerk shrink. Shrink of the log may be required later, after resolvign whatever's keeping the log from being reused (which may well not be recovery model)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1346453
Posted Friday, August 17, 2012 4:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 42,826, Visits: 35,956
Just as a note to a 'not a DBA', switching to simple recovery is not typically something you'd do to an important DB as that breaks the log chain and prevents point-in-time restores after the switch (as covered in the first article I referenced)
ReportServerTempDB should be in simple recovery anyway unless someone switched it into full recovery for some reason



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1346454
Posted Friday, August 17, 2012 4:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:09 PM
Points: 15,662, Visits: 28,053
Doubly important not to switch to simple recovery because you're already potentially in a position where you may have an issue and might need to recover the database to a point in time.

Frequently, the fast & easy solutions are the most dangerous.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1346468
Posted Friday, August 17, 2012 5:12 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:19 AM
Points: 728, Visits: 774
Correct,
but this is the reportservertempdb why would you need to recover it, just re-run your report?
Post #1346473
Posted Friday, August 17, 2012 5:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:17 AM
Points: 868, Visits: 1,131

Another one here for "why's it not being reused?"

Q: Has the drive always been this full?

I'd shy away from changing the recovery model if it is in fact not in simple mode. Could be all manner of reasons for this (good and bad). DBA returns from holiday to find his recovery models have changed...thoughts?

Post #1346481
Posted Friday, August 17, 2012 12:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:54 PM
Points: 162, Visits: 168
Agreed with GilaMonster, blindly shrinking a database might not be the best way of reclaiming space.

For this particular issue. If you are in a rush to get the server back, you could detach this ReportServerTempDB, move it to another location, attach it back then do your investigation or wait until your DBA comes back.

Also, the default recovery model of this database is SIMPLE, no need to change it unless it has been changed by your DBA. In this case, you might want to check with him/her.


----------------------------------------------------------------------------------------------
Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
Email: Louis.Li@rrlminc.com | Blog | LinkedIn
Post #1346705
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse