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

We have a lot of SQL servers on development environment where we never take backup of the databases (TFS for code is enough).

The (SharePoint) databases are all set to simple recovery model, but the log files, especially for the SharePoint configuration database is growing quite large and filling up our data drive on the SQL server.

Since these log files are never used for anything, i would like advice on how to best minimize the size of these log files -or even disable them if possible.

I'm not completely sure why the log files grow so large even on simple logging (checked for long running transactions (DBCC OPENTRAN) but found none).

I guess the reason for the log files not being truncated is, that we don't take any backups, and hence Checkpoints aren't reached.

The autogrowth for log files are set to autogrow by 10% restricted to 2 gb, so i guess that is why Checkpoint (70%) aren't reached here either.

What would be the be best strategy to keep log files small (best case 0) without sacrificing performance (eg VLF fragmentation)?

share|improve this question

4 Answers 4

up vote 3 down vote accepted

Are you running any maintenance jobs that could be doing any reindexing or other heavy work? Even with simple recovery, the log will need to be at least large enough to hold the largest transaction issued against the database. Try running DBCC SQLPERF(LOGSPACE) and check how much of the log file is actually in use. If it's very low, then simple recovery is likely working as intended, and the log file is probably only growing because it needs to be that large to support some larger-than-average transaction.

share|improve this answer
    
I just shrank all log files yesterday, and they are still nice and small. When running DBCC SQLPERF i found an avg of 59% usage with MIN being 21% and MAX being 91%. It has taken the log files 4 month of slow growing to reach almost 1 GB each for the SharePoint configuration databases, which is 8x the size of the database itself –  Anders Rask Feb 25 '11 at 11:49

Start with running the following query to see the reason that the log is not truncated:

select log_reuse_wait from sys.databases where name = '<database name>'

Description on the reasons on log reuse can be found here: http://msdn.microsoft.com/en-us/library/ms178534.aspx

share|improve this answer
    
should probably have mentioned that i already did this (i used log_reuse_wait_desc though). It say NOTHING. How ever i think i have a pretty good idea why Checkpoints arent reached (as mentioned), i am more interested in how to get a setting where i keep transaction log size at minimum without introducing other problems –  Anders Rask Feb 24 '11 at 11:31

Try running this query. This was put together by MS (I don't have the article handy) and was originally intended for Sharepoint running on SBS2008 but should work for any instance of Sharepoint. Make sure to verify that the DB names and paths match your instance.

declare @ConfigDB varchar(255);
declare @ConfigDBLog varchar(255);
declare @ConfigDBCmd varchar(255);
select @ConfigDB =  name from sys.databases where name like 'SharePoint_Config_%';
set @ConfigDBCmd = 'BACKUP database [' + RTRIM(@ConfigDB) + '] to disk=''C:\windows\temp\before.bkf''';
execute(@ConfigDBCmd);
set @ConfigDBCmd = 'use [' + RTRIM(@COnfigDB) + ']';
execute(@ConfigDBCmd);
set @ConfigDBCmd = 'BACKUP LOG [' + RTRIM(@ConfigDB) + '] WITH TRUNCATE_ONLY';
execute(@ConfigDBCmd);
set @ConfigDBCmd = 'use [' + RTRIM(@COnfigDB) + ']';
execute(@ConfigDBCmd);
select @ConfigDBLog =  name from sys.database_files where name like 'SharePoint_Config%_log';
set @ConfigDBCmd = 'use [' +  RTRIM(@ConfigDB) + '] DBCC SHRINKFILE([' + RTRIM(@ConfigDB) + '_log],1)';
execute(@ConfigDBCmd);
set @ConfigDBCmd = 'BACKUP database [' + RTRIM(@ConfigDB) + '] to disk=''C:\windows\temp\after.bkf''';
execute(@ConfigDBCmd);
go
share|improve this answer
    
i already have a script for shrinking logfiles, this is what i do right now. But what i really want is guidance on setting proper logfile size (eg autogrow) for a development environment where transaction log files are useless and where i dont take backups. –  Anders Rask Feb 24 '11 at 23:36

Double check you have SIMPLE recovery model on your development databases.

select name, recovery_model_desc from sys.databases

You need a log file: it can't be disabled or set to zero

share|improve this answer
    
as i said, i have simple logging enabled –  Anders Rask Feb 24 '11 at 11:25

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.