Here’s a quick little statement you can use to verify the size and growth settings for a SQL Server database. It doesn’t spell everything out for you, but if you’re aware of some common conventions (-1 = unlimited, 0 = no growth), it’s a fast and easy way to verify settings before issuing your ALTER DATABASE command. The filename is commented out by default, but provided just-in-case.
select db_name() [Database] , name [Logical Name] , ceiling(size/128.0) [Size (MB)] , case maxsize when -1 then maxsize else ceiling(maxsize/128.0) end [MaxSize (MB)] , case when (status & 0x100000) = 0 then ceiling(growth/128.0) else growth end [Growth] , case when (status & 0x100000) = 0 then 'MB' else '%' end [% or MB] --, filename from sysfiles
Some example output:
Database Logical Name Size (MB) MaxSize (MB) Growth % or MB ReportServer ReportServer 18 -1 1 MB ReportServer ReportServer_log 177 2097152 10 %
And an image from SSMS to verify:
- Tags: HOWTO, SQL Server, SSMS
- HOWTO: Sync Google Calendar on iPhone OS 3.0
- HOWTO: Setup SQL Server Linked Server to MySQL
- Connecting to Oracle from SQL Server
- Windows 7 in VirtualBox: Shared Folders workaround
- Fixing "passwd: Authentication token manipulation error" when changing passwords
- Installing Windows 7 Beta 1 in VirtualBox 2.1.0
- HOWTO: Synchronize Dropbox and ZumoDrive on Windows
- Updating SSH known hosts fingerprints (WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!)
- HOWTO: Connect to MySQL in SSIS
- Efficiently query the DATE in DATETIME
- Stop SOPA
- Access system date in SSIS without a Script Task
- Connecting to Oracle with SSIS 2008
- Set Permissions Across SQL Server Database Instances
- SQL Server Principals & Roles Permission Audit
- New Office Outlook 2010 Data File Menu Item
- Why RAM Matters Less with OS X Lion
- Cache multiple values in a Map Container for fast in-memory lookup
- Ubuntu VirtualBox Server Redux Addendum
- Default column value to identity of different column
- KALImaR:Im having the same 11.10 problem with this irritating disaster...Solutions please [...]
- Balkan:Hi All, I have similar problem as Dwarakesh, I set the automatic login on, and after that my passwo [...]
- Creating an automated database backup by means of SQL Server Agent Job « Placko's SQL KB:[...] Configuring SQL Server Agent Operators by Taylor Gerring [...] [...]
- Dwarakesh:yeah even I'm getting the same error on my sony e series laptop!!!! I am currently running ubuntu 1 [...]
- Phil:Thanks - this article was a great help. [...]
Comment Form