Category: SQL SERVER

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
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
No comments

SQL SERVER – Resolving database in SUSPECT mode

  By , ,

SQL Server runs in different modes, and can be in a specific state at a given time.

The modes are:

  • ONLINE
  • OFFLINE
  • RESTORING
  • RECOVERING
  • RECOVERY PENDING
  • SUSPECT
  • EMERGENCY

In this blog post, we will talk about how to deal with a database that is in SUSPECT mode.

Here are some of the reasons why SQL Server marks a Database as Suspect:

  • Database file corruption
  • Unavailable database files
  • Improper shutdown of SQL Server database Server
  • Failure to open the device where the data or the log file resides
  • SQL Server crash

How to Recover SQL Server from SUSPECT Mode

1.Turn off the suspect flag on the database and set it to EMERGENCY

EXEC sp_resetstatus 'YourDBName'; 
ALTER DATABASE YourDBName SET EMERGENCY

2. Perform a consistency check

DBCC CHECKDB YourDBName

3. Bring the database into the Single User mode and roll back the previous transactions

ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Take a complete backup of the database

5. Attempt the Database Repair allowing some data loss

DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS)

6. Bring the database into the Multi-User mode

ALTER DATABASE YourDBName SET MULTI_USER

7. Refresh the database server and verify the connectivity of the database

Cheers!

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

SQL SERVER – Merge Table Partitions

  By ,

Here are the scripts that I use to merge table partitions.

First, let’s check the partition information of a particular table (Indicate the table name in the

SELECT  pf.name AS pf_name ,
            ps.name AS partition_scheme_name ,
            p.partition_number ,
            ds.name AS partition_filegroup ,
            OBJECT_NAME(si.object_id) AS object_name ,
            rv.value AS range_value ,
            SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
                     ELSE 0
                END) AS num_rows
    FROM    sys.destination_data_spaces AS dds
            JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
            JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
            JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
            LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
                                                          AND dds.destination_id = CASE pf.boundary_value_on_right
                                                                                     WHEN 0 THEN rv.boundary_id
                                                                                     ELSE rv.boundary_id + 1
                                                                                   END
            LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
            LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
                                             AND si.index_id = p.index_id
                                             AND dds.destination_id = p.partition_number
            LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
                                                           AND p.partition_id = dbps.partition_id
	WHERE pf.name = 'TableName'
    GROUP BY ds.name ,
            p.partition_number ,
            pf.name ,
            pf.type_desc ,
            pf.fanout ,
            pf.boundary_value_on_right ,
            ps.name ,
            si.object_id ,
            rv.value
	ORDER BY 
		p.partition_number
GO

Below is a screenshot of a partitioned table information in one of our environments.

Next, let’s merge the empty partitions. I will merge the empty partitions on the left side only, because I will be needing the partitions on the right side for future growth of my database

Below is the script to merge empty partitions

ALTER PARTITION FUNCTION functionName()
MERGE RANGE(boundaryPoint)


Cheers!

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

SQL SERVER – Get Table Partition Information

  By ,

Here is a script that retrieves table partition information

SELECT
	pf.name as PartitionFunction,
	ds.name AS PartitionScheme, 
	p.partition_number AS PartitionNumber, 
	CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS PartitionFunctionRange, 
	CONVERT(BIGINT, prv_left.value) AS LowerBoundaryValue, 
	CONVERT(BIGINT, prv_right.value) AS UpperBoundaryValue, 
	fg.name AS FileGroupName,
	p.[row_count] as TotalRows,
	CONVERT(DECIMAL(12,2), p.reserved_page_count*8/1024.0) as ReservedSpaceMB,
	CONVERT(DECIMAL(12,2), p.used_page_count*8/1024.0) as UsedSpaceMB
FROM
	sys.dm_db_partition_stats AS p (NOLOCK)
	INNER JOIN sys.indexes AS i (NOLOCK) ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
	INNER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = i.data_space_id
	INNER JOIN sys.partition_schemes AS ps (NOLOCK) ON ps.data_space_id = ds.data_space_id
	INNER JOIN sys.partition_functions AS pf (NOLOCK) ON pf.function_id = ps.function_id
	INNER JOIN sys.destination_data_spaces AS dds2 (NOLOCK) ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
	INNER JOIN sys.filegroups AS fg (NOLOCK) ON fg.data_space_id = dds2.data_space_id
	LEFT OUTER JOIN sys.partition_range_values AS prv_left (NOLOCK) ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1
	LEFT OUTER JOIN sys.partition_range_values AS prv_right (NOLOCK) ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE
	OBJECTPROPERTY(p.[object_id], 'IsMSShipped') = 0 AND
	-- OBJECT_NAME(p.[object_id]) = 'partitioned_table' AND
	p.index_id=1
ORDER BY p.partition_number;

Cheers!

No comments

SQL SERVER – The logical file name is already in use

  By , ,

I just encountered the error below.

Msg 1828, Level 16, State 4, Line 3
The logical file name “OTDB_FG_WRTestResults_01” is already in use. Choose a different

I was able to solve it by executing the following scripts. Essentially, you just have to rename the logical file name to different one, then rename it back to your preferred name.

ALTER DATABASE [DBName] MODIFY FILE (NAME=N'OTDB_FG_WRTestResults_01', NEWNAME=N'OTDB_FG_WRTestResults_01_Temp')

The file name ‘OTDB_FG_WRTestResults_03_Temp’ has been set.

Then change it back to the original name

ALTER DATABASE [OptimalTestDB] MODIFY FILE (NAME=N'OTDB_FG_WRTestResults_01_Temp', NEWNAME=N'OTDB_FG_WRTestResults_01')

Done.

Cheers!

No comments

SQL SERVER – Backup Database to Multiple Files (Faster, and more manageable files)

  By ,

Benefits of writing database backups to multiple files:

  • Backup process can finish faster because it will run using multiple threads
  • Better I/O throughput if you have multiple disk arrays where you can write your  backup files.

Here are two methods of writing your backup to multiple files:

Using SQL Server Management Studio 

Using T-SQL

BACKUP DATABASE [AdventureWorks2014] TO  
DISK = N'C:\SQLBackup\AdventureWorks_backupfile1.bak',  
DISK = N'C:\SQLBackup\AdventureWorks_backupfile2.bak', 
DISK = N'C:\SQLBackup\AdventureWorks_backupfile3.bak',  
DISK = N'C:\SQLBackup\AdventureWorks_backupfile4.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2014-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

No comments

SQL SERVER – Trace Flag 1117 and 1118

  By ,

Trace flag 1117 will allow us to take care of tempdb contention. It permits us to grow our data files at the same time.

We also use 1118 to prevent tempdb contention. Trace flag 1118  deals with how we allocate extents whether it be mixed extents or uniform extents

These trace flags are enabled by default in SQL Server 2016. For prior versions, you have to manually turn-on these trace flags.

Below are the ways to enable Trace Flags:

To Enable Trace Flags at Startup

Go to Configuration Manager > Select your instance > Choose Properties > Go to Startup Parameters tab.

Enable Trace Flags with DBCC TRACEON/TRACEOFF

Add the -1 to enable it globally on the instance

DBCC TRACEON( 1117, -1)

DBCC TRACEON( 1118, -1)

To see which trace are enabled:

DBCC TRACESTATUS

 

 

No comments

SQL SERVER – Setup Blocked Process Report in Extended Events

  By ,

In this blog post, I will demonstrate how to setup a Blocked Process Report using Extended Events.

1. In Management Studio under Extended Events, right-click Sessions then choose New Session Wizard.

2. Click Next.

3.  Input Session name. Click Next.

4. Choose “Do not use a template“. Click Next.

5. Search for “blocked” in the Event Library. Select blocked_process_report. Click Next.

6. Check database_name and also sql_text (optional because these values will show in the XML report). Click Next.

7. Click Close.

8. Set the blocked process threshold. In this case I have set it to 10 seconds.

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 10 ;  
GO  
RECONFIGURE ;  
GO

9. Start Extended Events.

ALTER EVENT SESSION [Blocked Process Report] 
ON SERVER STATE = START

 

Cheers!