SQL SERVER – Error: Msg 4305, Level 16, State 1 – The log in this backup set terminates at LSN, which is too early to apply to the database.

While working with transaction log backup restore or log shipping, you must have seen below error message.

Msg 4305, Level 16, State 1, Line 47
The log in this backup set begins at LSN 33000000048000001, which is too recent to apply to the database. An earlier log backup that includes LSN 33000000044800001 can be restored.
Msg 3013, Level 16, State 1, Line 47
RESTORE LOG is terminating abnormally.

Let’s have a look at the cause of this message. Before that let’s have our sample database ready with backups so that we can play with them.

--Create an empty database
CREATE DATABASE SQLAuthority
GO
-- use the database
USE SQLAuthority
GO
--create first test table.
CREATE TABLE MyTestTable1 (iCol INT)
-- perform full backup F1
BACKUP DATABASE SQLAuthority TO DISK = 'c:\SQLAuthority_F1.bak' WITH FORMAT
--create second test table.
CREATE TABLE MyTestTable2 (a INT)
-- perform transaction log backup T1
BACKUP LOG SQLAuthority TO DISK = 'c:\SQLAuthority_T1.trn' WITH FORMAT
--create third test table.
CREATE TABLE MyTestTable3 (a INT)
-- perform transaction log backup T2
BACKUP LOG SQLAuthority TO DISK =  'c:\SQLAuthority_T2.trn' WITH FORMAT
--create forth test table.
CREATE TABLE MyTestTable4 (a INT)
-- perform full backup F2
BACKUP DATABASE SQLAuthority TO DISK = 'c:\SQLAuthority_F2.bak' WITH FORMAT

Now, we have backups taken in below order:

  1. Full back up ‘c:\SQLAuthority_F1.bak’
  2. First Transaction log backup ‘c:\SQLAuthority_T1.trn’
  3. Second Transaction log backup ‘c:\SQLAuthority_T2.trn’
  4. Full back up ‘c:\SQLAuthority_F2.bak’

Reproduce the Error

-- Let’s perform restore now but before that, lets drop the database
USE MASTER
GO
ALTER DATABASE SQLAuthority SET single_user WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE SQLAuthority
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'c:\SQLAuthority_F1.bak' WITH NORECOVERY
GO
RESTORE LOG SQLAuthority FROM DISK = 'c:\SQLAuthority_T2.trn' WITH NORECOVERY
GO

Here is the error.

Processed 280 pages for database ‘SQLAuthority’, file ‘SQLAuthority’ on file 1.
Processed 6 pages for database ‘SQLAuthority’, file ‘SQLAuthority_log’ on file 1.
RESTORE DATABASE successfully processed 286 pages in 0.148 seconds (15.097 MB/sec).
Msg 4305, Level 16, State 1, Line 43
The log in this backup set begins at LSN 33000000026400001, which is too recent to apply to the database. An earlier log backup that includes LSN 33000000023200001 can be restored.
Msg 3013, Level 16, State 1, Line 43
RESTORE LOG is terminating abnormally.

The error appeared because we have skipped T1. As the message says “An earlier log backup that includes … can be restored”. We can get list of backups taken from MSDB database on source server.

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'SQLAuthority'
--Get Backup History for required database
SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type]
WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date

We can also get the same information from ERRORLOG as well.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here are the sample messages. I have trimmed them for clarity.

2015-08-08 17:49:06.340 Backup Database backed up. Database: SQLAuthority, creation date(time): 2015/08/08(17:49:05), pages dumped: 291, first LSN: 33:136:179, last LSN: 33:232:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘c:\SQLAuthority_F1.bak’}).

2015-08-08 17:49:06.550 Backup Log was backed up. Database: SQLAuthority, creation date(time): 2015/08/08(17:49:05), first LSN: 33:136:179, last LSN: 33:264:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘c:\SQLAuthority_T1.trn’}).

2015-08-08 17:49:06.680 Backup Log was backed up. Database: SQLAuthority, creation date(time): 2015/08/08(17:49:05), first LSN: 33:264:1, last LSN: 33:280:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘c:\SQLAuthority_T2.trn’}).

2015-08-08 17:49:07.110 Backup Database backed up. Database: SQLAuthority, creation date(time): 2015/08/08(17:49:05), pages dumped: 291, first LSN: 33:312:143, last LSN: 33:392:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘c:\SQLAuthority_F2.bak’}).

So, once we find missing transaction log backup, we need to restore them in sequence. Do you have any other trick to solve this issue? If you can share, it would help others as well.
Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #031 – How to do Case Sensitive SQL Query Search

Question – How to do case sensitive search on any column of the table?

Answer

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from the above query, the collation of the query needs to be changed as follows.

SELECT
Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Adding File to Database in AlwaysOn Availability Group

Writing some typical interview questions have been on my list always. I look for new ways of tricking the person and most importantly, some of the interview questions are designed to make sure the person has strong fundamentals. This blog is one such attempt to understand the basics.

This is one of the common interview questions – What would happen if you add a new file (database file MDF or transaction log file LDF) to a database which is part of a high availability solution? As you might know that there are various high availability solutions like Failover clustering, database mirroring log shipping and the effect would be different in each solution. Out of many other high availability solutions, in this blog we will talk about availability group feature which was introduced in SQL Server 2012.

Let us talk about the basics of availability group first. Whatever modification operation done on the database would be logged to the transaction log file, called as log record. Since database is part of availability group the same command would be sent to all secondary replicas via log record. This means that adding data or log file to primary database essentially roll-forwards the same command to all secondary replicas.  This means that ADD FILE operation will be executed on Secondary servers. Using below code, I have added the file to drive E on Primary Instance.

USE [master]
GO
ALTER DATABASE [MyConDB]
ADD FILE (NAME = N'SQLAuthority_2',
FILENAME = N'E:\SQLAuthority.ndf' ,
SIZE = 4096KB, FILEGROWTH = 1024KB) TO FILEGROUP [PRIMARY]
GO

If this drive does not exist in secondary, then this operation will fail, resulting into the suspended secondary replica. Here is the information seen in the ERRORLOG (SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location)

2015-01-01 17:52:14.19 spid45s     Error: 5123, Severity: 16, State: 1.
2015-01-01 17:52:14.19 spid45s     CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\SQLAuthority.ndf’.

2015-01-01 17:52:14.27 spid45s     Error: 5183, Severity: 16, State: 1.
2015-01-01 17:52:14.27 spid45s     Cannot create the file “SQLAuthority_2”. Use WITH MOVE to specify a usable physical file name. Use WITH REPLACE to overwrite an existing file.

2015-01-01 17:52:14.30 spid45s     AlwaysOn Availability Groups data movement for database ‘SQLAuthority’ has been suspended for the following reason: “system” (Source ID 2; Source string: ‘SUSPEND_FROM_REDO’). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.

2015-01-01 17:52:14.33 spid45s     Error: 3313, Severity: 21, State: 2.
2015-01-01 17:52:14.33 spid45s     During redoing of a logged operation in database ‘SQLAuthority’, an error occurred at log record ID (34:358:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

If there is a possibility to recreate the same path on secondary replica, then we need to resume the data movement using below command.

ALTER DATABASE [SQLAuthority] SET HADR RESUME;
GO

If it is not possible to recreate the path on secondary, then we need to reinitialize this secondary again. Which means we need to restore full backup and a log backup from the primary.

Have you encountered the same problem in Log shipping or Clustering? What steps did you take to solve it? Do let me know via the comments below, I am sure we can learn from each other this way.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to do IF…THEN in SQL SERVER?

Just another day I received following question –

“Pinal,

I am from DotNet expertise and I do not know how to convert the logic of IF…THEN into SQL statement, would you please help?

~Rajesh”

I love simple questions as they have simple answers. In SQL Server, we can use a CASE statement to implement IF…THEN. Here is the example of the CASE statement.

Example 1:

If you are logic is as follows:

IF -1 < 1 THEN 'TRUE'
 ELSE 'FALSE'

You can just use CASE statement as follows:

-- SQL Server 2008 and earlier version solution
SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result
GO

-- SQL Server 2012 solution
SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result;
GO

If you are interested further about how IIF of SQL Server 2012 works read the blog post.

Well, in our example the condition which we have used is pretty simple, but in the real world the logic can very complex.

You can read more about this subject in the blog post over here: SQL SERVER – Implementing IF … THEN in SQL SERVER with CASE Statements.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Stored Procedure for Executing SSIS Packages in the SSIS Catalog – Notes from the Field #092

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

andyleonard SQL SERVER   A Stored Procedure for Executing SSIS Packages in the SSIS Catalog   Notes from the Field #092

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – How to create a stored procedure for executing SSIS Package in the SSIS Catalog? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


The following is a snippet from Chapter 2 of the book SSIS Design Patterns co-written by Matt Masson (Blog | @mattmasson), Tim Mitchell (Blog | @Tim_Mitchell), Jessica Moss (Blog | @jessicammoss), and Michelle Ufford (Blog | @sqlfool). Earlier in the chapter there are demos that describe a simple SSIS package named Chapter2.dtsx which is part of an SSIS project named Chapter2, and which is deployed to an instance of the SSIS Catalog in a Folder named “Chapter 2”. But you can use this stored procedure to execute any SSIS package in the SSIS Catalog. That’s the whole point!

SQL Server 2014 provides a new way to manage and execute Integration Services packages: Integration Server Catalogs. We explore this method next.

Integration Server Catalogs

You can only manage SSIS projects that use the Project Deployment Model in Integration Services Catalogs. To execute a package in the catalog, use SSMS to connect to the instance of SQL Server hosting the SSISDB database. Expand the Integration Services Catalogs node, and then expand the SSISDB node. Drill into the folder containing the SSIS project and package(s). Right-click the package you wish to execute and click Execute, as shown in Figure 2-1.

notes91 1 SQL SERVER   A Stored Procedure for Executing SSIS Packages in the SSIS Catalog   Notes from the Field #092

Figure 2-1. Executing an SSIS Package deployed to the SSIS Catalog

The Execute Package Window displays, as shown in Figure 2-2. It allows you to override Parameter values, ConnectionString properties of Connection Managers built at design-time, or any other externalize-able property accessible from a Package Path (via the Advanced tab) for this execution instance of the SSIS package stored in the SSIS Catalog.

notes91 2 SQL SERVER   A Stored Procedure for Executing SSIS Packages in the SSIS Catalog   Notes from the Field #092

Figure 2-2. Execute Package Window

Integration Server Catalog Stored Procedures

Please note the Script button above the Parameters tab in Figure 2-2. This button allows you to generate Transact-SQL statements that will execute the SSIS package. For the Chapter2.dtsx package stored in the SSIS Catalog, the scripts will appear similar to that in Listing 2-1.

Listing 2-1. Transact-SQL Script Generated From the Execute Package Window

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
   @package_name=N'Chapter2.dtsx'
  ,@execution_id=@execution_id OUTPUT
  ,@folder_name=N'Chapter2'
  ,@project_name=N'Chapter2'
  ,@use32bitruntime=False
  ,@reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
   @execution_id
  ,@object_type=50
  ,@parameter_name=N'LOGGING_LEVEL'
  ,@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

You can use these same stored procedures to execute SSIS Packages in the SSIS Catalog! In fact, I designed a script to create a wrapper stored procedure that will call the Transact-SQL statements executed when an SSIS Package is executed in the SSIS Catalog. You can see that script in Listing 2-2.

Listing 2-2. Script to Build a Wrapper Stored Procedure for Executing SSIS Packages in the SSIS Catalog

 /* Select the SSISDB database */
Use SSISDB
Go

 /* Create a parameter (variable) named @Sql */
Declare @Sql varchar(2000)

 /* Create the Custom schema if it does not already exist */
print 'Custom Schema'
If Not Exists(Select name 
              From sys.schemas 
                Where name = 'custom')
 begin
   /* Create Schema statements must occur first in a batch */
  print ' - Creating custom schema'
  Set @Sql = 'Create Schema custom'
  Exec(@Sql)
  print ' - Custom schema created'
 end
Else
 print ' - Custom Schema already exists.'
print ''

 /* Drop the Custom.execute_catalog_package Stored Procedure if it already exists */
print 'Custom.execute_catalog_package Stored Procedure'
  If Exists(Select s.name + '.' +  p.name
            From sys.procedures p
            Join sys.schemas s
                On s.schema_id = p.schema_id
         Where s.name = 'custom'
           And p.name = 'execute_catalog_package')
   begin
    print ' - Dropping custom.execute_catalog_package'
    Drop Procedure custom.execute_catalog_package
    print ' - Custom.execute_catalog_package dropped'
   end

   /* Create the Custom.execute_catalog_package Stored Procedure */
  print ' - Creating custom.execute_catalog_package'
go

/*

     Stored Procedure: custom.execute_catalog_package
     Author: Andy Leonard
     Date: 4 Mar 2012
     Description: Creates a wrapper around the SSISDB Catalog procedures
                  used to start executing an SSIS Package. Packages in the
                SSIS Catalog are referenced by a multi-part identifier
                 - or path - that consists of the following hierarchy:
        Catalog Name: Implied by the database name in Integration Server 2014
        |-Folder Name: A folder created before or at Deployment to contain the SSIS project
        |-Project Name: The name of the SSIS Project deployed
        |-Package Name: The name(s) of the SSIS Package(s) deployed

        Parameters:
        @FolderName [nvarchar(128)] {No default} – 
         contains the name of the Folder that holds the SSIS Project
        @ProjectName [nvarchar(128)] {No default} – 
         contains the name of the SSIS Project that holds the SSIS Package
        @PackageName [nvarchar(260)] {No default} – 
         contains the name of the SSIS Package to be executed
        @ExecutionID [bigint] {Output} – 
         Output parameter (variable) passed back to the caller
        @LoggingLevel [varchar(16)] {Default} – 
         contains the (case-insensitive) name of the logging level
         to apply to this execution instance
        @Use32BitRunTime [bit] {Default} – 
         1 == Use 64-bit run-time
                                                      0 == Use 32-bit run-time
        @ReferenceID [bigint] {Default} –          contains a reference to an Execution Environment
        @ObjectType [smallint] –          contains an identifier that appears to be related to the          SSIS PackageType property 
        Guessing: @ObjectType == PackageType.ordinal (1-based-array) * 10
         Must be 20, 30, or 50 for catalog.set_execution_parameter_value
         stored procedure

        Test: 
        1. Create and deploy an SSIS Package to the SSIS Catalog.
        2. Exec custom.execute_catalog_package and pass it the 
          following parameters: @FolderName, @ProjectName, @PackageName, @ExecutionID Output 
        @LoggingLevel, @Use32BitRunTime, @ReferenceID, and @ObjectType are optional and 
        defaulted parameters.

         Example:
           Declare @ExecId bigint
           Exec custom.execute_catalog_package
         'Chapter2'
        ,'Chapter2'
        ,'Chapter2.dtsx'
        ,@ExecId Output
        3. When execution completes, an Execution_Id value should be returned.
        View the SSIS Catalog Reports to determine the status of the execution 
        instance and the test.

*/
Create Procedure custom.execute_catalog_package
  @FolderName nvarchar(128)
 ,@ProjectName nvarchar(128)
 ,@PackageName nvarchar(260)
 ,@ExecutionID bigint Output
 ,@LoggingLevel varchar(16) = 'Basic'
 ,@Use32BitRunTime bit = 0
 ,@ReferenceID bigint = NULL
 ,@ObjectType smallint = 50
As

 begin
  
  Set NoCount ON
  
   /* Call the catalog.create_execution stored procedure
      to initialize execution location and parameters */
  Exec catalog.create_execution
   @package_name = @PackageName
  ,@execution_id = @ExecutionID Output
  ,@folder_name = @FolderName
  ,@project_name = @ProjectName
  ,@use32bitruntime = @Use32BitRunTime
  ,@reference_id = @ReferenceID

   /* Populate the @ExecutionID parameter for OUTPUT */
  Select @ExecutionID As Execution_Id

   /* Create a parameter (variable) named @Sql */
  Declare @logging_level smallint
   /* Decode the Logging Level */
  Select @logging_level = Case 
                           When Upper(@LoggingLevel) = 'BASIC'
                           Then 1
                           When Upper(@LoggingLevel) = 'PERFORMANCE'
                           Then 2
                            When Upper(@LoggingLevel) = 'VERBOSE'
                           Then 3
                           Else 0 /* 'None' */
                          End 
   /* Call the catalog.set_execution_parameter_value stored
      procedure to update the LOGGING_LEVEL parameter */
  Exec catalog.set_execution_parameter_value
    @ExecutionID
   ,@object_type = @ObjectType
   ,@parameter_name = N'LOGGING_LEVEL'
   ,@parameter_value = @logging_level

   /* Call the catalog.start_execution (self-explanatory) */
  Exec catalog.start_execution
    @ExecutionID

 end

GO

If you execute this script to create the custom schema and stored procedure in your instance of the SSISDB database, you can test it using the statement in Listing 2-3.

Listing 2-3. Testing the SSISDB.custom.execute_catalog_package Stored Procedure

Declare @ExecId bigint
Exec SSISDB.custom.execute_catalog_package 'Chapter2','Chapter2','Chapter2.dtsx',
@ExecId Output

Conclusion

This custom.execute_catalog_package stored procedure can be used to execute an SSIS package from any folder and project in the SSIS Catalog.

The SSIS Script Task can accomplish much more than generating log messages. This brief introduction and basic example have demonstrated how to get started configuring and using the SSIS Script Task. As you can see, SSIS Script Tasks give you development capabilities you may not have been aware of.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Knowing Which Database is Consuming My Memory

I have been fortunate enough to be at conferences around the world and it is always refreshing to see how people come up with some really common yet never answered questions from time to time. The classic questions I get asked ever since I started working with databases and SQL Server is – why does SQL Server take all the memory and not return it? Even more bizarre is the question – Can I know how much memory is my databases using?

I always tell them, memory is a big topic and we need to use a number of commands like DBCC MEMORYSTATUS to know the internal working. The much more interesting way is to find out what are the pages in our buffer pool for our various databases. This can be got using DMVs as shown below:

--List the Number of pages in the buffer pool by database and page type
SELECT DB_NAME(database_id),
page_type,
COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id! = 32767
GROUP BY database_id, page_type
ORDER BY number_pages DESC
GO
--List the number of pages in the buffer pool by database
SELECT DB_NAME(database_id),
COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id! =32767
GROUP BY database_id
ORDER BY database_id
GO

os buffer descriptors 01 SQL SERVER   Knowing Which Database is Consuming My Memory

As you can see in the above output, we can see the amount of data pages and index pages that are loaded into our SQL Server memory.

A small variation of the above query can be to scan the buffer pool based on the type of pages that are loaded into memory. Below is a typical query fired against the same DMV.

--List the number of pages in the buffer pool by page type
SELECT page_type, COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
GROUP BY page_type
ORDER BY number_pages DESC
GO
--List the number of dirty pages in the buffer pool
SELECT COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1
GO

os buffer descriptors 02 SQL SERVER   Knowing Which Database is Consuming My Memory

In the above query, I have also shown the dirty pages that are in memory and are yet to be flushed out.

This DMV is super useful when you have a number of databases that are running on our server and want to find out who is consuming the server memory. Do let me know your thoughts and what output are you seeing in your environment. Is there anything strange that you fine? Let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Unable to Start SQL – Error: SQL Server Could Not Spawn Lazy Writer Thread

I have a tendency to try out various settings on a typical SQL Server just to see how the behavior changes from time to time. While playing with affinity settings in SQL Server – I shot myself in the foot. It was interesting to reach how I came out of the situation. I was not able to reproduce this error on the enterprise edition though.

Here is what I found in Errorlog:

2015-07-28 17:12:11.31 Server    Processor affinity turned on: node 0, processor mask 0x00000006. Threads will execute on CPUs per affinity settings.

2015-07-28 17:12:11.31 Server    I/O affinity turned on, processor mask 0x00000001. Disk I/Os will execute on CPUs per affinity I/O mask/affinity64 mask config option.

2015-07-28 17:12:17.44 Server    Node configuration: node 0: CPU mask: 0x00000006:0 Active CPU mask: 0x00000006:0.

2015-07-28 17:12:17.45 Server    Error: 17120, Severity: 16, State: 1.

2015-07-28 17:12:17.45 Server    SQL Server could not spawn lazy writer thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
If we convert highlighted number to binary, we can decode the affinity which I have set.

0x00000006 (in hex) = 0110 (in binary) in Processor affinity
0x00000001 (in hex) = 0001 (in binary) in I/O affinity

You can treat 1s and 0s as checkboxes for processors. Below picture should make it more clear.

affinity 01 SQL SERVER   Unable to Start SQL   Error: SQL Server Could Not Spawn Lazy Writer Thread

As you can see there is no overlap so I don’t see any problem, but still SQL Server didn’t start. I checked various online places, but few articles asked to repair the instance, which will not work in this situation. So finally, I sat down calmly and recalled that there is an option to start SQL with minimal configuration. I have used that earlier with one of my clients for SQL startup due to tempdb misconfiguration.

Here are the steps which worked:

  1. Start SQL in minimal configuration using –f parameter.

NET START MSSQLSERVER /f

If you are having named instance called Inst1 then you need to use below
NET START MSSQL$INST1 /f

  1. Connect to SQL using any client tool (SQLCMD or SSMS)
  2. Change the affinity back to default.

T-SQL

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO;
GO
EXEC sys.sp_configure N'show advanced options', N'1';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'affinity I/O mask', N'0';
GO
RECONFIGURE;
GO
EXEC sys.sp_configure N'show advanced options', N'0';
GO
RECONFIGURE WITH OVERRIDE;
GO

UI

affinity 02 SQL SERVER   Unable to Start SQL   Error: SQL Server Could Not Spawn Lazy Writer Thread

  1. Stop SQL Service

Default Instance – NET STOP MSSQLSERVER

Named Instance – NET STOP MSSQL$INST1

  1. Start it normally.

In some situations, you might run into issue where you would get this error while following above steps.

Login failed for user ‘LoginName’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

You can refer my friend Balmukund’s blog to make a connection in single user mode via start up parameter “m”

Help: How to fix error – Reason: Server is in single user mode. Only one administrator can connect at this time

Once you are able to connect using SQLCMD, you need to use T-SQL to fix the affinity value. Hope this blog helps you to solve one of the things I discovered by my experiments. Do let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Increase Characters Displayed in Text Results

Working with various SQL Server options is always a great feeling. Every now and then I go to the Options page to get just the thing I want. When I wrote the blog post – SQL SERVER – Getting started and creating JSON using SQL Server 2016 I was expecting interesting conversations about JSON use. On the contrary, guess what – I got an email from a junior explorer of SQL Server 2016 CTP is stating that he was not getting the same output that I had shown in my blog post. I was surprised by this statement.

I asked the mailer to send me a screen shot or an example of what he was seeing. Lesser did I know the context because this was exactly what I had got and I had changed something for the blog post.

Using Query Options

Here are the steps I did while I was writing the previous blog post.

Right click in the query editor and select Query Options. Under Results, select Text. Uncheck “Include column headers in the result set” and change the maximum number of characters displayed to 8192.

Click on in the editor and click Results To and choose Results to Text and click OK.

increase max number character 01 SQL SERVER   Increase Characters Displayed in Text Results

After making the above change, I was able to get upto 8192 characters as part of output to Text.

This is one the simplest option that I thought was easy to find and yet had to share them with couple of readers. Since I was repeating the same, I thought it was worth to write it as a blog post for you.

Note: The default maximum number of characters displayed in each column is 256. The maximum allowed value is 8192.

Do let me know if you have ever changed this option every in your environments? What value did you use whenever you had to change? Let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #030 – Retrieve Last Inserted Identity of Record

Question: What are the different ways of retrieving the identity of last inserted record? Which method do you prefer?

Answer: There are many different ways to do that. Here are three different ways to retrieve the identity of the last inserted record. I personally use SCOPE_IDENTITY () to return the identity of the recently inserted record as it avoids the potential problems associated with adding a trigger.

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Remove All Characters From a String Using T-SQL?

In the recent past I have seen the best interaction and learning happens when we all get into a puzzle and challenge mode. And that just gets the creative juice in our minds and some really innovative solutions are given. So this blog is written like a nice trivia solution and I would love to hear from you on potential alternate solutions to this as you read the simplified solution.

There are many situations where a developer needs to do a lot of string manipulation using T-SQL. There are many things which ideally should be done via SQLCLR.

One of my blog reader posted a comment recently

I want to get only integer part in string. for example, string contain ab123ce234fe means i want only integer part like 123234. how can i get?

I gave him below suggestion.

SET NOCOUNT ON
DECLARE
@loop INT
DECLARE
@str VARCHAR(8000)
SELECT @str = 'ab123ce234fe'
SET @loop = 0
WHILE @loop < 26
BEGIN
SET
@str = REPLACE(@str, CHAR(65 + @loop), '')
SET @loop = @loop + 1
END
SELECT
@str

Do you think there is a better way to achieve that? Please write it in comments section. It is going to be a great learning opportunity for all.

Reference: Pinal Dave (http://blog.sqlauthority.com)