RSS

Monthly Archives: June 2013

SQL Server 2014 – Public CTP Now Available

Check this Link

SQL Server 2014 – Public CTP Now Available

 
Leave a comment

Posted by on June 25, 2013 in Administration

 

The connection to the primary replica is not active. The command cannot be processed

The connection to the primary replica is not active. The command cannot be processed

MSSQLWIKI

 

 

 

 

 

 

 

 

 

 

| || |

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

 

View original post

 
Leave a comment

Posted by on June 22, 2013 in General topics

 

Bookmark lookup , Key lookup , RID lookup in SQL Server

Introduction : 

Today i will Show  :

What  is the Meaning of Bookmark lookup , Key lookup , RID lookup in SQL Server ?

when SQL Server Retrieve Bookmark lookup , Key lookup , RID lookup ?

How can we Remove the Bookmark lookup , Key lookup , RID lookup ?

Bookmark lookup , Key lookup , RID lookup Meaning  and when SQL Server Retrieve it ?

Read the rest of this entry »

 
Leave a comment

Posted by on June 17, 2013 in Index, Performance MSSQL

 

Drop All index in Table

To Drop ll index in Table by T-SQL i will Create SP to do this Task

CREATE PROCEDURE [dbo].[Utils_DeleteAllIndexesOnTable]
— Add the parameters for the stored procedure here
@TableName VarChar(100)
AS
BEGIN
Declare @IndexName varchar(100)
DECLARE index_cursor CURSOR FOR
SELECT name FROM sysindexes where id = object_id(@TableName)
AND NAME IS NOT NULL and ROWS > 0

OPEN index_cursor

— Perform the first fetch.
FETCH NEXT FROM index_cursor into @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN

if left(@IndexName,2) = ‘PK’
BEGIN
print ‘drop constraint ‘ + @IndexName + ‘ on ‘ + @TableName
Exec( ‘ALTER TABLE ‘ + @TableName +
‘DROP CONSTRAINT ‘ + @IndexName )

END
ELSE
BEGIN
— This is executed as long as the previous fetch succeeds.
print ‘drop index ‘ + @IndexName + ‘ on ‘ + @TableName
Exec(‘drop index ‘ + @IndexName + ‘ on ‘ + @TableName)
END

FETCH NEXT FROM index_cursor into @IndexName

END

CLOSE index_cursor
DEALLOCATE index_cursor
END

Execute your Stored Procedure but don’t Forget to pass the Table name as parameter to SP

exec [Utils_DeleteAllIndexesOnTable] Your_Table_Name

 
Leave a comment

Posted by on June 17, 2013 in Script

 

Performance Dashboard Reports in SQL Server 2008

introduction :

Microsoft is Great Company Really i love this Company .

Microsoft have Tools Report name (SQL Server 2005 performance Dashboard Reports) it,s good Report From Microsoft you can Download the Tool and Customize it to Show in your Server Stander Reports .

The Microsoft SQL Server 2005 Performance Dashboard Reports are used to monitor and resolve performance problems on your SQL Server 2005 database server. The SQL Server instance being monitored and the Management Studio client used to run the reports must both be running SP2 or later.

Now i need to Setup This report on SQL Server 2008R2 so i will Explain it step by step.

  1. Download the Tool from website .
  2. Install Performance Dashboard in the Default Location if you need to change it no problem but don’t forget the Path.
  3. I installed this tool in the default path After Installation Go to this Path (C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard)
  4. open your SQL Server You need to Effect This Report on .
  5. Search on this Folder (PerformanceDashboard) to (Setup. Exe)

Performance Dashborder_2

  1. By CTRL+F try to found this Statement ( select @ts_now = cpu_ticks) in your Code you will See this Line in this Script it .select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
  2. Replace it by This T-SQL (select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info)
  3. Performance Dashborder
  4. Run this Script on your SQL Server after you replaced the code with new line if you try to run this Script on SQL Server 2005 it will give you Error :Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6 Invalid column name ‘cpu_ticks_in_ms’.
  5. Right Click on your SQL Server >> Reports >> Custom >> go to this Path (C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard) >> Select (performance_dashboard_main.rdl) >> RUN
  6. After this Step you will find your New report (Performance Dashboard ) is Working immediately .
  7. If you need to Work it Again Right Click on your Server >> reports >>Performance_dashboard_main.
  8. Performance_dashboard3

Performance_dashboard_main

Performance_dashboard4

Performance_dashboardPerformance_dashboard5

 

in the finale i think it’s great report also if it not get All what we need but it’s good from SQL Server it’s have a good Interface to help all guys working on SQL Server Product .

 

SQL SERVER – 2014 Announced and SQL Server 2014 Datasheet

 
Leave a comment

Posted by on June 9, 2013 in General topics

 

Policy # Check the Data and Log file Extensions for All Dataabases

Today i will explain how to Check all Data file and log file for all your database we will check the Extensions for (MDF , NDF , LDF) by using policy management .

1- Create Conditions

at the first we will create 2 condition one for Data file and the other for Log file

I- Data File Extensions Condition

Open SQL Server Management studio >>> Mangment >>> Policy Mangment >>> Conditions >>>> Right Click on it >>> New Condition

Name : Data File Extensions .

Facet  : Data File .

Field : @File name

Data File Extensions Conditions

II – Log File Extensions Conditions :

Open SQL Server Management studio >>> Mangment >>> Policy Mangment >>> Conditions >>>> Right Click on it >>> New Condition

Name : Log File Extensions .

Facet  : Data File .

Field : @File name

Log File Extensions Conditions

2- Create policy :

Now we will Create 2 Policy one “Check Data File” and the Other is “Check log File”

I – Policy – Check Data File :

Open SQL Server Management studio >>> Mangment >>> Policy Mangment >>> Policies>>>> Right Click on it >>> New Policy

Name : Check Data File  .

Check Condition : Data File Extensions

Check Data File Policy

II – Policy – Check Log File :

Open SQL Server Management studio >>> Mangment >>> Policy Mangment >>> Policies>>>> Right Click on it >>> New Policy

Name : Check Log File  .

Check Condition : Log File Extensions

Check log File Policy

Now after we create the policy if you go to Policies and right click on Policy name ” Check Data File ” >>> Evaluate

Evaluate

After the Evaluate Finish you will find the Data file for your all databases in your SQl Server Instance if you clicked on View you will See the Full path to the MDF and NDF

in my example now i have MDF only no File Extensions  With NDF so i have More Cross

I Wish Success To Everyone

Eng. Mostafa Elmasry

Database Administrator