Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 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; 
share|improve this answer
    
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 3: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 3: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 3: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 3: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 3:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.