Today I have needed to know what version of sql server 2012 and service pack my costumer is using in differents Sharepoint 2013 farms. As you know, when you check the help in SQL Management Studio you only see the product code.
With this link you can verify what means every code.
Here I put a script that tune your sql server with the best practice of Microsoft. I used to tune all my sql servers in all instances. Execute it on every instance of your sql servers and all is done.
CREATE TABLE #numprocs
DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @CORES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT
INSERT INTO #numprocs
SELECT @CORES = Internal_Value FROM #numprocs WHERE [Index] = 16
SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N’tempdb.mdf’, LOWER(physical_name)) – 1) DataFileLocation
WHERE database_id = 2 and FILE_ID = 1)
SET @FILECOUNT = (SELECT COUNT(*)
WHERE database_id = 2 AND TYPE_DESC =’ROWS’)
SELECT @SIZE = size FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
SET @SIZE = @SIZE / 128
SELECT @GROWTH = growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
SELECT @ISPERCENT = is_percent_growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
WHILE @CORES > @FILECOUNT
SET @SQL_SCRIPT = ‘ALTER DATABASE tempdb
FILENAME = “‘+ @BASEPATH + ‘tempdb’ + RTRIM(CAST(@CORES as CHAR)) + ‘.ndf”,
NAME = tempdev’ + RTRIM(CAST(@CORES as CHAR)) + ‘,
SIZE = ‘ + RTRIM(CAST(@SIZE as CHAR)) + ‘MB,
FILEGROWTH = ‘ + RTRIM(CAST(@GROWTH as CHAR)) +”
IF @ISPERCENT > 0
SET @SQL_SCRIPT = @SQL_SCRIPT + ‘%’
SET @SQL_SCRIPT = @SQL_SCRIPT + ‘)’
SET @CORES = @CORES – 1
DROP TABLE #numprocs
If you have problems with the space of the transactionlog, you can reduce it doing a trunk with the following query:
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(“TransactionLogName”, 1)
ALTER DATABASE [DatabaseName] SET RECOVERY FULL WITH NO_WAIT
Historically, System Center products have been tested and supported with only the SQL_Latin1_General_CP1_CI_AS collation. It is the default collation when you install SQL Server on an EN-US Windows Server. When you install SQL on all other languages of the Windows OS the collation default will be something different, typically a Windows collation. The SQL Server collations are no longer being updated and will eventually be phased out in favor of the Windows collations. Therefore, the SQL_Latin1_General_CP1_CI_AS collation doesn’t have some of the latest Unicode characters in its codepage which can cause some problems for those languages which have recently added characters to Unicode. Historically this has been a minimal to non-existent issue. With the introduction of Service Manager to the System Center suite we had our first component that has (a) a full-text searchable knowledge base and (b) a user base that included not just IT people but really the entire organization. The result is that data is stored in newer Unicode characters more often. We started hitting search and sort issues with customers that were using the SQL_Latin1_General_CP1_CI_AS collation during the beta testing of Service Manager 2012 because they were storing some of the newer Unicode characters in the database that weren’t in the SQL_Latin1_General_CP1_CI_AS collation codepage.
The SQL Server team advised us that we should start switching to the Windows collations to minimize these issues from happening. That is why when you are installing SCSM on a SQL Server that is using the SQL_Latin1_General_CP1_CI_AS collation you will see this warning message:
You can bypass this warning message and continue to install using the SQL_Latin1_General_CP1_CI_AS. Further, the SQL_Latin1_General_CP1_CI_AS collation was not listed as a supported collation on the Language Support for System Center 2012 – Service Manager page on the TechNet Library. The required collation for SCSM per the documentation currently is Latin1_General_100_CI_AS (note the 100).
Because all other System Center components (except SCOM which can also be installed on a few other additional collations – more on that in a minute) are required to be installed on SQL_Latin1_General_CP1_CI_AS, most people thought they would need a dedicated SQL Server instance for SCSM. We would generally recommend having a separate instance of SQL Server for running SCSM for performance and scalability reasons, but understand that there are probably plenty of cases where it makes sense to collocate SCSM and other System Center components on a single SQL Server instance.