Restore MDF file to New Database

Execute below script to restore any MDF and LDF files to new Database.

USE [master]

GO

CREATE DATABASE DemoDB ON

( FILENAME = N'I:\MSSQL\DATA\DemoDB.mdf' ),

( FILENAME = N'K:\MSSQL\Logs\DemoDB_log.ldf' )

FOR ATTACH ;

GO

SQL to Shrink LogFile after Truncating Records

Below SQL helps you to shrink a DB grown due to truncating of records from tables regularly:

USE DemoDB;

DBCC SHRINKFILE ('DemoDB_log',0,truncateonly)

SSAS Error: No Mapping between Account Names and Security IDs was Done

We may come across the below error mostly while we are deploying SSAS cube in to one server:

It is because of difference in domain between development server and deployment server. In that cases Goto Roles --> Membership tab, and remove non-supportive members from the role.

Convert Unix Timestamp to Date format

SELECT *, DATE_FORMAT(FROM_UNIXTIME(TimeStamp), '%d-%m-%Y %h:%i:%s') AS LogDate
FROM dbo.reports
ORDER BY Timestamp DESC
LIMIT 10

T-SQL to Disable all Jobs in in SQL Agent

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

SSIS Package Runs Fine in Integration Server but Fails From SQL Agent Job

SSIS Package Runs Fine in Integration Server but Fails From SQL Agent Job:

This is the most common issue faced when we deploy packages in 64 bit system. When we create any package with Run64bit set as false, this issue occurs.

In order to overcome the 32\64 bit environment issue, it better we use command line in our job to execute the packages.

Use the below command:

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec /DTS "\MSDB\ETLFolder\ETLExtractMaster" /SERVER DW01P /CHECKPOINTING OFF  /REPORTING V

SQL Server Timed Out due to Low Memory Space While SSIS Package was Running

SQL Server Timed Out due to Low Memory Space While SSIS Package was Running:

While I was running my data warehouse job (containing many ETL Master Packges) in SQL SERVER 2008 R2, environment I was frequently facing Timed Out issue and SQL server got disconnected, but when I ran single package manually, it ran without failure.

So I performed the below tasks to monitor the performance of the Job:

1. Monitor performance in Task manager: The CPU usage was more than 90% when the job was running.

2. Tracked job even in EVENT VIEWER when the job failed, and got the below error message:

3. Executed below query in SQL Server:

SELECT [name] AS [Name]
      ,[configuration_id] AS [Number]
      ,[minimum] AS [Minimum]
      ,[maximum] AS [Maximum]
      ,[is_dynamic] AS [Dynamic]
      ,[is_advanced] AS [Advanced]
      ,[value] AS [ConfigValue]
      ,[value_in_use] AS [RunValue]
      ,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Max server memory (MB)','Min server memory (MB)')

On executing the above query, I found that the server is configured with maximum config value:

4. Executed the below query to trouble shoot the issue:


EXEC sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'min server memory', '1024'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'max server memory', '6000'
RECONFIGURE WITH OVERRIDE

I set Minimum Allowed Memory to 1 GB and Maximum Allowed Memory to 6 GB and now my data warehouse job runs without any issue and consumes less CPU usage.