0

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)?

4 Answers 4

3

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.

1
  • 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 Feb 25, 2011 at 11:49
0

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

1
  • 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 Feb 24, 2011 at 11:31
0

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
1
  • 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. Feb 24, 2011 at 23:36
-1

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

1
  • as i said, i have simple logging enabled Feb 24, 2011 at 11:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .