Saturday, June 19, 2010

Error 3702 Cannot Drop Database Because It Is Currently In Use

Saturday, June 19, 2010  Nareshkar Pakanati  No comments

While running below query I am getting error.

USE AdventureWorks;
GO
DROP DATABASE AdventureWorks; 
GO 

ERROR:
Msg 3702, Level 16, State 3, Line 2
Cannot drop database “AdventureWorks” because it is currently in use. 

Workaround / Solution:
Step 1: Check if any open transactions through SP_Who2 

Step 2: If any active connections then kill. 

Step 3: Use below script for doping the AdventureWorks database instead of above script, use master database first and then drop the database. 
USE MASTER;
GO
DROP DATABASE AdventureWorks; 
GO


Cannot drop database because it is currently in use, Cannot drop database “DataBaseName” because it is currently in use. Cannot drop database "MyDatabaseName" because it is currently in use. "Cannot drop database because it is currently in use". How to force drop database. Problem Dropping Database. Cannot drop database “DbName” because it is currently in use. how to drop database which is in use.

Read More

Wednesday, June 16, 2010

SQL Server ERROR 15281 Ad Hoc Distributed Queries Component Is Turned Off

Wednesday, June 16, 2010  Nareshkar Pakanati  No comments

Usually we get this kind of error while retrieving data from other source like MS Access, Excel, etc.

Error :
Msg 15281, Level 16, State 1, Line 3 
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online. 

Fix / Workaround:
For this you have to enable Ad-hoc remote queries components through SQL Server Surface Area Configuration Manager or T-SQL SP_Configure. Please follow below for enabling through GUI.

In SQL Server 2005 you can use Surface area configuration manager, if it is in sql server 2008 or higher version use facets, from 2008 onwards they have replaced Surface are configuration manger with Facets. 

Step 1: Open SQL Server Surface Area Configuration from Start Menu. 

Step 2: Select Surface Area Configuration for Features. Select the Ad-Hoc Remote Queries component and check the box "Enable OPENROWSET and OPENDATASOURCE support".


Click the OK button to apply the changes. Close the Surface Area Configuration window.
Surface Area Configuration for Features. ad hoc distributed queries Server Configuration Option. What are the security risks with enabling Ad Hoc Remote Queries. What are the security risks with enabling Ad Hoc Remote Queries. Running Ad Hoc Queries on Remote SQL Server 2008 or 2012 or 2005 or 2008 R2. How to enable the use of 'Ad Hoc Distributed Queries'. AD Hoc remote quiers. Msg 15281. Level 16. State 1. Line 3. SQL Server blocked access to STATEMENT. xp_cmdshell enabled but still throws error. Excel Import to SQL Server using Distributed Queries. Getting execute permission to xp_cmdshell. Running Queries on Excel Spreadsheets using SQL Server 2008. OLE DB provider.

Read More

Thursday, June 10, 2010

How to View All Indexes in a Table

Thursday, June 10, 2010  Nareshkar Pakanati  No comments

Through below simple Stored Procedure which it can display the name of Indexes and the columns on which indexes are created.

USE AdventureWorks;
GO
EXEC sp_helpindex 'HumanResources.Employee'
GO 

Above Stored Procedure will return following information.

A new and improved sp_helpindex. sp_helpindex sql server 2005. sp_helpindex sql server. sp_helpindex sql server 2008. sp_helpindex sybase. use sp_helpindex sql. sql sp_helpindex example. sp_helpindex permissions. How do you execute the sp_helpindex system stored procedure. Stored procedure to view index information. Should I call sp_helpindex for each table and store the results in a temp table. Modified sp_helpindex SP to show all indexes for all tables. in sql server 2012. in sql server 2008 R2. in sql server 2005.

Read More

Tuesday, June 8, 2010

The DBA Daily Checklist

Tuesday, June 08, 2010  Nareshkar Pakanati  No comments

Main Checklist for Initial Setup Of The SQL Server:
1. Automate all possible jobs and maintenance plans on the server for things like database backups, integrity checks, automatic shrinking, transaction log backups, etc. You could do this by creating Maintenance Plans in SQL Server, which would automatically generate and schedule the required jobs.
 
2. Install SQL or Database Mail on all your production servers and set it up to send you notifications on your email (or cell phone or pager - whatever is convenient).
 
3. Through SQL or Database Mail, set up email notifications for all the jobs and maintenance plans on the production server - for every database. Also set up email notifications for you to be notified in case of a severity alert - like file growing large and thus reducing disk space, etc.
 
4. Always keep a script of the functional database schema in a secure location on the network. This comes in handy if you need to know the structure of the database in production or you need to recover a database, which does not have any backup left.
 
5. Set the MSSQL and SQL Server Agent services to Auto-start when the server starts.

DBA Monthly checklist:
1. Make a list of all the sa passwords for each server and save it in a secure place.
 
2. Make a list of all the passwords for each login created on the production boxes.
 
3. Save the SQL Servers’ and Windows' configuration information in a secure place. This information is needed to rebuild an NT & SQL Server box in case of a disaster.
 
4. Perform a test restore of a database backup. This is done in order to prepare for unforeseen situations.
 
5. Save information about any changes made to a server - hardware or software.
 
6. Maintain system logs in a secure fashion. Keep records of all service packs installed for both Microsoft Windows NT Server and Microsoft SQL Server. Keep records of network libraries used, the security mode, sa passwords and service accounts.
 
7. Assess the steps in recovering from a disaster ahead of time on another server, and amend the steps in your Disaster Recovery Document, as necessary to suit your environment.
 
8. Audit Database Access: You should periodically perform a review of who has access to your production databases and what type of rights they possess. Doing so can prevent unauthorized access to production data.\

DBA Daily checklist:  
1. Check the connectivity of each server over the network. You could do this by pinging the SQL servers twice a day or by clicking the server’s name in your Enterprise Manager and seeing if it is able to connect.
 
2. Check whether the services are running. For each server, go to its SQL Service Manager and check whether the SQL Server Agent and MSSQL Server services are running (showing a green light). If not, start those services. (You could also check these from the Control Panel or Enterprise Manager).
 
3. Check whether the scheduled tasks on the production servers are running normally. You could check this from the Enterprise Manager of each server or your email (if you have set up SQL Mail to notify you).
 
4. Check the hard disk space available on the SQL Servers. If system drives run low on space, they crash.
 
5. Check all the database and transaction log space on each server. If the database or transaction log space runs out, the transactions will fail.
 
6. Check NT event Logs for any error messages. SQL Server writes to the NT application log in case of application errors or SQL errors and also warns you before a problem becomes critical.
 
7. Check SQL Error Logs for any errors occurring within SQL Server. SQL Server warns you through these logs before the problem becomes critical.
 
As needed Checklist:
1. Run disk defragmentation utilities: You should periodically run disk defragmentation utilities on your server's. A high degree of hard disk fragmentation can lead to decreased hard disk performance. 
DBA Checklist. DBA Daily Checklist. DBA Daily Checklist free download pdf. Checklist Application Report for SQL Server Database Administration. SQL Server DBA Checklist. The DBA Checklist. DBA Morning Check List. Database Specifications Checklist. Daily DBA Checklist. A Helpful SQL Server DBA Checklist. Oracle Database Administrator Checklist. The DBA Daily Checklist. Database Administration. sql server 2008 dba checklist. sql server 2012 dba checklist. sql server 2005 dba checklist. DBA Checklist Updated. DBA Checklist Latest. day to day dba work in real environment. day to day dba tasks. day to day dba responsibilities. day to day database admin responsibility. Security Checklists.

Read More

Friday, June 4, 2010

How To Find SQL Server Up Time

Friday, June 04, 2010  Nareshkar Pakanati  No comments

Here is the query to Check SQL server UP time Through TrSQL. 

USE master;
GO
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END

 The output would be like below screen-shot:

SQL Server Up Time. How To Check SQL SERVER Uptime Through T-SQL.  How to determine instance uptime. T-SQL to get the SQL Server Service Start time. Determine the Uptime of a SQL Server Instance. How To sum up time field in sql server. Using the UPTIME Utility to Monitor Servers. What's the current uptime of SQL Server?. Tracking Uptime. Continuous Uptime for SQL Server. Server Monitors from up.time. Get SQL Server last restart time and uptime. Uptime Assurance for SQL Server Databases.

Read More

Thursday, June 3, 2010

SQL Server Order Of Execution Of The Query

Thursday, June 03, 2010  Nareshkar Pakanati  No comments

Here is the SQL Server Order Of Execution of the query.

1.  FROM clause 
2.  WHERE clause 
3.  GROUP BY clause 
4.  HAVING clause 
5.  SELECT clause 
6.  ORDER BY clause
Logical Query Processing Phases. SQL Query Order of Operations. SQL query to return rows in random order. Order in descending order. In Query "Order By". sql server query order by date. group by query in sql server. order by query. order by in sql server. Query Builder for SQL Server Ordering. Query Performance Tuning. Query Plan Sort. SQL Server query optimizer. Order Of Execution of the query. in sql server 2005. in sql server 2008 R2. in sql server 2012.

Read More