SQL Server for SCCM 2012 R2 RRS feed

  • Question

  • Hello,

              I'm very new to SQL concepts, I have recently installed a backend database for SCCM application with below configuration.

    Installed the SQL on VM with 10GB RAM, 50GB(OS Partition), 400GB (SCCM DATA & SQL DATA) and 50GB (SQL Logs)

    OS: WIn Server 2012 R2

    SQL : SQL Server 2012 SP1 Std

    Question: I want to know,how to configure the following things,

    01. Limit memory utilization of SQL (Min & Max)

    02. limit the maximum size of the log files

    03. Limit Disk utilization for SQL etc...

    Friday, April 4, 2014 5:55 PM


  • Hi Julie,

        My friend Jorge Segarra has a blog about min and max memory that I'd recommend you look at.  http://www.sqlchicken.com/2012/02/setting-memory-limits/  

    the best answer is "it depends", you want to define an amount other than the default.  by default SQL Server installs with 2 TB of memory enabled regardless of what is on the source system.

    With SCCM and 500 GB data files, you will probably find you have memory pressure with only 10 GB.  I would leave a minimum of 1 GB per for every 4 dedicated to the OS, up to 4 GB for the OS.  So in your case, 8 GB for SQL 2 GB for the OS.  If you can add more for the VM that would be a good idea.  Typically you would want 3 x your largest returned dataset in a query.  When you do a checkdb or index rebuilds your memory with get thrashed.

    Log files, make sure your database is in simple recovery model.  Understand the type of backups you need to maintain transnational consistency.  Grow them at a minimum of 4 GB increments.  Transaction logs are made up of VLF's and VLF fragmenation will cause slower writes on write heavy systems like SCCM.  Here's a great blog for more on VLF's http://blogs.msdn.com/b/blogdoezequiel/

    Disk limitation should be based on size.  I like 100 GB of free space or 20%, whichever comes first.  This will be determined by the amount of history you want to maintain though.

    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    • Edited by Bradley Ball Friday, April 4, 2014 7:02 PM
    • Marked as answer by Juliesmiley Saturday, April 5, 2014 7:28 AM
    Friday, April 4, 2014 6:04 PM