Drop Database User who Owns Schema

Scenario :

UserName : GPxTestUser is owner of db_datawriter schema, and we wanted to drop the user

Once u made a user as schema owner (this is a common problem when we create Database user using SSMO, because by mistake everybody check the schema instead of Database Role) , then there is no way to revert back or change the owner of schema from "Database User" window. This can confuse a newbie. Though, I feel it should not be allowed to change the Owner of Schema from Database User Window and it should be done from "Schema Property Window" only. What i would say is this option of choosing schema owner should be removed form "Database User" window altogether to avoid confusion.

When we try to drop a database user who owns some schema from Database the system will throw the following Error :-

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

First you should see the Schemas and the curresponding owners

SELECT s.name SchemaName, d.name SchemaOwnerName FROM sys.schemas s INNER JOIN sys.database_principals d ON s.principal_id= d.principal_id


Drop USER gpxtestuser

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Resolution :-

Alter the Schema Owner to any other Prinicpal; here I am changin owner of DB_Datawriter as DB_Datawriter (earlier it was GPxTestUser)

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [db_datawriter]

Then the user can be droped

To Transfer/Change the ownership of a object to another Schema.

If you want to drop a Schema , first you should transfer the object owned by this schema to another schema otherwise, when you drop schema you will get a messge as mentioned below :-

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'SCH1 ' because it is being referenced by object 'TAB1 '.

Now, before droping the schema SCH1 , the obeject(s) need to be transfered to another schema.

Syntax : ALTER SCHEMA Target_schema_name TRANSFER object_name

The below statement will transfer the Ownership of TAB1 from SCH1 to DBO

ALTER SCHEMA dbo TRANSFER SCH1.TAB1

in the same manner , transfer all the objects to whichever schema you want and then drop the schema.

Dynamic Management Views that can be used to monitor and troubleshoot performance issues in SQL Server.

Add Prefix Sys. to view

dm_exec_cached_plans : Provides information about the query execution plans that are cached by SQL Server for faster query execution.

dm_exec_connections :Provides information about the connections established to SQL Server on various endpoints.

dm_exec_sessions :Contains one row per authenticated session on the SQL Server instance.
dm_exec_query_stats :Provides aggregate performance statistics for cached query plans.

dm_exec_query_optimizer_info : Provides detailed statistics about the operation of the SQL Server query optimizer.

dm_io_pending_io_requests :Contains a row for each pending I/O in the system.

dm_os_loaded_modules : Contains a row for each module loaded into the server address space.

dm_os_memory_cache_counters :Provides a general snapshot of the health of the cache.

dm_os_memory_cache_entries : Enables you to view all entries in caches and their statistics.

dm_os_performance_counters :Lists SQL Server 2005 Performance Monitor counters and their current value.

dm_os_waiting_tasks :Provides information on the wait queue of tasks that are waiting on some resource.

dm_tran_locks :Returns one row for every active request to the lock manager that has either been granted or is waiting to be granted (that is, the request is blocked by an already granted request).

dm_tran_current_transaction :Returns a single row that displays state information of the transaction in the current session.

dm_tran_active_transactions :Provides information about active transactions.

dm_clr_properties :If the CLR is enabled, provides information such as CLR version, directory, state, and so on.

dm_clr_appdomains :Returns a row for each CLR application domain in the server.

dm_clr_loaded_assemblies :Contains a row for each CLR user assembly loaded into the server address space.

Performance Tuning - SQL Server 2005

Few best resources :
SQL Server 2005 Waits and Queues - Performance Tuning
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

Database Mirroring Best Practices and Performance Considerations
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

Why SQL Server 2005 has Performance degrades after upgrade
http://blogs.msdn.com/sqlqueryprocessing/archive/2006/09/26/compilation-time-issues-in-oltp-applications-when-upgrading-to-sql-2005.aspx

A Special Republic Day

My first republic day as a Ex- Sailor. I am no more in Delhi, so I don’t feel that warm and tension. I always believe that if you want to know the significance and true spirit Republic Day it is Delhi the place to be. I still remember, those days when I and Leena (my wife) went first time to watch republic day parade in 2002. On that day we could not make it because of high security though I was in uniform. But in the next year we could make it. The whole function will be around 3 hrs and it is always colorful, vibrant and worth to watch. It is a trailer show of the diverse India. I got a pass of enclosure just opposite to VVIP’s daises. Now all those days are gone. I am not sure I would ever watch republic day parade again from India Gate. I really miss Delhi.

SQL Server 2005 DMVs for monitoring/tuning TEMPDB

In SQL Server 2005 , TempDB plays a significant role in overall SLQ server performance. In earlier versions it was difficult to monitor and troubleshoot the bottle neck.

In SQL Server 2005, you have number of DMVs to see which users are accessing the TEMPDB database , the internal object being used and version Stores size. The DMV are as follows

Sys.dm_db_file_space_usage
Sys.dm_db_session_file_usage
Sys.dm_db_task_space_usage

Note : In SQL Server 2005 these DMVs only applicable to Tempdb

Counters to Monitor Tempdb Row versioning usage

(a) SQL Server:Transaction Object : Version Generation Rate (KB/s)
(b) SQL Server:Transaction Object : Version cleanup Rate (KB/s)

Refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Hide SQL Server Instance in SQL Server 2000 and SQL Server 2005

To hide SQL Server 2005 Instance

In SQL Server 2005 you can hide a specific instance while other instance continue to expose. To hide a instance steps are as follows
1. Open SQL Server Configuration Manager from Prgramfile – Microsoft SQL Server 2005 – Configuration Tool
2. In SQL Server Configuration Manager, Expand SQL Server 2005 Network Configuration,
3. Right Click on “Protocol for ” and select properties
4. In Flag pane , you can find HideInstance with No set. Change No to Yes.
5. No need to restart the service, when you pressed OK, the new setting is immediately applied and new connection will not be able to connect to the instance using SQL Browser Service. You can still connect to the instance by specifiying protocol , Server name/IP address and port number or the named pipe directly

To hide an instance of SQL Server 2000:

1. Open the SQL Server Network Utility.
2. Select the instance that you want to hide. (This is only available on SQL Server 2000 instances). 3. Select TCP/IP from the list of enabled protocols.
4. Click Properties.
5. Select the Hide Server check box.
6. Click OK.
Note : Hide Server Option Cannot Be Used on Multiple Instances of SQL Server 2000
Refer :http://support.microsoft.com/kb/308091

Apply Command : New in SQL Server 2005

Apply command can be used with a table-valued user defied function (UDF) so that the UDF accepts a different parameter value for each corresponding row being processed by the main query. There are two ways to apply, apply command.
(a) Cross Apply :- It is bit confusing because it has a contradictory behavior from name. ie. it operates more like an inner join tan a cross join. The Cross Apply command will join data from the main query with any table-valued data sets from the UDF. If no data is returned from the UDF, then the row from the main query is also not returned. So, its just behave like inner join
(b) Outer Apply :- It will return all the rows from main query , if at all no data is returned from the UDF. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

It is well explained in BOL with perfect example. But thing is that you must know something like this exists in SQL Server 2005.

MCITP SQL 2005 DBA - Yet another mile stone in my SQL journey

I am a MCITP in SQL 2005 track from 6 Jan 07. The exam was no. 70-447 and I scored 918 out of 1000. I was little late to achieve this goal as per my schedule , but at the end of the day I am more than happy now. By getting certified one does not become a master , but it of course prove that he/she has a learning habit and if u have done in the true spirit u will have fare understanding about the tools and the new features of the product. So, I would always recommend to get certified as soon as possible. The exam was not easy as I thought. The question pattern has totally changed. This is my 9 th Microsoft certification, so I took this exam for granted. But when I saw the questions and the new case study section I was sweating. You have to read 4-5 papers of the Case study document and understand the language and the actual requirement. And also each case study section has its own time limit. So anybody going for 70-447 , must know this pattern change and the new case study section.

Total time – 210 min
Total Questions – 66
Pass marks - 700
Total - 1000

Blocked Process Threshold Option – A useful New Feature of SQL Server 2005

SQL Server 2005 introduces a new advanced system configuration (sp_configure) option called "blocked process threshold." We can use this option to proactively monitor blocking and deadlocking instances. You can use sp_configure to set the blocked process threshold to an integer value between 0 (the default) and 86400 (24 hours). If you have set the blocked process threshold configuration value to 15. Now if there is a blocking for over 15 seconds, SQL Server will raise an event that can be captured by using the SQL Server event notification mechanism to perform an action, and the event can also be seen in SQL Profiler. The event is raised every 15 seconds after that until the blocking is resolved.

SQL Server raises the BLOCKED_PROCESS_REPORT event when there is a blocking for 15 seconds and every 15 seconds after that until blocking is resolved. This event can be captured by using the event notification mechanism introduced in SQL Server 2005. The event notification mechanism captures the event and notifies a Service Broker service by placing a message in the queue


Reference : http://msdn2.microsoft.com/en-us/library/ms181150.aspx

Insallation of SQL Server 2005 from Local Folder

If you are installing SQL Server from CD media dumps which is copied into a local/network folder (in SQL Server 2005 Installation CDs are Two where as in DVD media only one DVD) , the folders should be named as Servers and Tools for CD1 and CD2 respectively. Otherwise , client components and SQL Server BOL will not be installed. There will be error if you are not named the folder as above mentioned.

Ref : http://msdn2.microsoft.com/en-us/library/ms144259.aspx