This is example of long running queries of database within email alert, we already had example how you can use sp_send_dbmail stored procedure from msdb database to send backup size email daily, and how to send missing indexes report via email. This is just another use case of sp_send_dbmail stored procedure. I used following arguments to achieve this @profile_name (name of an existing Database Mail profile, type sysname, default NULL), If you are not sure about your profile account use the following script, it shows all the accounts for all profiles in the instance. EXECUTE...

Several time, using different SQL Server versions I end up having a problem with initializing secondary database using SSMS transaction log shipping GUI (mainly with large databases). I got errors similar to: System.Data.SqlClient.SqlError: Cannot open backup device ‘\\network_path\trnls\db01.bak’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)     Indicating that you don not have enough permissions.   Even after I confirmed all permissions are OK, and I already have another DB using same network location successfully, still end up with same error....

Most of the time it is very easy to remove secondary server from your log shipping configuration. But what happens if secondary server is not available.   We had this case on one of our testing environment, we had secondary server on the cloud and after a month server lease expired. When we tried to remove that server from our log shipping configuration we end up with following errors.   Error details   As you can see from error details SQL Server Management Studio could not delete the Secondary server ‘SQLNODE2’. (Microsoft SQL Server Management Studio) Object reference not...

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,...

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...

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',...

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...

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...

“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...

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,...