Friday, October 23, 2009

An error occurred while reading the app.config file

I edited my app.config file manually, which did not seem to agree with VS. Even though the XML was valid, I received the message: An error occurred while reading the app.config file. The file might be corrupted or contain invalid XML. Despite this message the code will still run, but its just annoying.

This is a known bug, which I managed to get rid off by removing the project from the solution, restarting Visual Studio and adding the project again.

Thursday, October 22, 2009

SQL Server: Automated log file maintenance

Ok you've all seen it before. You remote desktop onto a client's sql server to upload your app, when your upload fails due to a lack of disk space. Darnit! So you have to reduce the LDF's, which has grown to be several GBs large.

The following little script will reduce all the non-system db's log files. One can also schedule this script as a job that automatically reduces the log files. (Disclaimer: Read-up on the function log files fulfil before simply using this script. I would not recommend running this on critical production systems.)

-- This gets us all on the same page
use [master]

-- Define some variables
declare @statement varchar(2000)
declare @dbname sysname
declare dbname_cursor cursor for

-- Select all non-system db's
select [name] from master.dbo.sysdatabases where
[name] <> 'master' and
[name] <> 'model' and
[name] <> 'msdb' and
[name] <> 'tempdb'

-- Iterate through each db record
open dbname_cursor
fetch next from dbname_cursor into @dbname
while @@fetch_status <> -1
select @statement = ''
select @statement = @statement + 'use ' + @dbname + '; '

-- Backup db by runcating only (will not reduce physical file size)
select @statement = @statement + 'backup log ' + @dbname + ' with truncate_only; '

-- Get the db log file name
select @statement = @statement + 'declare @log_file varchar(2000); '
select @statement = @statement + 'select @log_file = [name] from sys.database_files where type_desc = ''LOG''; '

-- Reduce the physical file size
select @statement = @statement + 'exec(''dbcc shrinkfile ('' + @log_file + '', 0)''); '
fetch next from dbname_cursor into @dbname
close dbname_cursor
deallocate dbname_cursor