Month: April 2020

No comments

SQL SERVER – Memory Settings Formula (Best Practice)

  By ,

Here are the formulas for setting memory in SQL Server.

  • Single-instance dedicated Max server memory formula: physical memory – 4G (or 10% whichever is greater to leave some GBs for the OS).
  • Single-instance shared Max server memory formula: physical memory – 4G ( or 10% whichever is greater to leave some GBs for the OS ) – (memory for other services).

Use the sys.configurations view to query server configuration options including Max server memory(MB) and Min server memory (MB).

SELECT
	SERVERPROPERTY('InstanceName') AS myinstance,
	*
FROM	
	sys.configurations
WHERE name IN
	(
		'max server memory (MB)',
		'min server memory (MB)'
	)
GO

Use the sys.dm_os_sys_info view to query the total amount of physical memory in the server, number of schedulers, last server startup time, and if SQL Server runs in a virtual or physical environment

SELECT physical_memory_kb / 1024 AS Physical_Memory_MB
FROM sys.dm_os_sys_info

Use the sp_configure system stored procedure to set server configuration options to prevent potential UI data entry errors.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory (MB)', <Value>
GO
RECONFIGURE
GO

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – Database Instant File Initialization

  By ,

Instant file initialization is a setting that can make a positive impact at instance level on SQL Server I/O performance.

All I/O operations are done via the OS and the storage layer. For example:

  • Claiming disk space for creating a new database file
  • Autogrowing a database file
  • Restoring a database from backup

Whenever SQL Server needs to allocate space for these operations, it will first fill the space it needs with zeros. This zeroing phase, or zero initialization, can take a significant amount of time, depending on storage performance and size of the disk space to zeroed. Therefore, it can adversely impact file creation, autogrow, and database restore performance.

How to Turn-On Instant File Initialization

SQL Server doesn’t have a setting or checkbox to enable Instant File Initialization.

Instead, it detects whether or not the service account it’s running under has the Perform Volume Maintenance Tasks permission in the Windows Security Policy. You can find and edit this policy by running secpol.msc (Local Security Policy) in Windows.

  1. Expand the Local Policies Folder.
  2. Click on User Rights Assignment.
  3. Go down to the “Perform Volume Maintenance Tasks” option and double click it.
  4. Add your SQL Server Service account and click OK out of the dialog.

After granting this policy to the service account, you’ll need to restart the SQL Server service in order for the policy to take effect and for SQL Server to start using Initial File Initialization.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
One Comment

SQL SERVER – TSQL Script to Change the Compatibility Level of all Databases

  By ,

I’ve just migrated 8 of my demo databases from SQL 2014 to SQL 2017, and I want to change the compatibility level (from 120 to 140) of all those databases via TSQL. Here is a script to do just that.

BEGIN
	SET NOCOUNT ON;

	DECLARE @rc INT = 0, @i INT = 1, @v_cmd NVARCHAR(MAX);

	DECLARE @cmds TABLE(RowNum INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Cmd NVARCHAR(MAX) NOT NULL);

	INSERT INTO @cmds
	SELECT 'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 140' FROM sys.databases 
	WHERE compatibility_level = 120

	SET @rc = @@ROWCOUNT;
	RAISERROR('The COMPATIBILITY_LEVEL of %d databases will be changed to 140', 10, 1, @rc) WITH NOWAIT;

	WHILE @i <= @rc
	BEGIN
		SELECT @v_cmd = cmd FROM @cmds WHERE RowNum = @i;

		PRINT @v_cmd;
		EXEC(@v_cmd);

		SET @i += 1;
	END;
END

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – How to Fix a Database in Recovery Pending State

  By , ,

A SQL database will be marked with different states if one or more of its core files are in a inconsistent state. The type of state will depend on how serious the damage is. Here are some of the states:

  • Online – occurs when one of the data files is damaged during an execution of a query or some other operation.
  • Suspect – occurs when a database cannot be recovered during startup
  • Recovery Pending – occurs when SQL Server knows that recovery of the database is to be done, but something is obstructing before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, because it has not started yet
Database marked as ‘Recovery Pending’

Here a script to check the current state of all your databases in a SQL instance. 

SELECT name, state_desc from sys.databases
GO

There are several reasons why a database is marked as ‘Recovery Pending‘

  • The database was not cleanly shutdown. There could be one or more transactions active at that time, resulting in the deletion of active transaction log file.
  • To overcome server performance issues, A user could have tried moving the log files to a new drive, but in the process, corrupted the files instead.
  • Insufficient memory or disk space that prevented database recovery from getting started.

Let’s Fix It

ALTER DATABASE TestDB SET EMERGENCY;
GO
ALTER DATABASE TestDB set single_user
GO

DBCC CHECKDB (OptimalTestDb, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE OptimalTestDb set multi_user
GO

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter