Feeds:
Posts
Comments

Quick one today:

Recently, in our lab machine, we had a problem of too many VLF’s. More than 8000 VLFs for each of our VLDB’s.

So, we needed some aggressive approaches to clean up Transactional Log file in a hurry!! This is what we ended up doing.

Note: These steps are not to be used in a production environment. These are aggressive & unsophisticated steps not qualified for production environment. For more information read great blogs on SqlSkills website.

Root Cause:This happens, when Transaction Log files are incrementally grown in small increments over a long period of time a.k.a. AutoGrowth 1 MB.

Benefits: We need to maintain the health of Transactional Log file for better database performance.

Step 1:

If you database is in FULL recovery mode, take a Transactional Log backup. If SIMPLE mode, then run CHECKPOINT and then wait a minute or two.

 

Step 2:

Get the current count of VLFs

--
-- Capture the current VLF count
--
DBCC LogInfo
GO

 

Step 3:

Shrinkfile to the least possible size.

Now, shrink the Transactional Log file to the smallest file size possible. This varies from database to database based on the initial size specified during creation.

TRUNCATEONLY forces the free space to be returned to Operating System. We want to do this, so we could increase it later correctly.

--
-- Shrink log file size first.
--
DBCC SHRINKFILE (N'Sample_log' , 0, TRUNCATEONLY);
GO

 

Step 4:

Increase Transactional Log File Size
Now increase the log file size to its desired or expected size in its final state.

  • If you need 5 GB log file size, then assign something like 8 GB.
  • If your database usually needs 100 GB log file size, then assign something like 104 GB.
  • Essentially in multiples of 8 GB
--
-- Now, increase the log file size to desired size.
--
ALTER DATABASE Sample
MODIFY FILE (
     NAME = N'Sample_log'
   , SIZE = 104 GB
)
GO

 

Step 5:

Again get the current count of VLFs

--
-- Capture the current VLF count
--
DBCC LogInfo
GO

Now you’ll see that the VLF count is much smaller. Each VLF size is larger and help with better performance.

 

Hope this helps,
_Sqltimes

Quick one today:

Recently, when setting up replication on a lab machine, this error occured. Seen this error several times in the past, but never took time to blog about it.

TITLE: Configure Distribution Wizard
------------------------------
SQL Server could not retrieve information about server 'DC1REPL01'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=CantGetInformation&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Could not find stored procedure 'sp_MSreplcheck_qv'. (Microsoft SQL Server, Error: 2812)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.00.4100&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

Replication_Publisher_Error

We have Sql 2008 R2 for Publisher and Subscriber; And Sql 2014 for Distributor. This error occurred, when replication was being configured from the Publisher (Sql 2008 R2).

Since Distributor is Sql 2014, connecting to Distributor (Sql 2014) from a Publisher (Sql 2008 R2) has resulted in this error.

When configuring replication, we need to make sure all steps are attempted from the Management Studio (SSMS) of the latest version instance (which usually is Distributor.

Solution

This is fairly simple error to resolve.

Reattempt the actions from Distributor’s Management Studio (SSMS).

Log into Distributor machine and connect to Publisher from its Sql 2014 SSMS. Replication activities need to be performed from the instance that has the most latest SQL and SSMS versions. Since Distributor needs to be at least the same or later version of Sql compared to Publisher & Subscriber, we need to attempt these steps from Distributor’s SSMS.

 

Hope this helps,
_Sqltimes

Quick one today:

Earlier, on all our production systems, we needed to extend retention of all Sql Server error logs. Usually, after every few days (5 or 6), Sql Server purges oldest Sql Server Error Log. If you want to increase it, there is an easier option.

Step 1:

In Sql Server Management Studio, go to Management >> Sql Server Error Logs >> Right click and go to Configure.

SqlServer ErrorLogs Configure

SqlServer ErrorLogs Configure

Step 2:

Set the retention value to appropriate number. For us we are required to maintain 60 days, so it is set to 61 days, just to be safe.

SqlServer ErrorLogs Increase Retention

SqlServer ErrorLogs Increase Retention

 

Side Note:

Essentially it is a registry setting on the OS where the instance is installed. Look for something like

> HKEY_Local_Machine
> Software
> Microsoft
> Microsoft Sql Server
> (instance name)
> MSSQLServer
> Look for registry key called NumErrorLogs.

It would look something like this.

SqlServer ErrorLogs Registry Setting

SqlServer ErrorLogs Registry Setting

Hope this helps,
_Sqltimes

Quick one today:

Some times, when SlipStream is performed for Sql Install or for any other reasons, if you want to extract the Service Pack compressed executable into a pre-defined folder structure, there is a technique. Use /extract flag as a parameter to the exe file to extract it into a pre-defined location. Lets see an example:

SQLServer2014SP1-KB3058865-x64-ENU.exe /extract "DestinationLocation"

OR

SQLServer2014SP1-KB3058865-x64-ENU.exe /x "DestinationLocation"

 

Manual SP Extraction

Manual SP Extraction

As you see above, it asks to confirm the destination location; Once confirmed, the SP is extracted to that location.

 

Hope this helps,
_Sqltimes

Recently, we discussed how to call procedures with OUTPUT parameter. Now, we’ll cover a nuance in the same path. Default parameter values in Stored Procedures.

 

When we call a procedure that has DEFAULT values defined for its parameters, the way we call the procedure could change what values goes go into the execution of the procedure.

 

Variation 1: Purpose of DEFAULT values

DEFAULT values are defined in Store Procedures definition, so when no value is provided in procedure call, default values could be used (during execution).

Sample:

Consider the below sample procedure definition code below:

CREATE PROCEDURE dbo.abc
	  @Param1 INT = 10
...

Parameter @Param1 is defined with 10 as the DEFAULT value. So, when I call the procedure as:

EXEC dbo.abc
GO

The procedure takes the DEFAULT value 10 in its execution.

 

Variation 2 : Values in procedure call

When we actually provide any values in procedure call, the new value provided in the call is used in procedure execution, rather than the default value

DECLARE @P1 INT = 15
EXEC dbo.abc @P1

Now, in procedure execution, the new value (15) is used, and not the default value (10). This makes sense. Every time we need to run the procedure with different values, we just provide them in the parameter and it takes into effect; Where no value is provided, default values kick-in.

Variation 3 : NULL Value in procedure call

When we call the procedure with parameters, but do not provide any value, the default NULL value, assigned during variable declarations, will go in as the value.

DECLARE @P1 INT
EXEC dbo.abc @P1
GO

In this case, when the variable @P1 is declared, by default, NULL value is assigned. Since we do not have a subsequent step to assign value, the NULL value will go into the procedure call. This NULL value will overwrite the default value (10) mentioned in procedure definition.

So, if you want to run with default values defined in procedure definition, just run it like this:

EXEC dbo.abc
GO

 

Complete Code

For more testing, use the below complete code :

--
-- Sample procedure to check the default values
--
CREATE PROCEDURE dbo.abc
	  @Param1 INT = 10
	, @Param2 INT OUTPUT
AS
	SET @Param2 = @Param1

	SELECT @Param1, @Param2
GO

--
-- Variation 1: Purpose of DEFAULT values
--
EXEC dbo.abc
GO

--
-- Variation 2 : Values in procedure call
--
DECLARE @P1 INT = 1
      , @P2 INT
EXEC dbo.abc @Param1 = @P1, @Param2 = @P2 OUTPUT
GO

--
-- Variation 3 : NULL Value in procedure call
--
DECLARE @P1 INT
      , @P2 INT
EXEC dbo.abc @Param1 = @P1, @Param2 = @P2 OUTPUT
GO

Hope this helps,
_Sqltimes