Home / server / Questions / 665849
Accepted
Groo
Asked: 2015-02-08 12:09:05 +0800 CST

How to update system databases in SQL Server

  • 772

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?

database

1 Answers

  1. Best Answer

    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
    • 2