0

I encountered SQL Database log file growing rapidly and filled up the Hard Disk space, the growth was approximately 190MB per day.

there is Sharepoint running in the server, how can I check what causes the log files size increase? could it because of the scheduled sharepoint crawl and DB backup?

I intend to shrink some of the log files (e.g: Report Server log file), how can I determine these log files will not having any side effect after shrink?

EEAA
  • 109,363
  • 18
  • 175
  • 245
sams5817
  • 167
  • 6

1 Answers1

1

Can you share the results of these below? Are you taking regular transaction log backups?

select name, log_reuse_wait_desc from sys.databases

DBCC OPENTRAN()

You need to properly size the transaction logs so that auto grow won't be kicked in and frequently shrinking them is NOT a good idea.

EDIT: Code based on your comments to check which log files grew recently.

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

--Check if the data and log files auto-growed. Look for tempdb, log files etc.
SELECT 
    gt.ServerName
    , gt.DatabaseName
    , gt.TextData
    , gt.StartTime
    , gt.Success
    , gt.HostName
    , gt.NTUserName
    , gt.NTDomainName
    , gt.ApplicationName
    , gt.LoginName
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in ( 92, 93 ) --'Data File Auto Grow', 'Log File Auto Grow'
ORDER BY StartTime;
Sankar Reddy
  • 1,374
  • 8
  • 8
  • the log file I try to shrink is showing NOTHING in log_reuse_wait_desc column. by the way, I tried shrink the log file and it do not decrease any file size, can truncate help? thanks – sams5817 Apr 11 '11 at 03:16
  • 1
    Why do you think there is need to shrink the log file? What is the recovery model of the database? Are you taking regular transaction log backups if you are in full recovery model? -- Individual File Sizes and space available for current database SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB], size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id] FROM sys.database_files OPTION (RECOMPILE); – Sankar Reddy Apr 11 '11 at 03:21
  • NOTHING for log_reuse_Wait_desc is good and why do you think there is need to shrink the log files? Can you share the results of the above file space query? And using TRUNCATE is NOT a good thing and it has adverse side effects. In fact it is removed in 2008. – Sankar Reddy Apr 11 '11 at 03:23
  • if not use shrink, what is the best practice to reduce log file size? the recovery model is now Simple and before that is Full, Yes the transaction log is backup regulary. – sams5817 Apr 11 '11 at 03:42
  • 1
    Transaction log backups makes sense in FULL recovery model only. If you are in simple recovery then the Transaction log clears up after the transaction completes. The best practice is to keep the transaction log big enough so that it won't autogrow for the biggest transaction in the database. If the log has grown to a extent that you are running out of space then onetime shrinking teh file is ok but using SHRINK as a regular maintenance is NOT a good practice. – Sankar Reddy Apr 11 '11 at 03:46
  • I understand that using SHRINK regularly is not a good practice, however the hard disk is running out of space, I would like to seek for advises which database log file can be shrink and which is highly important cannot shrink – sams5817 Apr 11 '11 at 03:55
  • In this case you may want to use DBCC SHRINKFILE (logFileName, TargetSizeinMB). Which ones to do is upto you and you need to figure that out which log file grew recently. how do you check that is using the default trace. Let me send the code. – Sankar Reddy Apr 11 '11 at 04:02
  • thank you for your code, if I wanted to move some of the log file to other Drive, what are the steps I need to perform? – sams5817 Apr 11 '11 at 04:13
  • This link shows how to do this with examples. http://msdn.microsoft.com/en-us/library/ms345483.aspx – Sankar Reddy Apr 11 '11 at 04:15