MCSA – SQL Server 2012

It’s a great self satisfaction to pass another milestone, MCSA SQL Server 2012.

Retrieving SQL Agent Job Status

Well, this post is all about retrieving SQL server agent job status after executing a job programmatically. There are two system tables in msdb database related to following query.

• sysjobs             : which keeps all the existing agent job details
• sysjobhistory : which keeps all the execution details

Basically you need to run following query on underlying SQL server to get job status of particular job. Job name and step name can be parameterized.

SELECT SJH.message,
SJH.run_status,
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobhistory SJH ON SJH.job_id = SJ.job_id
WHERE SJ.name = 'job1'
AND SJH.step_name = 'step1'

This will produce status and message of particular job step. You can filter out the previous executions using date column which can be found in sysjobhistory table.

SQL Server Transaction Log Management

I am going to explain how to manage transaction log file size in this post. This is the most effective and recommended way to maintain log file size. Most of the business critical databases must have full recovery mode to accomplish recovery purposes. Hence transaction log file is increased rapidly. So we need to have proper maintenance plan to overcome space problems.

we can take transaction file backups frequently(weekly,daily, may be even once in few hours) to maintain healthy log file size. This will allow transaction log file to free spaces for new log records.

Create a new maintenance plan under database management and insert a backup database task.

Here is the configuration.

and schedule the task daily(this depends on how frequently your log file is being increased) at suitable time.

it will look like this.

Hope this helps.

Power View Integration with SharePoint 2010/2013 – Multidimensional Mode

This time I thought to write about Microsoft SQL Server 2012 new feature power view integration with SharePoint 2010/2013 . Yes with existing multidimensional cube. There are so many spikes here and there so I will try to explain as much as simple.

Following are the requirements for this environment.

1. SQL Server 2012 SP1 Cumulative Update 4 or above (http://support.microsoft.com/kb/2833645)
2. SharePoint Server 2010 or 2013

• First of all you need to install SharePoint server.(2010 or 2013)
• Then Install SQL Server 2012 to same server.

• Install instance of SQL Server database engine with reporting services-SharePoint.

• then you need to install powerpivot instance for SharePoint. (Do not check on Add SQL Server Database option since you already have one)

• Create your site collection in SharePoint using powerpivot site template.
• Run the PowrPivot configuration tool in order to configure power pivot in your SharePoint installation. If you installed SharePoint 2013 then run PowerPivot configuration for SharePoint 2013 Configuration.

• After configuring powerpivot there will be power pivot solutions in SharePoint farm.
  • In SharePoint Central Administration go to System Settings
  • Manage farm solutions under Farm Management
  • Deploy powerpivot solutions to your site collection.

• Activate power pivot integration feature in your site collection. (Site Settings –> Site Collection Features under Site Collection Administration)

• Then install SharePoint reporting service and create service proxy.
  • Run following commands in SharePoint Management Shell with administrator privileges.
  • Install-SPRSService
  • Install-SPRSServiceProxy

• Start reporting services service

• In SharePoint Central Administration go to System Settings
• Then start Reporting Services in Manage Services on Server

• Create reporting services web application in SharePoint.
  • In SharePoint Central Administration go to Application Management
  • Then go to Manage Service Applications under Service Applications
  • Create new SQL Server Reporting Services service application

• Create new application pool for this service application

• Create new power pivot site in your site collection

• In that created site, you will have inbuilt power pivot gallery library. In that library, go to library settings.
• In Advanced Settings, Enable content types.

• Now in library settings, you will have Add from existing site content types. Go to that link and add BI Semantic model and Report Data Source

• After adding those content types you can create new Report Data Source in power pivot gallery.

• Following figure shows how to configure Report Data Source. Select Microsoft BI Semantic Model for Power View as Data Source Type. Give your analysis server as connection string.

• Now you have Report Data Source. That’s all you need to create your first power view report using your existing multidimensional cube. 🙂

Feel free to give any suggestions.

Shrink Database Log File

Most of the time we use full recovery mode in critical databases in order to accomplish high reliability.  Then we realize rapid growth of database log file. With high frequency of translations, sooner you will realize this matter and will face to hard disk space problem of server. These are few ways that we can reduce log file size.

First way using T-SQL query

Before shrinking the log file, we have to change recovery mode to simple. After shrinking,  set back to full mode again.

My database name is Test

Log file name is test2_log

I’m going to reduce this log file to 100mb

Second way

This can also done by database properties.

First set the recovery model to simple in options tab.

Then using database Tasks –> Shrink –> Files , reduce the log file size.

Do not forget to set recovery model to full mode again.

 

Third way

The best and most effective way. Please refer to this post sql server transaction log management