How to Manage SQL Server Database Files

• Log files
• Query log space usage for every DB on the server: DBCC SQLPERF(‘logspace’)
• Add a log file to the DB: ALTER DATABASE dbname ADD LOG FILE(NAME = logicalname, FILENAME = ‘filepath’, SIZE = size, MAXSIZE = maxsize, FILEGROWTH = filegrowth)
• Modify auto growth: ALTER DATABASE dbname FILE (NAME = logicalname, AUTOGROW = size)
• Remove MAXSIZE limit: ALTER DATABASE dbname (NAME = logicalname, MAXSIZE = UNLIMITED)
• Prevent autogrow: ALTER DATABASE dbname (NAME = logicalname, AUTOGROW = 0)
• Data files
• the command is essentially the same with some new options (e.g. “TO FILEGROUP”).