locked
Database Flags RRS feed

  • Question

  • I am looking to find out possible Database Flags:a few like below

    1. Auto-growth should be MB not in %
    2. Database Compatibility levels should be same
    3. Disable/Drop logins which are no longer useful
    4. Clean all the Disabled Jobs on SQL Server Agent 
    5. Backups, DBCC, Index,Stats--should have managed well with proper timings
    6. Tempdb files and ldf files on faster drives

    can any one share a few more ?

    Thursday, September 17, 2015 5:01 PM

Answers

  • a). Database Data and log file on different physical drives

    b). Set min/Max memory, enable lock pages in memory for sql service account

    c). set computer advanced properties - display - for best performance

    d). Set power management options - performance

    e). SQL Server error log - Audit success and failure logins

    f). Set number of error logs to 50

    g). create job to recycle error log daily

    h). TempDB Data files should be equal to the number of logical cores. Max 8.

    i). Set database mail to send alert on job failures.

    j). Enable Trace flag 1222 to log deadlock information in SQL Server Error Log

    K). If it is physical Server, Set BIOS Settings to run under : Performance Profile.

    l). If system supports enable turbo boost.

    M). Check if  BIOS/RAID Controller/Firmware/CPLD/Network Drives are running the latest version.

    N). Update N/W card setting to allow Jumbo frames to get max through put for ISCSI n/w.

    Regards,

    KRanp.

    • Marked as answer by coolguy123SQL Thursday, September 17, 2015 6:42 PM
    Thursday, September 17, 2015 5:19 PM
  • There is no such DB flag for what you are asking. What you are interested is best practice

    Couple of which you have suggested is not really a best practice like many applications run on different compatibility level as that of database/SQL Server

    Of course you will always delete unused login.

    Of course you will always try to put tempdb on faster drive.

    Are you documenting anything ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by coolguy123SQL Thursday, September 17, 2015 6:42 PM
    Thursday, September 17, 2015 5:26 PM
    Answerer

All replies

  • a). Database Data and log file on different physical drives

    b). Set min/Max memory, enable lock pages in memory for sql service account

    c). set computer advanced properties - display - for best performance

    d). Set power management options - performance

    e). SQL Server error log - Audit success and failure logins

    f). Set number of error logs to 50

    g). create job to recycle error log daily

    h). TempDB Data files should be equal to the number of logical cores. Max 8.

    i). Set database mail to send alert on job failures.

    j). Enable Trace flag 1222 to log deadlock information in SQL Server Error Log

    K). If it is physical Server, Set BIOS Settings to run under : Performance Profile.

    l). If system supports enable turbo boost.

    M). Check if  BIOS/RAID Controller/Firmware/CPLD/Network Drives are running the latest version.

    N). Update N/W card setting to allow Jumbo frames to get max through put for ISCSI n/w.

    Regards,

    KRanp.

    • Marked as answer by coolguy123SQL Thursday, September 17, 2015 6:42 PM
    Thursday, September 17, 2015 5:19 PM
  • There is no such DB flag for what you are asking. What you are interested is best practice

    Couple of which you have suggested is not really a best practice like many applications run on different compatibility level as that of database/SQL Server

    Of course you will always delete unused login.

    Of course you will always try to put tempdb on faster drive.

    Are you documenting anything ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by coolguy123SQL Thursday, September 17, 2015 6:42 PM
    Thursday, September 17, 2015 5:26 PM
    Answerer
  • Thanks
    Thursday, September 17, 2015 6:42 PM
  • Thanks
    Thursday, September 17, 2015 6:42 PM