Friday, December 7, 2012

The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)

The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)

This is the solution type of solution I found on the websites. Question is does it work, I dont't know because it didnt work in my condition so I really have no idea.


To resolve this issue:
1. After starting Management Studio 2008, select Databases in the Object Explorer once.
2. If the Object Explorer Details are not already showing, select View from the toolbar then select Object Explorer Details. 
3. Right click on any column header and make sure that “collation” is not checked. 

4. Refresh the database list by clicking the Refresh button located at the top of the Object Explorer Details panel.  You should have now have full access to your database at this point. 


Enjoy Have Bless day with MS SQL.

Could not Obtain Exclusive Lock On Database


Some time this error appears and First things comes in our mind is What has possibly gone wrong, I normally see this error when I am restoring the databases or overwrite a a dump database.

Since SQL is also a software and like every software it has its own way of operating and connecting with other entities. Let solve this issue, what steps will take care of this error.

pic


1)  If the above error appears, close your Studio Management Console. Because sometimes its Studio Management that is holding a old connection. First and normally it fixes the problem.

2)  Second reason may come because of "Auto Close" property if turn "ON", Go in DB properties and turn off this property.

3) Check if any other window is open holding a connection to the specified DB. 


Use below script to turn "AUTO Close" off


SELECT DATABASEPROPERTYEX('Your DB' , 'IsAutoClose' ) AS [AutoClose]
GO
ALTER DATABASE [Your DBSET AUTO_CLOSE OFF
GO

Thursday, December 6, 2012

Some Daily Database Tips

Some Useful information for daily life DBA, Well I writing this post just to include things we as DBA need in everyday life you can find more information on other blogs and site this is something for information for new DBA guys. There are couple of commands that  can help us. Let looks us at commands. and Why we use them.

1)    sp_who

        Provides information about current users, sessions, and processes.
      sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]
          ACTIVE excludes sessions that are waiting for the next command from the user.


2)    sp_who2

Shows all the sessions that are currently established in the database.
sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of   sp_who2 only show the row or rows of the executing SPID.

3)    Kill

Terminates a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete.

4)    Sys

Sys is special system table as understandably by its name. What is purpose of Sys? Sys provide various functionalities like all table names in a database, schema level information , object and their information, etc. In short Sys is key player for for many system and database which you can find over here. Lets looks at some below.

5)    Sys.Table


Returns a row for each table object, currently only with sys.objects.type = U.

6)    Sys.Objects

Contains a row for each user-defined, schema-scoped object that is created within a database.

7)    Information_schema


Information schema is another SQL built in table, it has  schema level information.
An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

Tuesday, October 16, 2012

WAMP Server Not Starting

For more than 3 hours I would like to put my head in the wall because WAMP server simply refuse to get online and I had no idea hat going on. So here comes action open google search and found out so many answers and guess nothing worked. I need some thing that can solve the issue I re-install the WAMP disable my IIS but no progress Finally I managed to make the WAMP work. And I will List you the steps and the some tips if that will help you. I am sure that will help and yeah we'll track down that rascal stopping WAMP to go online :)


First Thing,

1) WampServer > Apache > Service >Test Port 80. This give me the following message: 

Some Message will pop up like this and show you which app is using port 80
------------- Your port 80 is actually used by : 
Server: Microsoft-IIS/7.5 ------------- 
2) Open the Application close it and make sure when they start they start after WAMP.

3) Make Sure SSRS(MS SQL Reporting )  is shutdown if you are using WAMP . 

4) Disbale IIS if nothing works because IIS and WAMP are sometime conflicting with each other


UPDATE : 02/06/2013
Ok I am writing an update for the same post because this time WAMP server again give me a pain in the @$$. But this Time nothing stated above worked. So I decided to trouble shoot more because I always believe one or other way these things will work. So Get Ready If the above steps fail in your situation also.

5) One thing is clear and must be remember problem is not your computer, its the software battle going inside the PC makes conflict and the better one win. So Remembering this strategy. I Focus my attention to Web products.

6) As I was scrolling down the Services, I found web deployment agent, And I disable that Guess My WAMP started to work like a charm. So Consider making it disable if WAMP is having problems.

P.S. If WAMP still not start then Take down Web services and and restart wamp again and again. One time you will see it must start all cost.

Take Care Guy if I found more about WAMP I will update over here

UPDATE : 08/16/2013
All of the above did'nt work so I finally banged my head into the wall. Nothing above work.
Before I finally decided to move to Linux I gave last shot and open the services, Promise to my self I'll close all services one by one it should work something is holding it.

1) I close SQL services
2) World Web publishing 
And Guess it worked.








Thursday, July 19, 2012

Execution cannot continue as the language dependent resource file

Arrrrrrr!! Yes that the first reaction came from my junior DBA. And I think it necessary as he is learning. Ok what to do now. Solution is straight and simple.

As you receive this error you will be prompted for  location where the SQL cannot find the required file.
Download those files easily available put in the folder specified and you are good to go.

Is nt this simple and straight? So Join our mailing for small but effective techniques.

DTS Packages in SQL 2005/2008

 I was given a task to import DTS packages in SQL 2008 which were imported successfully done.  However when my team tried to edit them, they would not open.   --The following error was displayed:  'SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature.  Microsoft.SqlServer.DtsObjectExplorerUI) .


This is so frustrating DTS package is imported and not opening I searched the web and didnt find much except people just trying to give their advice's for NONE that worked for me.


But afterwards I found an awesome article that can actually help many of the developer looking to import and convert old DTS into SSIS packages. But it didnt work well so I need to fix more things but that didn't mean the page was crap no it was good and gave alot of information. My blogger is having issue not uploading the link so pasting the information.







1) Install Sql Server 2000 DTS Designer Components using the following link

2) Install Sql Server 2005 backward compatibility components available with Microsoft SQL Server 2008 Feature Pack using the following link depending (x86,x64 and IA64)
3) We need to change the Environment Variable for the Server so that c:\Program files(x86)\Microsoft Sql Server\80\Tools\Binn is ahead and all the sql 2008 paths comes at the end and we can verify it by opening the command prompt and firing the command  “path”
C:\>path
PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\PROGRA~1\CA\SHARED~1\SCANEN~1;C:\Program Files\CA\eTrust Antivirus;C:\Program Files (x86)\Windows Imaging\;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\
OR
If you are not able to change the path Environment variable you can follow the following steps
Manually Copy the following files
%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll
To the following destination respectively
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlsvc.rll
To make the DTS designer work under BIDS for SQL Server 2008, user will need to manually copy the following files
%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll
To the following destination respectively
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\semsfc.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlsvc.rll
4) Run the Repair for the sql server 2005 backward compatibility from Control Panel, Add/Remove Programs, Change
And now you should be able to open the DTS packages under SSMS.



For Trouble shooting if you have further problems Click this post 
 

Tuesday, July 17, 2012

T SQL implementation of Database Mirroring

So how to implement T SQL for Database implementation.


Only writing the code will not get you easily understand the concept lying between the using SSMS and TSQL. So let us write and analyze the code step by step.
For this I will recommend first to use SSMS and note the steps.
First we need to take full back up and transaction log of principal and restore to mirror. For this post
Please click here.


First we need to configure Mirroring End points. These End points are more like socket where we want our server to listen to this specific port in concise words.




1)     /****** Object:  Endpoint [Mirroring******/
CREATE ENDPOINT [Mirroring]
                AUTHORIZATION [ALITEST\sqlmirror]
                STATE=STARTED
                AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
                FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

Now Let us see what I have written,
First line indicate creation of new end point name mirroing.
second line indicate user authorization to use this end point.
third line indicate server is allow to listen this end point.
Fourth line indicate parameters for the end point more like configuration.
Fifth line we indicating this end is for mirroing and role can be set to partner, witness but in this case I set all.

Same way create end points for all the server i.e. mirror and witness also





2)     /****** Object:  Endpoint [Mirroring******/
CREATE ENDPOINT [Mirroring]
                AUTHORIZATION [ALITEST\sqlmirror]
                STATE=STARTED
                AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
                FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO



3)     /****** Object:  Endpoint [Mirroring]   ******/
CREATE ENDPOINT [Mirroring]
                AUTHORIZATION [ALITEST\sqlmirror]
                STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
                FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO


Now one Last step as noted in the SSMS.
This step only tells the servers who is who i.e. who is principal who is mirror which one is witness.

Setting Principal to be mirror partner 
*** Run this on principal first.
Alter Database <DB name> set partner='TCP:\\Servername'

Setting mirror  to be  Principal  partner 
*** Run this on  mirror .
Alter Database <DB name> set partner='TCP:\\Servername'


Setting principal to be  witness partner 
*** Run this on   principal  .
Alter Database <DB name> set witness ='TCP:\\Servername'

And if all goes well You have successively started mirroring via TSQL

P.S Run all the script separately on respective servers or this will fail.



Monday, July 16, 2012

Implementation of Mirror Database Using SSMS

Implementation of mirror database consist of special set of step that enables the SQL to perform mirroring.
In this post I would be discussing the Mirror implementation from SSMS which is easy and in our next post we discuss about T SQL implementation.

Steps:


1. Take full full back up of principal database using right click -)Task-)backup-) full
2.Take transaction log back up of principal database using right click -)Task-)backup-) transaction log
3. Open Mirror Server right click  Mirror  database-)Task-)restore-) full
4. Open Mirror Server right click  Mirror  database-)Task-)restore-) transaction logs
5.Open Principal server go Task-) Mirror -) configure-)Set the principal-)Set the mirror-)Set the witness (if you want automatic fail over to mirror database in case principal database die)

And yes above Five steps are enough to start your mirroring using SSMS.
In our post we discuss about T -SQL implementation of mirroring

Sunday, July 15, 2012

High Availability Solution in SQL

Many times a novice DBA thinks what to do when his seniors and manager just make him struck with high availability solutions and he is given the task for ultimate solution design . This post normally deals with my situation as I join the company soon three experience DBA left and I was one of replacements for the expert DBA but having no experience is another blessing :) You learn a lot and feel the responsibility thinking no one is gonna pamper you.  My manger told me to work on a high availability solution which was not available in our enviroment. My manger and other collegues were more of infrastructure guy so I start searching a good solution.

So, following are the solutions pretty common all over the web. But million dollar question which solution fit your environment the answer to this question belong to only one group or one person that may be you or that may be the one dealing with database him/herself.

I am not going in detail of what they are. There is plenty of Microsoft online documentation for that I shall be giving a briefly going over about these and then taking scenario's in which I used these technologies for better explanation.

High Availability Solutions for SQL
1) Mirroring
2)Clustering


P.S. Please click the Mirror link to go and access the information

Saturday, July 14, 2012

Database Mirroring


Database Mirroring

Mirroring was intorduce in 2005 by Microsoft. It is a low cost low maintenance solution when I say low maintenance it only means it has degree less complex than the cluster. In Mirroring, Principal database is duplicated exactly to a Mirror which is clear from its name.

Let consider pros and cons for which some people like mirroring and some don't.

Pros:
1. It easy to implement.
2. It can ensure no data loss (if using High Safety mode)
3.It provides a secure copy of database separate from the production so if production goes down Mirror database kicks in immediately without using time for manual restoration.
4.it provide three modes of operations. Asynchronous , Synchronous without witness ,Synchronous with witness.

Cons:
Most people think Mirror Server is actually a waste of resource. Reason because it waiting for principal server to die malfunction  or gone for maintenance and then it can start. It something more then standby server and database.

Asynchronous : Principal database commit data without waiting for mirror database to commit. Data loss chance present.

Synchronous without witness : Principal database does not commit data before the mirror database. Therefore principal database waits for mirror database to commit first. No Data loss. Manual fail over to Mirror Database

Synchronous with witness:Principal database does not commit data before the mirror database. Therefore principal database waits for mirror database to commit first. No Data loss. Automatic fail over to Mirror Database.

Implementation of mirror Server will be discussed in the next and above will be linked with the post.

Friday, July 13, 2012

SQL Full Back ups SSMS and T SQL


Today I was visiting my professor in the school where I met a young guy who was looking to go and make career in database technology. He was not familiar with the SSMS  in depth and requested if I can teach him basics of T sql scripts and some normal operations of SSMS. As Below I was about to write the T sql script for backup that I teached him then I though it would be nice to share the SSMS backup also. So initially I will showing the SSMS backup and then follow by T sql script back up.

Backup Using SSMS
Right click the database -> task->backup->select the database browse the database file from it path and leave all worries to SSMS for restoring.
P.S. I am not discussing any errors right now and everything goes smooth you will find a back up of the database on the mentioned folder.






Back up Using T- SQL:
I am writing the most basic script to back up database reason to just to mention a basic script is to make new people familiar with the T sql and it operation not to make them confuse in ocean of  TSQL puzzles. Once you grab the Idea you would be making more complex scripts.

Backup Database <Database Name> to disk=N'Specify the location'

The italic words are keywords and must be written as they are.

One important point SQL language is not a programming language  it a Query language but it does not mean it does not uses any programming concept what I have learn the different its not as flexible as programming language but if you know how to use the SQL and Programming this fusion makes you an excellent programmer.




P.S. For New DBA's Please keep your recovery model to Full until you earn enough experience and knowledge for the other two models.

Thursday, July 12, 2012

SQL Back ups Checklists

A challenging question for expert DBA and a nightmare for invoice. I would say it something needed to be taken care precisely because the whole company is dependent on the DBA guys if something goes wrong. And top of that I got responsibly to design a whole new Database including EDI, maintenance , hardware , monitoring and software required this is only side it also include solution like clustering and mirroring. We will  keep out attention to the maintenance plan and I mean simple but a powerful plan that can handle a lot of the burden.

Here what, I implemented. This is only basic version. Its something invoice DBA would like to go with since it involves more GUI and less T Sql but my next post I would emphasis more on T Sql scripts. Since every DBA should know the main power and flexibility being offered through the script .
But for now let get going with SSMS and GUI stuff over here.



It is one of the most important tasks. Backup plan consist of many small sub tasks.

1.      Full Backup:

A full back up of the databases is taken daily at  some specific tme . An Alert will send to the concerns for successful completion or failure of the task to execute.

2.      Differential Backup:
A differential back up will be taken after 1 hour.  An Alert will send to the concerns for successful completion or failure of the task to execute.

3.      Transactional Log
A Transactional log will be taken after 10 minutes. An Alert will send to the concerns for successful completion or failure of the task to execute.

4.      Check db integrity

Database integrity need to be check daily for all databases (Needs to run off time only since this consume a lot of time and affect the performance).

5.      verify your backups

Backup of all databases need to be verify for integrity and consistency.

6.      Rebuild index

Rebuild Indexes will run once a week on Saturday morning if required. This will not be weekly activity but depends on the need of fragmentation which will kick in as soon as fragmentation level goes up to 30%. The percentage figure comes in as discussed in most forums where expert DBA gave their advice.

7.      Reorganize index

Rebuild Indexes will run once a week on Saturday morning. This will not be weekly activity but depends on the need of fragmentation which will kick in as soon as fragmentation level goes up to 30%. The percentage figure comes in as discussed in most forums where expert DBA gave their advice


8.      Update statistics

Update statistics will take place daily with the regular maintenance plan. This will increase query performance for the last saved data.

9.      Backup History Cleaning

Back up history cleaning will only run once a week and clear the history one weeks before the current week.

10. Maintenance History Cleaning

Back up history cleaning will only run once a week and clear the history one weeks before the current week.

11. Third Party Software

Back up taken by the third party software daily at  some specific timeVerify integrity and Back up taken by the third party software.

12. Monitoring Reports

Report covering different aspects of disk utilization, SQL IO and performance monitoring must be email every morning at some specific tme

13. Alerts

Alerts are setup for every job being completed or fail in the maintenance plan.
These alerts will come in the shape of Emails. 



Above is the maintain check list I perform. These all can be done via SSMS which makes it easy. Now most of the people thinking we almost got all the things answer is "NO" at various times you need custom scripts to go ahead and solve the issues or some solution only specific to current environment.

Do share your opinion it is valuable to me and how I can improve my posts to help a lot of people

Wednesday, July 11, 2012

SQL DATABASE MODES OF OPERATION


SQL Server database following states but I will only write little about the first 4 as these are most occur scenario's
1)ONLINE (Available – online for data)
2)OFFLINE
3)RESTORING
4)RECOVERING
5)RECOVERY PENDING
6)SUSPECT
7)EMERGENCY (Limited Availability)

1)ONLINE (Available – online for data): Database is operational and live.
2)OFFLINE: Database is not available
3)RESTORING: Database is alive but not available for any outside operation. This states normally shows up when we leave the DB in norecovery mode.
4)RECOVERING:Database is alive but in a process of getting recovered.

SQL:Msg 3117, Level 16, State 1, Line 7 The log or differential backup cannot be restored because no files are ready to rollforward.


I was recently trying to perform a restoration which includes a full database backup and transaction log to be restored on mirror server. and I came up with this little interesting problem
Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to roll forward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I am using SSIS package to deploy all of this for specail job to be run on mirror server and now we were restoring back. The solution is very simple
restore database <DB name> from disk=N'Your Location' with norecovery.
restore log <DB name> from disk=N'Your Location' with norecovery.
And Yes its done

Popular Posts