RSS

Category Archives: Administration

How to Grant Show Plan Privilege

How to Grant Show Plan Privilege

Showplan Privilege it’s granted for any one need to see the execution plane for SQL Server query to check the performance of the index or doing index analysis.

Grant Showplan for one user in one database  :

GRANT Showplan TO [DominName\username]

Grant Showplan for one user in All databases in one SQL instance:

 EXEC sp_MSforeachdb N’IF EXISTS
(
SELECT 1 FROM sys.databases WHERE name = ”?”
AND Is_read_only <> 1
)
BEGIN
print ”Use [?]; GRANT Showplan TO [DominName\username]”
END’;

after the execution take the Print scripts and run it in another session.

 
1 Comment

Posted by on November 16, 2014 in Administration

 

Tags: , ,

How to Check the last restart for DB Server

How to Check the last restart for DB Server

Hi Dears i received request from the developers by the Staging DB was down yesterday evening, between 6:00 PM to 9:00 PM, can you please advice at this time to do Scientific investigation and troubleshooting so at this time you should check 2 point :

  1. Last SQL Server instance restart.
  2. Last DB server (windows services ) restart.

Last SQL Server Restart :

we can check it easily and by the Easiest ways (SQL Query)

SELECT sqlserver_start_time FROM sys.dm_os_sys_info
SELECT ‘Statistics since: ‘ + CAST(sqlserver_start_time AS VARCHAR) FROM sys.dm_os_sys_info
Server Restart

Read the rest of this entry »

 
Leave a comment

Posted by on November 15, 2014 in Administration

 

Tags: , , , , , , ,

How to monitor your SQL Server instances..(2/10)?

in the previous post we Speaking about our idea for this Series of posts and i Started my First post with How to Diagnosis MSSQL Server or MSSQL Database with fast way ..? Today i will complete this part from How to Diagnosis  Your Database,

Database Information : in this Script we can cover this below points

  • How many databases are on the instance?
  • What recovery models are they using?
  • What is the log reuse wait description?
  • How full are the transaction logs ?
  • What compatibility level are they on?
  • What is the Page Verify Option?
  • Make sure auto_shrink and auto_close are not enabled!

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
db.[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N’Log File(s) Used Size (KB)%’
AND ls.counter_name LIKE N’Log File(s) Size (KB)%’
AND ls.cntr_value > 0 OPTION (RECOMPILE);

Check Database File and all user database paths :

SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

How to Check the VLF count for All Databases:

CREATE TABLE #VLFInfo (RecoveryUnitID int, FileID int,
FileSize bigint, StartOffset bigint,
FSeqNo bigint, [Status] bigint,
Parity bigint, CreateLSN numeric(38));

CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);

EXEC sp_MSforeachdb N’Use [?];

INSERT INTO #VLFInfo
EXEC sp_executesql N”DBCC LOGINFO([?])”;

INSERT INTO #VLFCountResults
SELECT DB_NAME(), COUNT(*)
FROM #VLFInfo;

TRUNCATE TABLE #VLFInfo;’

SELECT DatabaseName, VLFCount
FROM #VLFCountResults
ORDER BY VLFCount DESC;

DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;

 

How to get CPU utilization by database :

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N’dbid’) AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 — system databases
AND DatabaseID <> 32767 — ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

Login_Name list with the Session Count:

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

Check Adhoc Query:

SELECT TOP(20) [text] AS [QueryText], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N’Compiled Plan’
AND cp.objtype = N’Adhoc’
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE)

Top Cash quires by Execution count :

SELECT TOP (250) qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,
total_worker_time, total_logical_reads, 
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
			THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
	  ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text 
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

this not the end i have lot of Scripts can help us to figure the most repeated issue in SQL Server so if you need to know the more Follow me in the Next Post 


 



		
 

Tags: , , ,

Building Your Security Audit in SQL Server 2012

Introduction :

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. SQL Server Audit provides the tools and processes you must have to enable, store, and view audits on various server and database objects.

You can record either server audit action groups’ per-instance, and database audit action groups or database audit actions per database. The audit event will occur every time that the auditable action is encountered.

All editions of SQL Server support server level audits. Database level auditing is limited to Enterprise, Developer, and Evaluation editions

How SQL Server Audit Works:

Auditing a SQL Server instance is not anything new. Previous and current versions of SQL have had C2 auditing functionality, which can incur more overhead than necessary and is not always flexible. For example, if the c2 logs fill up the disk to which they are being saved, the SQL instance will shut itself down. The data being collected by c2 gives details about both failed and successful attempts to access or modify objects and data, but the results are not returned in a way to make it easy to determine the nature of the events, simply the events themselves.

SQL 2008 Enterprise version allows for you to quickly and easily configure SQL Server Auditing to automate your auditing needs. The simple explanation for how this works is as follows:

 

  1. Create a SQL Server Audit object
  2. Create an Audit Specification (can be at the server or database level)
  3. Turn it on

It really is that easy to get it up and running. The hard part will come later on when someone stops by to ask for the details surrounding specific events, but even that is not difficult to provide due to the inclusion of an audit log reader.

Audit

Building Your Security Audit by T-SQL Script :

i will Explain Now :

How to Build SQL SERVER AUDIT SPECIFICATION  in SQL Server 2012 by T-SQL.

How to Build SQL DATABASE AUDIT SPECIFICATION in SQL Server 2012 by T-SQL.

Hot Read your SQL Server Audit file by Fn_get_Audit_File Function.

SQL SERVER Audit-Server:

Now if you Need to build Audit on All your Server to Check for Example The FAILED_LOGIN_GROUPWhat is the Steps we will do it one by one that’s what i will Explain now .

At the First We Need to Check if you have SQL Server Audit Created or no on our Servers i will Check the server_audits and the Server_audit_specificationsc by using the DMV:

Check Your Server Audits:

Select from Sys.server_audits
SELECT FROM sys.server_audit_specifications

before the Creating i will Check if i have Server Audit by my new audit name or no if i have i will delete it

USE [master]
GO
IF EXISTS (SELECT FROM sys.server_audits WHERE name = N'Audit-Server-FAILED_LOGIN_GROUP')
BEGIN
 ALTER SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
 WITH ( STATE = OFF );
 DROP SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
END
GO
IF EXISTS (SELECT FROM sys.server_audit_specifications WHERE name = N'ServerAuditSpecification-FAILED_LOGIN_GROUP')
BEGIN
 ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-FAILED_LOGIN_GROUP]
 WITH ( STATE = OFF );
 DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification-FAILED_LOGIN_GROUP]
END
GO

CREATE SERVER AUDIT :

CREATE SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
TO FILE
( FILEPATH = N'E:\My Work\SQLServer Audit\'
 ,MAXSIZE = 100 MB
 ,MAX_ROLLOVER_FILES = 2
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
GO
USE [master]
GO

Enable SERVER AUDIT :

ALTER SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
WITH ( STATE = ON );
GO

CREATE SERVER AUDIT SPECIFICATION:

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-FAILED_LOGIN_GROUP]
FOR SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = OFF)
GO

Enable SERVER AUDIT SPECIFICATION :

ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-FAILED_LOGIN_GROUP]
WITH ( STATE = ON );
GO

Now your SQL Server Audit Building on your Server level is Completed Let’s Check it and try to login with any Failed User or with correct user but with uncorrected password to test our New Audit

Check your SQL Server Audit from your SSMS(SQL Server Mangment Studio)
Audit2

Check your Audit File under your path of ‘E:\My Work\SQLServer Audit\’

Audit3

Now i tried to login with uncorrected user name DBA let’s go to Read the SQL Server Audit file by T-SQL to See if this action is saved or no

Read all information from a specific audit file:

SELECT FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit-Server-FAILED_LOGIN_GROUP_CC232C99-8C93-4DAD-89A6-7CE7001F9364_0_130229499734310000.sqlaudit'nullnull)
GO

Audit4

DATABASE AUDIT SPECIFICATION:[Audit_Database_Backup_and_Restore]

DATABASE AUDIT SPECIFICATION is SQL Server Audit on Database level. i will work in Database [AdventureWorks2012] and my Example will be How to Audit

Create Server Audit : 

USE [AdventureWorks2012]
GO
IF EXISTS (SELECT FROM sys.database_audit_specifications WHERE name = N'DatabaseAuditSpecification-Backup_and_Restore')
BEGIN
 ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Backup_and_Restore]
 WITH ( STATE = OFF );
 DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Backup_and_Restore];
END
GO
USE [master]
GO
IF EXISTS (SELECT FROM sys.server_audits WHERE name = N'Audit_Database_Backup_and_Restore')
BEGIN
 ALTER SERVER AUDIT [Audit_Database_Backup_and_Restore]
 WITH ( STATE = OFF );
DROP SERVER AUDIT [Audit_Database_Backup_and_Restore]
END
GO
CREATE SERVER AUDIT [Audit_Database_Backup_and_Restore]
TO FILE
( FILEPATH = N'E:\My Work\SQLServer Audit\'
 ,MAXSIZE = 100 MB
 ,MAX_ROLLOVER_FILES = 2
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
GO

Enable SERVER AUDIT:

USE [AdventureWorks2012]
GO
ALTER SERVER AUDIT [Audit_Database_Backup_and_Restore]
WITH ( STATE = ON );

CREATE DATABASE AUDIT SPECIFICATION : DatabaseAuditSpecification-Backup_and_Restore

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Backup_and_Restore]
FOR SERVER AUDIT [Audit_Database_Backup_and_Restore]
ADD (BACKUP_RESTORE_GROUP)
GO

ENABLE DATABASE AUDIT SPECIFICATION:

ALTER Database AUDIT SPECIFICATION [DatabaseAuditSpecification-Backup_and_Restore]
WITH ( STATE = ON );
GO

Now try to take backup from Database Adventurework2012 then check the Audit file in ‘E:\My Work\SQLServer Audit’

Backup Database AdventureWork2012 :

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITHNOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Read the Audit File :

SELECT FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit_Database_Backup_and_Restore_B3D67D19-2753-4B0C-8FB1-90B732A56DDD_0_130229523138080000.sqlaudit'nullnull)
GO

Audit5

How to read SQL Server Audit :

Read all information from a specific audit file :

SELECT FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit_Database_Backup_and_Restore_B3D67D19-2753-4B0C-8FB1-90B732A56DDD_0_130229523138080000.sqlaudit'nullnull)
GO

Read all audit files with a specified name :

SELECT FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit_Database_Backup_*.sqlaudit'nullnull)
GO

Read all audit files with a specified name:

SELECT FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit_Database_Backup_*.sqlaudit'nullnull)
GO
 
Leave a comment

Posted by on September 7, 2013 in Administration

 

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

 

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

 

How to Transfer SQL Server Jobs Task from Source Server to Distention Server!

Question :

How i can transfer SQL Server jobs Task from Source Server to Distention Server without T-SQL Script ?

Answer :

We can do that by SSIS ” SQL SERVER INTEGRATION SERVICES ”  by creating new package on it to transfer the jobs task from one server to another server.

Note : also by SSIS i can trnsfer

1- Database Task

2- Error Massage Task

3- Login Task

4- Master Stored procedure Task

5- SQL Server objects Task

 

Demo to how to transfer SQL Server jobs task :

1- open Start menu >>> SQL Server >>>  SQL Server Business Intelligence Development Studio >>> File Menu >>> New >>> project >>> Select project type is Business Intelligence Projects from the left bar >>> select from the Right template Integration Services Project >>>write name for this Project EX (Transfer Jobs) >>> Choose your path to save the project >>> OK

Transfer Jobs_1

2- from the left Select from the Toolbox Menu under the Control flow items >>> Transfer job Task  >>> Drag and Drop this task on Control flow page >>> right click on the task >>> Select Edit .

 Transfer Jobs_2

 

3- After the Edit we will view New Screen ” Transfer Jobs Task Editor ” >>> Select jobs from the left bar .

4- We have 3 Group :

A- Connection :

Source Connection           : The SQL Server Instance the Jobs Crated on it .

Destination Connection : The SQL Server you need to referrer the jobs to it .

B- Jobs :

Transfer All Jobs : False if you don’t need to transfer All jobs and this is Default Value , True  if you need to transfer All jobs .

Job List : if you select The transfer All jobs is FALSE Select from here the Jobs list you need to transfer it to another Server .

C- Option :

If Object Exist : Meaning if the one Job from the List job you need to transfer it to another Server it’s Created already on this Server you will Select from this 3 Value

  • FailTask : If the job Found Fail The task.
  • Overwrite : if the job Found Overwrite on it .
  • Skip : if the job Found Skip this job

EnablejobsAtDistention : False = Disable , True = Enable .

After you finish your Configuration Click OK

Transfer Jobs_3

4- now we need to run the task by F5 or Click on play icon to execute the package.

5- Now Check your Distinction SQL Server Agent Jobs you will found the New Jobs you Selected it to Transfer .

I Wish Success To Everyone

Eng. Mostafa Elmasry

Database Administrator