Currently Browsing: SQL Tips and Tricks

SQL Server / Missing Indexes Query / Cached Plans / Email Alert

Posted by in SQL Server, SQL Tips and Tricks on Feb 15th, 2018 | 1 comment
SQL Server / Missing Indexes Query / Cached Plans / Email Alert
This is example of missing indexes of database within email alert, we already had example how you can use sp_send_dbmail stored procedures from msdb database to send backup size email daily, and this example is just to show use case with different scripts. I used following arguments to achieve this @profile_name (name of an existing Database Mail profile, type sysname, default NULL), @recipients (Is a semicolon-delimited list of e-mail addresses to send the message to, varchar(max)), @body (Is the body of the e-mail message, nvarchar(max)), @subject (Is the subject of the e-mail message,...
read more

SQL Server / The target principal name is incorrect. Cannot generate SSPI context

Posted by in SQL Server, SQL Tips and Tricks on Jan 12th, 2018 | 5 comments
SQL Server / The target principal name is incorrect. Cannot generate SSPI context
One of our old SQL servers was running under the local system context. Then we decided to change the account that the SQL service runs under, and we created domain service account with basic domain user permissions. Eventually, we end up with following error trying to access our SQL Server remotely.   SQL Server SPN Creation To run SQL Server service you can use Local System account, local user account or a domain user account. If you are using Local System account to run your SQL Service the SPN will be automatically registered.  Nevertheless, if you are using domain account to run SQL Server...
read more

SQL Server / Instant File Initialization

Posted by in SQL Server, SQL Tips and Tricks on Dec 11th, 2017 | 0 comments
SQL Server / Instant File Initialization
Instant file initialization reclaims used disk space without filling that space with zeros. What those this means for our SQL Server data files? Data file grow needs to be completed immediately.   Lets do a simple test. I will create additional tempdb files with fixed 10GB size without Instant file initialization enabled for the SQL Server service account. /* Adding additional tempdb files */ USE [master]; GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'D:\MSSQL\TempDB\tempdev2.ndf' , SIZE = 10GB , FILEGROWTH = 0); ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3',...
read more

SQL Server / 64KB Allocation Unit Size

Posted by in SQL Server, SQL Tips and Tricks on Dec 8th, 2017 | 12 comments
SQL Server / 64KB Allocation Unit Size
MS SQL Server Best Practices Article suggests as a SQL Server configuration best practice is to format your data, logs, and tempdb file drives with a 64KB allocation unit size. NTFS Allocation Unit Size When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb. Be aware however, that using allocation unit sizes greater than 4 KB results in the inability to use NTFS compression on the volume. SQL Server, although it is not recommended that you use this, does support read-only data on compressed...
read more

SQL Server / Extended Events / Finding Deadlocks

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Nov 20th, 2017 | 0 comments
SQL Server / Extended Events / Finding Deadlocks
Recently we experienced deadlocks on one of our staging servers, so I wanted to capture those deadlocks using Extended Events. This how I did it. First of all you need location where you gonna keep your XEL file, then we can create Extended Event session Using SSMS Creating new Extended Event session   Selecting Events you want to capture, in this case xml_deadlock_report   Save the event data to an XEL file   Using T-SQL CREATE EVENT SESSION [Deadlock_Report] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET...
read more

Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)

Posted by in SQL Server, SQL Tips and Tricks on Oct 11th, 2017 | 2 comments
Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)
“Operating system error 32 (The process cannot access the file because it is being used by another process)” message in SQL Server error log BackupDiskFile::OpenMedia: Backup device ‘\\NAS\Shared_LogShip\DB_log.trn’ failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.). If you are using shared NAS for your logs backup location for the primary server and you restoring those logs directly to the secondary server (without copy job) you can face this kind of issues.  If backup and restore job are scheduled at the...
read more

SQL Server / Upgrading the Secondary Server Instances (STANDBY)

Posted by in SQL Server, SQL Tips and Tricks on Oct 9th, 2017 | 0 comments
SQL Server / Upgrading the Secondary Server Instances (STANDBY)
If you planning to upgrade to the new SQL Server Version 2016 or 2017 and if there is log shipping in place between those servers you should follow some upgrading order. Always upgrade the secondary SQL Server instances first, before upgrading the primary server instance. If you do the opposite log shipping will fail because a log backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server. But this is not a reason why I am writing this post. I am writing to alert you that in case you have a secondary database configured by using RESTORE WITH STANDBY,...
read more

Next Entries »