SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Introduction

 

You know there is always an issue - the log file growing very fast and big.  If you have plenty of storage, then this might not be a problem for you.  Anyway, this is no exception in the latest version of SQL, we still have to do something to truncate and shrink these files.

 

Implementation

 

1)   Let’s first check the log file size.

 

SELECT

  --DB_NAME(database_id) AS DatabaseName,

  --Physical_Name,

  Name AS Logical_Name,

  (size*8)/1024 SizeMB

FROM

  sys.master_files

WHERE

  DB_NAME(database_id) = 'tempdb'

GO

 

Output

 

 

2)   Now truncate the log file.

 

USE tempdb;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE tempdb

SET RECOVERY SIMPLE WITH NO_WAIT;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE(tempdb_log, 1);  --file_name is the logical name of the file to be shrink

GO

-- Reset the database recovery model.

ALTER DATABASE tempdb

SET RECOVERY FULL WITH NO_WAIT;

GO

 

3)   Let’s check the log file size.

 

SELECT

  --DB_NAME(database_id) AS DatabaseName,

  --Physical_Name,

  Name AS Logical_Name,

  (size*8)/1024 SizeMB

FROM

  sys.master_files

WHERE

  DB_NAME(database_id) = 'tempdb'

GO

 

Output

 

 

Consider the following information when you plan to shrink a file:

 

• Make a full backup of your database before shrink the database file.
• From setting the database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.
• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation. In this case, you should consider increasing the Growth Rate of your Database to keep the performance under control.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

Reference: http://technet.microsoft.com/en-us/library/ms189493.aspx

Lookup functions in SSRS 2008 R2 - III

Introduction

This is the final post to discuss the last Lookup function Multilookup(). The other two – Lookup() and LookupSet() have already been discussed in my previous posts.

Syntax

Multilookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound. The only difference from the previous lookup functions is that, here this is a VariantArray.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, the value from the first matching row will be returned for all the values in the source expression. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the previous posts of Employees & Departments, suppose we want to display the name of the departments to which the Employee is related to, we can use the following expression -

Table Structure of Employees Table

ID	EmployeeID	DepartmentIDs
1	1	1,2,3
2	2	2,3

Table Structure of Departments Table

ID	Name
1	D1
2	D2
3	D3

Required Output

ID	EmployeeID	Departments
1	1	D1,D2,D3
2	2	D2,D3

To get the Departments, we can use the following expression in SSRS -

=Join(MultiLookup(Split(Fields!DepartmentIDs.Value,","),

   Fields!ID.Value,Fields!Name.Value,"Departments")),

   ",")

Here, we have used 3 functions -

1. Split() – To convert the comma separated DepartmentIDs into a value array.
   
2. Multilookup() – To find the Name of departments for the matching ID.
   
3. Join() – Prepare the comma separated string for the names returned by the Multilookup() as array.

Conclusion

So, with this we have covered all the 3 lookup functions provided in SSRS 2008 R2 and have learned how to make best use of them to fetch data from multiple datasets.

Lookup functions in SSRS 2008 R2 - II

 Introduction

In my last post on Lookup functions related to SSRS 2008 R2, I had explained Lookup(). It is used to fetch the first matching value from the other DataSet. Now, what if we want all the matching values from the other DataSet. Here, the LookupSet() function comes handy. Let’s try to understand this.

Syntax

LookupSet(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, all the values from the matching rows will be returned. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the last post of Employees & Departments, suppose we want to display the name of all the departments to which the Employee is related to, we can use the following expression -

=JOIN(LookupSet(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments"),",")

Here, we have used 2 functions of SSRS,

1. LookupSet() – To get an Variant array of the matching values. Here, the DepartmentID field will be matched with the ID field of the Departments Dataset and the Name from all the matching rows will be returned.
   
2. JOIN() – To join all the elements of the array as a comma separated string.

Hope, I have explained clearly the use of LookupSet() funciton. Next, I will blog about the last Lookup function – MultiLookup().

Get numeric value from string using TSQL

Introduction

Many times while working on applications, we often land into a situation where we want to get the only numeric values from alpha numeric string. There are some approaches given, like you may achieve it with making a loop and without loop.

Approaches

I have prescribed as a simple query, but you will achieve same by making a function.

1. Get the numbers through loop.

DECLARE @AlphaNumericString VARCHAR(MAX) = 'ABC1DEF2GHI3'

DECLARE @NumericValueIndex INT

SET @NumericValueIndex = PATINDEX('%[^0-9]%', @AlphaNumericString)

WHILE @NumericValueIndex > 0

  BEGIN

    SET @AlphaNumericString = STUFF(@AlphaNumericString, @NumericValueIndex, 1, '' )

    SET @NumericValueIndex = PATINDEX('%[^0-9]%', @AlphaNumericString )

  END

SELECT ISNULL(@AlphaNumericString,0) 'Numeric Values'

OUTPUT

2. Get the numbers without loop.

DECLARE @AlphaNumericString VARCHAR(MAX) = 'ABC1DEF2GHI3'

DECLARE @NumericValues VARCHAR(MAX) = ''

SELECT

  @NumericValues =

    @NumericValues +

      CASE WHEN NumericValue like '[0-9]' THEN NumericValue ELSE '' END FROM

      (

        SELECT

          SUBSTRING(@AlphaNumericString,NumericValue,1) NumericValue

        FROM

        (

          SELECT

            number NumericValue

          FROM

            master..spt_values

          WHERE 

            TYPE = 'p' 

            AND number BETWEEN 1 AND LEN(@AlphaNumericString)

        ) T

      ) T

SELECT ISNULL(@NumericValues,0) 'Numeric Values'

OUTPUT

Limitation

There is a limitation in 2nd approach, the table “master..spt_values” contains only 2048 rows value. So if you want to get the numbers from the very big text contains above 2048 characters, then this approach is not suitable.

To over come this limitation, you can use “Tally Table” instead of “master..spt_values”. Replace “master..spt_values” with “Tally Table”.

Want to get more information about Tally Table? Click here.

Conclusion

If you have the need of numeric values only from less characters of 2048, then no need to process for “Tally Table”. Just use the “master..spt_values”.

Lookup functions in SSRS 2008 R2 - I

Introduction
Most of us who are regularly working with SSRS have always felt the need of some way through which multiple DataSets could be joined – something similar to JOINs of SQL. But there was no straight forward way to get it done in SSRS until the release of SSRS 2008 R2. SSRS 2008 R2 came up with Lookup Functions which plays a very handy role in getting the things done in a very simple way. However, these functions can not be taken as a alternate to JOINs. As their name suggests, they are just lookups helping us to retrieve values from a dataset that has name/value pairs and that is not bound to the data region.
Different Flavors
We are provided with 3 different lookup functions -

1. Lookup(source_expression, destination_expression, result_expression, dataset)
   
2. LookupSet(source_expression, destination_expression, result_expression, dataset)
   
3. Multilookup(source_expression, destination_expression, result_expression, dataset)

Let’s take up them one by one

1. Lookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.
destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter.
result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression. 
Note, If multiple matches are found, the value from the first matching row will be returned. And we can not use any aggregate functions in the result_expression.
Dataset – The Dataset in which we want to look the values into.
E.g. Suppose, we are having 2 Datasets – EmployeeDetails & Departments in SSRS. The tablix is bound to EmployeeDetails and in one of the columns we need to display the DepartmentName to which the Employee in each row belongs to. The below expression will help us to fetch the Name of the department from the Departments Dataset.

=Lookup(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments")

Here, the DepartmentID field from the Employees Dataset will be matched with the ID field of the Departments Dataset and the Name for the matching row will be returned.
So, we have seen how we can make use of Lookup() and get the matching value. Will continue with the other two functions in my next blog.

CRM 2011 – Get Optionset labels in Silverlight 4 using SOAP endpoints

 

Recently, I need to fetch the optionset values of one of the custom entities named as ‘Candidate’. It has one a custom attribute like job type. The job type is having values like Permanent, Temporary, Part time, on contract. I developed a Silverlight 4  data grid control to show the all candidates with some search criteria. By design, using OData service reference you can’t fetch the metadata of CRM entity. So here only get the related values of the optionset like 1 for Permanent, 2 for Temporary and so on. So, if you would like to show the label you have to choose the SOAP endpoint service reference.

Now, to start with SOAP endpoint development I referred a very useful link of MSDN. I am sharing the same here,

http://msdn.microsoft.com/en-us/library/gg594452.aspx 

With the help of this link you can setup the required service reference and the utility classes to use messages or methods of CRM SDK.

Below is the code snippet which I have used to fetch the labels using SOAP endpoints in my candidates.xaml.cs file. You can use the same code by just passing the entity and attribute names. I hope this will be helpful. 

   1: /// <summary>

   2: /// Get options set labels for specified entity's optionset type attribute

   3: /// </summary>

   4: /// <param name="entityName"></param>

   5: /// <param name="attributeName"></param>

   6: private void GetOptionSetLabels(string entityName, string attributeName)

   7: {

   8:     OrganizationRequest request = new OrganizationRequest();

   9:     request.RequestName = "RetrieveAttribute";

  10:     request["EntityLogicalName"] = entityName;

  11:     request["LogicalName"] = attributeName;

  12:     request["MetadataId"] = Guid.Empty;

  13:     request["RetrieveAsIfPublished"] = true;

  14:  

  15:     IOrganizationService service = SilverlightUtility.GetSoapService();

  16:     service.BeginExecute(request, new AsyncCallback(OnGetOptionSetLabelsComplete), service);

  17: }

  18:  

  19:  

  20:  

  21: /// <summary>

  22: /// Retrieve the resutls

  23: /// </summary>

  24: /// <param name="result"></param>

  25: private void OnGetOptionSetLabelsComplete(IAsyncResult result)

  26: {

  27:     //Get the original query back from the result.

  28:     OrganizationResponse response = ((IOrganizationService)result.AsyncState).EndExecute(result);

  29:  

  30:     if (response != null && response.Results.Count > 0)

  31:     {

  32:  

  33:         //Get the actual optionset meta data

  34:         OptionSetMetadata optionSetMetadata = ((EnumAttributeMetadata)(response.Results[0].Value)).OptionSet; 

  35:  

  36:         //Set the metadata object in required field

  37:         Common.JobTypeOptionSetMetadata = optionSetMetadata;

  38:     }

  39: }