Archive for ‘SQL Server 2008’

December 8, 2013

SQL Database State stuck in Restoring

Suddenly one of the production SharePoint site in one of our client site was down. When tried to browse the site was getting the error “Cannot connect to the configuration database. So i logged into the database server and was able to see the following: probsOne of the database was get stucked in the restoring mode. The rest of the databases was in the mode of restoring. I was waiting for more than one hour to let the restoring process get finish and unfortunately nothing happened. 😦

So the resolution is to:

Execute the following command in any case if your database get stucked at the time of restoring:

RESTORE DATABASE [DATABASENAME] WITH RECOVERY;

Once i execute the above i was getting the following error:

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database ‘kiainternet01’ because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

i didn’t aware the above database is configured for mirroring. So i had to switch off the mirroring temporarily using the following command:

ALTER DATABASE [DATABASENAME] SET PARTNER OFF;

upon that executed the following command:

RESTORE DATABASE [DATABASENAME] WITH RECOVERY;

finally the databases were online :

final

October 27, 2013

SQL Server Client Network Utitily

If you are working with a number of SQL Server instances or have really complex server/instance names, its good to use aliases with easy naming conventions. The same can be used to connect to SQL Server instancess. For you to create these aliases you can use the SQL Server Client Network utitily programs (cliconfg). Aliases would secure the real instance names of the SQL Server in any case if its exploited by an external hacker.

cliconfg

Launch “cliconfg” by Start > Run > CliConfg. Then go to Alias tab and click the add button to add the alias.

October 1, 2013

Restore-SPSite : 0x80070003

I was restoring a SharePoint site from a lab environment to Production.  This is one of the frequently used powershell commands that we use in addition to the Backup-SPSite.

Restore-SPSite http://ggvstg01:12160/discussionforum/GeneralDiscussi

-Path C:\Users\user\Desktop\Backup\GeneralDiscussion.bak –Force

Come on man, was worried to see the below error.

**Restore-SPSite : 
<nativehr>0x80070003</nativehr>
<nativestack></nativestack>
At line:1 char:1
2

So started to search on this error hence this is the first time I saw this error. Some posts says its related to managed path issue, advice us to check the managed path in both the environments. I checked, so in my case its nothing to do with the managed paths Hence the site had only one root site collection. In some post it says the SharePoint versions are different. I thought this might be correct hence in my lab environment it was the SharePoint enterprise version with the trial license. But in production it was the SharePoint Enterprise. Suddenly I thought of checking the SharePoint logs and the event viewer. I couldn’t see any specific error in the Share Point logs. Then moved to check on the Event Viewer. Ohhhh man, it was something related to the login which I used to execute the power shell command “insufficient SQL Database permission for user named domainaame\username”.

3

Finally logged into the SQL Server and applied the above said user the “sysadmin”permission and executed the restore command without any issue. 😀

February 20, 2012

SQL Server 2005 Management Studio : Cannot parse script.

I was trying to run a script which i got it from our one of live server. So as usual i tried to run the scripts (file size 112 MB) which i got with the data. Ohhh what the hell, was getting the error  “Cannot parse script.” 😦


was finding a way to sort out this matter. As per that in some of the forum posts it says the SQL Server Management studio is having some limitations with the Query Parser. Just refer this post for more details.

Finally found out we can execute using the sqlcmd prompt as below:

you can find the SQLCMD.exe in the following path:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

and after that execute the command as below:

sqlcmd -S YOURSQLSERVER\INSTANCENAME -d DataBaseName -i C:\Your Script.sql

By the way the SQLCMD also uses the usual SQL Server 2005 query parser. So it means the above mentioned error is not related to the SQL Server 2005 Parser instead its related to some bug in SQL Server 2005 Management Studio. 🙂

August 21, 2011

SQL Server 2008 Evaluation Edition Expired

Once you upgrade an existing SQL Server 2008 evaluation edition to any of registered version you will prompt with the following error when you tried to open the Management Studio.

There is a workaround to sort out this issue : its for your to hack registry keys.

Set CommonFiles registry value to 3 in HKLM\SOFTWARE\\Microsoft\Microsoft SQL Server\100\ConfigurationState Registry.

After that you have to reinstall (if the tools are not installed currently) or run edition Upgrade.

This is identified as a known issue where SQL Server Management Studio is not upgraded when you do any of  SQL Server 2008 edition upgrade.

Reference.

version and edition upgrades.

April 25, 2011

Microsoft SQL Server Sample Databases

you can download the sample databases for SQL Server from codeplex.

http://sqlserversamples.codeplex.com

July 14, 2010

Failed to connect to the SQL database ‘SSODB’ on SQL Server

When you tried to configure BizTalk after the successful installation, if you prompted for an error like the one mentioned above, Execute the following command in Visual Studio command prompt.

explore to the following folder:

C:\Windows\Microsoft.NET\Framework64\v2.0.50727>

and run the following command :

RegAsm.exe “C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSQL.dll”

Note : As i am using the Windows 2008 R2 Server, i had to explore to the Framework64 folder.

image

March 11, 2010

Installing a SQL Server 2008 named instance – Windows Server 2008 R2

Run the setup from your SQL Server 2008 CD

clip_image002

clip_image004

Click Installation tab and click New SQL Server Stand Alone installation or add features to an existing installation.

clip_image006

clip_image008

clip_image010

clip_image012

clip_image014

clip_image016

Accept the licensing terms.

clip_image020

clip_image022

Give a name to the SQL Server instance.

clip_image024

clip_image026

clip_image028

i am not going to install this particular instance in mixed mode hence i have selected the windows authentication mode. Setup wont allow you to go to next step unless if you didn’t specify a administrator account. I have added the current user credential as one of the administrator account for the SQL Server.

clip_image032

clip_image036

clip_image038

clip_image040

clip_image042

clip_image044

clip_image046

clip_image048

clip_image050

http://support.microsoft.com/kb/955499

http://support.microsoft.com/kb/956173

March 10, 2010

Uninstalling SQL Server 2008 Instance

please follow the steps mentioned below to uninstall a named instance of a SQL Server 2008. For the reference of others I have published the screen shots to help you guys in some way. Please make sure before uninstalling that none of the SQL services are not used by other applications.

Go to the control panel => Programs => Programs and Features

clip_image002

Browse to SQL Server 2008 and click the Uninstall/Change button above.

clip_image004

Click continue when a prompts comes and make sure that no one is logged into the server.

Click Remove and please follow the screen shots as mentioned below.

clip_image006

clip_image008

clip_image010

Select the SQL Server 2008 instance to uninstall.

clip_image012

Select the features belonging to the instance that you are going to uninstall and keep the shared features.

clip_image014

clip_image016

clip_image018

clip_image020

clip_image022

At the time of uninstallation process I had problem with uninstalling the reporting services and the error was:

“An error has occurred: Access to the path ‘<Path of perf-ReportServer-rsctr.dll>’ is denied”

Please refer this link to resolve the error:

To resolve this issue, close or stop any process or application that may be using the Report Server performance counter, and then try to uninstall Report Server.

http://support.microsoft.com/kb/956173