August 17, 2012 at 3:29 am
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,
August 17, 2012 at 3:49 am
Shrinking the DB is not only not the solution, it'll potentially make things worse
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2012 at 3:58 am
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
August 17, 2012 at 4:01 am
GilaMonster (8/17/2012)
Shrinking the DB is not only not the solution, it'll potentially make things worsePlease read through this - Managing Transaction Logs[/url] 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
August 17, 2012 at 4:16 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2012 at 4:19 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2012 at 4:43 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 17, 2012 at 5:12 am
Correct,
but this is the reportservertempdb why would you need to recover it, just re-run your report?
August 17, 2012 at 5:28 am
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?
😉
August 17, 2012 at 12:26 pm
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.
August 17, 2012 at 12:43 pm
mr.neil.bryan (8/17/2012)
Would shrinking this database file resolve our issue?
Possibly.
Since it is the Reporting temp db, if you have a lot of free space in it, I would go ahead and try shrink it. First, run:
DBCC SQLPERF(LOGSPACE)
If the Reporting temp db has lots of freespace, attempt to shrink it as someone posted before:
USE ReportServerTempdb
DBCC SHRINKFILE (2, 2000)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 17, 2012 at 1:14 pm
The problem with shrinking any tempdb file is that it probably is the size it is for a reason. There is a good chance once you have shrunk it that some process is going to come along, need the extra space and grow it right back again.
That being said (and agreeing with the people who say this is a really bad idea). If you can't possibly get in touch with your DBA and can't possibly wait until someone more experienced with databases/your DBA is able to look at the situation, then at least in my opinion shrinking tempdb down a little bit, but NOT all the way (maybe to 350gb) is probably one of the less bad things you could do.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 17, 2012 at 1:26 pm
Since their current log drive is full, they are in a tough situation.
Without a DBA there, to me the safest thing to shrink is a temp db.
It could try to grow again, but to prevent that I'd do either/both:
(1) set a max size the log file on ReportTempdb can allocate
(2) add a second log to ReportTempdb on another drive, also with a max size setting
If both of those files fill up, then you may have to recycle RS or even stop it for a while.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 18, 2012 at 12:41 pm
backup the log file
August 20, 2012 at 6:09 am
kilpasis (8/18/2012)
backup the log file
Making sure all of the databases on the drive have log backups is certainly something the op should check if there is unexpected growth. But taking a log backup isn't actually going to gain space in and of itself. If there is a database that has not had a log backup in a while you could backup it up and then shrink down the log itself. I have seen cases where a database of 500 mb had a log of over 200 gb because someone forgot to set up log backups.
Here is a query you can run on MSDB to tell you when the last full, differential and log backups have occurred.
SELECT backupset.database_name, DATABASEPROPERTYEX(backupset.database_name,'RECOVERY') AS RecoveryModel,
MAX(CASE WHEN type = 'D' THEN backupset.backup_start_date ELSE NULL END) AS LastFull,
MAX(CASE WHEN type = 'I' THEN backupset.backup_start_date ELSE NULL END) AS LastDifferential,
MAX(CASE WHEN type = 'L' THEN backupset.backup_start_date ELSE NULL END) AS LastLog
FROM msdb.dbo.backupset backupset
GROUP BY backupset.database_name
ORDER BY 5
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply