0

I am having problems updating a system database in SQL Server 2008. Perhaps there is a different way to do it, but I basically need to change/remove growth restrictions on certain log files for a bunch of databases.

Being lazy, instead of doing this manually for each of the 100 databases using SSMS, I thought I could create a batch query.

First, this information is visible through the sys.master_files view:

select database_id, name, max_size 
from sys.master_files

And through this link ("How to update system tables in SQL 2008 R2"), I found out that the relevant database for this view is actually sys.sysbrickfiles:

Execution plan reveals actual databases

But even after connecting with sqlcmd and using sp_configure 'allow updates', 1, querying the sys.sysbrickfiles returns "Invalid object name 'sys.sysbrickfiles'".

What am I missing here?

2

In order to update sys.brickfiles manually, you're required to use the Dedicated Administrator Connection. This is available via SQLCMD; you just need to type ADMIN: before the servername.

[Insert generic warning about being careful and having backups here.]

Conversely, you could loop through a list of databases and change the autogrowth settings that way (quick scribble based on something I had lying around; you'll probably need to modify it):

declare @SQL nvarchar(max)
        @RowsToProcess int,
        @CurrentRow int = 0,
        @dbname nvarchar(80)

CREATE TABLE #dblist(RowID int not null identity(1,1), dbname nvarchar(50) )  
INSERT into #dblist (dbname) SELECT name FROM sys.databases where name not in ('master','msdb','model','tempdb')

SET @RowsToProcess=@@ROWCOUNT


WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT 
        @dbname=dbname FROM #dblist WHERE RowID=@CurrentRow
        set @SQL='ALTER DATABASE [' + @DBName + ']  MODIFY FILE (NAME=N''' + @DBName + '_Log'', FILEGROWTH=20MB);'
        print @SQL
        EXEC SP_EXECUTESQL @SQL             
END


drop table #dblist
  • 1
    Thanks! I went for the second approach, it worked without a dedicated admin connection! Typical newbie, went in a completely wrong direction. :) – Groo Feb 8 '15 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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