Check this Link
SQL Server 2014 – Public CTP Now Available
Monthly Archives: June 2013
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
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
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 ?
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
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.
- Download the Tool from website .
- Install Performance Dashboard in the Default Location if you need to change it no problem but don’t forget the Path.
- I installed this tool in the default path After Installation Go to this Path (C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard)
- open your SQL Server You need to Effect This Report on .
- Search on this Folder (PerformanceDashboard) to (Setup. Exe)
- 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
- Replace it by This T-SQL (select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info)
- 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’.
- 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
- After this Step you will find your New report (Performance Dashboard ) is Working immediately .
- If you need to Work it Again Right Click on your Server >> reports >>Performance_dashboard_main.
Performance_dashboard_main
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 .
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
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
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
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
Now after we create the policy if you go to Policies and right click on Policy name ” Check Data File ” >>> 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
