How to know the current settings for a server?

Question

Post reply

How to know the current settings for a server?

nagkarjun1

Ten Centuries

Points: 1020
More actions
September 13, 2012 at 11:36 am

#261442

How to know the current settings for a server for the following:
Remove Builtin\Administrators from the SQL Server Logins
Latest Service Pack for SQL Server
Number of tempdb files
Max Server Memory Value
Rights to Perform Volume Maintenance Task for SQL Service Account
Database Files Location
Enable only required Network Protocols
Review Members of System Adminstrators Role in SQL Server
NETBIOS and Server Message Block should be Disabled
Use Windows Only Authentication Mode
Enable Lock Pages in Memory Rights
Page file Size should be 1.5 to 2 times the actual RAM
SQL Service Account
SA account enabled

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

here's the obvious answers.

Remove Builtin\Administrators from the SQL Server Logins

Latest Service Pack for SQL Server

Number of tempdb files

Max Server Memory Value

Rights to Perform Volume Maintenance Task for SQL Service Account

Database Files Location

Enable only required Network Protocols

Review Members of System Adminstrators Role in SQL Server

NETBIOS and Server Message Block should be Disabled

Use Windows Only Authentication Mode

Enable Lock Pages in Memory Rights

Page file Size should be 1.5 to 2 times the actual RAM

SQL Service Account

SA account enabled

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Charles Hearn SSCrazy Points: 2680 More actions · Answer 2

nagkarjun1 (9/13/2012)
How to know the current settings for a server for the following:
Page file Size should be 1.5 to 2 times the actual RAM

Some of these are certainly debatable. I have 128GBs of RAM on my server. Do I need a 256GB page file?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

nagkarjun1 (9/13/2012)
Enable Lock Pages in Memory Rights

That's debatable whether you want it or not

Page file Size should be 1.5 to 2 times the actual RAM

Err, why? How often do you want the SQL Server service paging its entire working set to disk? (Hint, never)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Show SSCertifiable Points: 6143 More actions · Answer 4

I guess nakarjun ask the way to retreive server configurations.If so, sys.configurations can be used for server level settings.