SQL Server 2014 Health check DMV queries Day 2


images (1)

Hi guys in the Previous post i explained How we can get the SQL Server instance version and How we can know the installed date for SQL Server instance put today is DAY 2 and i will show new DMV in our track Instance Level configuration Queries

DMV#3 Server Properties

below DMV is more helpful and useful this DMV This gives you a lot of useful information about your instance of SQL Server like (Computer name , ProcessID , SQL Server collation  , Product level…etc)

DMV#4 SQL Server Agent Job Information

below DMV it will return for us basic information about SQl Server agent and the user name configure it to review it one by one.

DMV#5 SQL Server Agent Alert Information 

by the below DMV we will return the basic information about SQL Server agent alerts

Check the Full posts and the scripts : http://sqlserver-performance-tuning.net/?p=5924

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Website Slideshare ,Youtube Channel.

SQL Server 2014 Service Pack 1 has released


Congratulation Microsoft and big Congratulation for us (SQL Server Releases blog)

sql-server-2014-logo

One year ago, Microsoft launched SQL Server 2014. Today, April 15, Microsoft are pleased to announce the release of SQL Server 2014 Service Pack 1 (SP1). The Service Pack will be available for download on the Microsoft Download Center.

SQL Server 2014 SP1 contains fixes provided in SQL Server 2014 CU 1 up to and including CU 5, as well as a rollup of fixes previously shipped in SQL Server 2012 SP2. For highlights of the release, please read the Knowledge Base Article for Microsoft SQL Server 2014 SP1.

As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below. SQL Server 2014 with SP1 will be available in additional venues including the Volume Licensing Center and via Microsoft Update starting May 1, 2015.

Microsoft® SQL Server® 2014 SP1
Microsoft® SQL Server® 2014 SP1 Express
Microsoft® SQL Server® 2014 SP1 Feature Pack

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Website Slideshare ,Youtube Channel.

Shared disks on SQL Server Cluster


Hello my followers today my post is very simple post but more helpful and more important to know by the easiest way what is the shared disks on SQL Server cluster you manage it .

USE master
GO
SELECT *
FROM sys.dm_io_cluster_shared_drives
—-OR
SELECT *
FROM fn_servershareddrives()

this script will return the Shared drive names on your SQL Server cluster

Untitled

Follow the author:

View all my tips , LinkedIn Website Slideshare  , ABOUT ME

How many nodes exist in a SQL Server cluster


Bild_go-cluster+(S.+34)+-+NICHT+IN+SOCIAL+MEDIA+VERWENDEN

Any DBA going for new environment he should know how many nodes Exist in SQL Server Cluster he will mange it so today i will share with small DMV can tell us this information easily without doing more effort

“” SELECT * FROM ::FN_VIRTUALSERVERNODES() “”

DMV Return :

Node Name : the name of the Windows Server (Computer Name)

Status : Sever Status (Ruining / DOWN )

Is Current owner : it will tell you which node your SQL sever up and running on it now

Follow the author:

View all my tips , LinkedIn Website Slideshare  , ABOUT ME

Keep your database in safe mode


images (1)Hi Seniors DBA HI junior DBA HI DBA Consultant , There is no one without errors or defects if you agree with me Complete the post if not don’t waste your time !, How you can keep your database in safe Mode from any mistake can by happen by any accident ? we should put our database in the safe mode Under any circumstances ,so what you can do if you deleted your database by wrong ! and in the same time you lost your last backup for this database !! really it is very bad situation and may be not happened put may be happen also, to be calm and to Keep your database in safe mode you should take preventive action one of this preventive action Forcing the Drop database Command or Rename database to rollback and you can do it easily without more effort and without more maintenance cost by using DDL trigger ,  to know more about triggers and DML , DDL and Login triggers go for this link (CREATE TRIGGER (Transact-SQL)) Trigger can be created on tables , Databases and on All Server what we will do now Create trigger on all Server

Create Trigger 

IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = ‘KeepYourDBinSafeMode’)
DROP TRIGGER KeepYourDBinSafeMode
ON ALL SERVER;
GO
Create TRIGGER KeepYourDBinSafeMode
ON ALL SERVER
FOR Drop_Database ,Alter_Database
AS
PRINT ‘You must disable Trigger “KeepYourDBinSafeMode” to drop or alter Database!’
ROLLBACK
GO

Check the trigger from Mangment Studio 

Keepyourdatabaseinsavemode

Continue reading “Keep your database in safe mode”