SA can be disable or enabled using following script. Make sure that you are logged in using windows authentication account.
/* Disable SA Login */ALTER LOGIN [sa] DISABLE
GO
/* Enable SA Login */
ALTER LOGIN [sa] ENABLE
GO
Thursday, August 4, 2011
Measure CPU Pressure
CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started.
SELECT
scheduler_id,
scheduler_id,
cpu_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;
Get Query Plan Along with Query Text and Execution Count
SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'
Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold
-- Specify your Database Name
USE AdventureWorks;
GO
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
Find out which queries are running currently on your server
Following script find out which are the queries running currently on your server.
SELECT sqltext.TEXT,req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext While running above query if you find any query which is running for long time it can be killed using following command.
Count Duplicate Records – Rows
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTableGROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
List All Stored Procedure Modified in Last N Days
SELECT name
Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
----Change 7 to any other day value
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7
----Change 7 to any other day value.
Error : 40 – could not open a connection to SQL server
An error has occurred while establishing a connection to the server when connecting to SQL server 2005, this failure may be caused by the fact that under default settings SQL server does not allow remote connection. ( provider: Named Pipes Provider, error: 40 – could not open a connection to SQL server. )
Fix/Workaround/Solution:
Step 1) Make sure SQL SERVER is up and the instance you try to connect is running.
Step 2) Your system Firewall should not block SQL Server port.
Step 3) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.
Step 4) Now follow this KB Article of MSDN depending on your server :
Get Latest SQL Query for Sessions – DMV
How can one figure out what was the last SQL Statement executed in sessions.
SELECT session_id, TEXT
The query for this is very simple. It uses two DMVs and created following quick script for the same.
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
T-SQL Script to Take Database Offline/Online
-- Create Test DB
CREATE DATABASE [TestDB]
GO
-- Take the Database Offline
ALTER DATABASE [TestDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [TestDB] SET ONLINE
GO
-- Clean up
DROP DATABASE [TestDB]
GO
Finding Last Backup Time for All Database
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
Get Query Running in Session
Run the following query to find out what the latest query that was executed in the session.
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (YourSessionID)
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO
Finding Memory Pressure – External and Internal
The following query will provide details of external and internal memory pressure. It will return the data how much portion in the existing memory is assigned to what kind of memory type.
SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressure
FROM sys.dm_os_memory_clerks
GROUP BY TYPE
ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESC
GO
Set autoshrink off for all databases
use master
go
declare @Databasename varchar(128)
declare @String varchar(1000)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
fetch next from Nonsysdatabases
into @Databasename
while @@fetch_status = 0
begin
set @String = 'ALTER DATABASE [' + @Databasename + '] SET AUTO_SHRINK OFF'
exec (@String)
fetch next from Nonsysdatabases
into @Databasename
end
close Nonsysdatabases
deallocate Nonsysdatabases
go
declare @Databasename varchar(128)
declare @String varchar(1000)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
fetch next from Nonsysdatabases
into @Databasename
while @@fetch_status = 0
begin
set @String = 'ALTER DATABASE [' + @Databasename + '] SET AUTO_SHRINK OFF'
exec (@String)
fetch next from Nonsysdatabases
into @Databasename
end
close Nonsysdatabases
deallocate Nonsysdatabases
Wednesday, August 3, 2011
How to Monitor tempdb Use
Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see Troubleshooting Insufficient Disk Space in tempdb.
SQL Server Management Tools
SQL Management Studio for SQL Server provides a complete package for all common SQL Server tasks for database administrators and developers.
DBArtisan is a database administration tool from Embarcadero which aims the maximum availability, performance and security of databases including MS SQL Server
DBArtisan provides powerful GUI editor and wizards for DBSa in order to speed routine tasks while minimizing errors for increades productivity.
EMS SQL Manager for SQL Server is a MS SQL Server administration and database development tool.
Quest Software is being developing a very useful and handy tool for SQL Server administrators. The SQL tool is in Beta phase and MS SQLServer administrators can download free.
This SQLServer tool enables administrators to gather information about the SQL Server installations in their network.
Discovery tool can find the number of SQL Servers installed, can find out how many databases and the size of databases on those deployed SQL Server 's.
Discovery Wizard tool can also bring out the SQL Server versions and the Service Pack levels of the SQL installations in the network.
And what makes this tool unique is that SQL admins can see the users and the network traffic connecting to those MS SQL Server instances and watch this traffic within a period of time.
A must tool for SQL Server administrators especially for those administrators that are new in the company and want to get a summary of the existing MS SQL Server instances in their company network.
Tools for SQL Server - Database Desing and Data Modeling Tools
Embarcadero ER/Studio is a data modeling tool enables database designers document and re-use data assets.
ER/Studio enable database desingners reverse engineering so making it easier to analyze and optimize existing SQL Server databases.
Schema Examiner can be used to validate database design errors, and find out schema errors.
Schema Examiner prepares sql scripts for resolving the found schema problems.Schema Examiner can be used to identify problems like missing relationships between sql tables, or incorrect relationships, disabled triggers, required missing indexes on sql tables, etc.
Tools for SQL Server - SQL Monitor and Database Monitoring Tools
SQL ResponseSQL Response is a sql monitoring tool SQL Server administrators require much. SQL Response monitors the health of the SQL Server instance and the activity on the server. Administrators can build alerts for notification of problems when they occur on the server.
It is easy to monitor sql deadlocks, blocking processes or problems like long running and resource consuming queries.
Zero Impact SQL Monitor is an other tool for SQL Server that is used as database monitoring tools. Zero Impact SQL Monitor enables database administrators to monitor performance of their SQL Server database systems without any affect.
SecureSphere Database Activity Monitoring
Data Comparer for SQL Server can create automatic scripts for eliminating data differences between SQL Server database tables.
SQL auditing tool also reports before update and after update data values as well as change time.
SecureSphere Database Activity Monitoring is a compact tool for major databases like SQL Server for sql monitoring, auditing and reporting.
dbForge Schema Compare for SQL Server is used for schema comparison and synchronization between different sql databases. dbForge Schema Compare enables analyze the differences in database structures of development, test and production databases environments. It can build scripts and propagate the desired modifications to a target SQL Server database.
SQL Server Tools Compare Data and Data Synchronization
Data Sync is a data compare and data synchronization tool. An important feature of Data Sync tool from Spectral Core is that Data Sync can compare and synchronize data between two different database servers.
SQL Data Compare is a handy tools for SQL Server professionals in order to compare data in sql tables and if required synchronize SQL database contents by generating sql DML scripts like insert, update or delete.
dbForge Data Compare for SQL Server enables database workers for easy data comparison and synchronization between different SQL Server databases
SQL Comparison SDK supplies developers an API for reaching Data and Schema comparison tools of Red Gate.
EMS Data Comparer for SQL Server is a powerful data compare and synchronization tool.
Data Comparer for SQL Server can create automatic scripts for eliminating data differences between SQL Server database tables.
SQL Server Backup Tools - SQL Backup and SQL Server Backup Compression Software
SQL Server professionals can find more related information on sql backup compression atSQL Backup Compression Default Option in MS SQL Server 2008 sp_configure Command
for more detailed review of SQL Server backup compression software and SQL Server tools for sql backup compression.
SQL Backup from Red-Gate is a complete sql tool to compress, encrypt and monitor SQL Server backups for SQL Server administrators.
One of the SQL Server tools of Quest Software company is the SQL Server Backup and Recovery Tool "LiteSpeed for SQL Server".
LiteSpeed offers secure encrypted backups and fast sql backup compression method for saving from sql backup time and backup storage space.
It is also possible using LiteSpeed Backup and Recovery Tool for SQL Server to restore individual database objects.
You can also download a 15 day free evaluation and free trial download of this SQL Server Backup and Recovery tool.
For a detailed review on LiteSpeed please refer toLiteSpeed Backup SQL Server Backup Compression Tool.
For a detailed review on LiteSpeed please refer toLiteSpeed Backup SQL Server Backup Compression Tool.
Task Automation Tools for SQL Server
DB Mail is an automation tool not for sending e-mails from most common databases but can manage automation of sending e-faxes, SMS and cell phone messages, etc.
DB Mail is a perfect tool for automating messaging needs of a database application.
SQL Log Rescue is log analysis tool which IT professionals can use for viewing details in SQL Server logs and information on database transactions. DBAs can also use SQL Log Rescue records in order to get lost data back in case you have accidentally delete, or modify data in a database.
SQL Audit Tool
DB Audit Expert is a professional sql auditing tool for SQL Server. System, database and security administrators as well as auditors can use DB Audit Expert as an sql audit tool.
DB Audit Expert enables tracking and analyzing database activity about database security, data access and usage, data creation, data updates or data deletion.
SSW SQL Auditor or SQLAuditor is a tool for big development teams to ensure Quality Assurance. Database designers can trace if best practice standards are applied to the SQL Server application by running SQL Auditor.
SQL Auditor sql audit tool can be assumed as the FxCop for SQL Server development teams.
This tool can find database desing problems and fix them, can identify sql naming problems, can find missing relations, missing primary keys, foreign keys, etc.
ApexSQL Audit is an sql auditing tool from Apex. It can track DML statements and save audit data into an other seperate SQL database.
ApexSQL Audit tool has also command line support.
OmniAudit SQL Server Auditing tool is easy to install and easy to use sql audit tool.
By activating OmniAudit on any database, table and field, database administrators can identify which field in which table has been altered by which user.
SQL auditing tool also reports before update and after update data values as well as change time.
OmniAudit also fullfills the Compliance Requirements of regulations like FDA Part 11, HIPAA, PCI-CISP, Sarbanes-Oxley, etc.
Rename Dabase Name using T-SQL sp_rename
CREATE DATABASE SQLDatabase
GO
sp_renamedb @dbname = 'SQLDatabase' , @newname = 'RenameDatabase'
GO
sp_renamedb @dbname = 'SQLDatabase' , @newname = 'RenameDatabase'
GO
GRANT ALTER TRACE Permission
USE master
GO
GRANT ALTER TRACE TO
GO
USE master
GO
REVOKE ALTER TRACE FROM
GO
GO
GRANT ALTER TRACE TO
GO
USE master
GO
REVOKE ALTER TRACE FROM
GO
Allow a user to run activity monitor without making that user system admin
-- Grant
GRANT VIEW SERVER STATE TO << login name >>
Subscribe to:
Posts (Atom)