Executing CHECKDB on all user databases via TSQL

Run below Query. And once the query executes completely, go through the SQL Server Error log to see the results.

Read and filter SQL server error logs using TSQL

Command:

Parameter 1: To specify the errorlog number. Starts from 0. 0 = current, 1 = Archive #1, 2 = Archive #2

Parameter 2: Log file type. 1 or NULL for Error log and 2 for SQL Agent Log

Parameter 3: Search string

Parameter 4: Second search string further refine your search. Continue reading “Read and filter SQL server error logs using TSQL”

Check SQL Authentication Login Account Password

Did you come across any situation where you wanted to make sure that the password you set for a SQL account is what you really wanted? Below is how you can validate the password for a particular account. With this methord, you can find out the forgotten password for a SQL account without causing any audit failures and by not changing any security settings.

SELECT name FROM sys.syslogins WHERE pwdcompare ('SomePasswordWhichYouKnow',password) =1

When you run the above query, you will get the list of accounts which has password set as SomePasswordWhichYouKnow.

This way, you can figure out what password was set while creating an account (SQL authentication account)

Error while executing a sql job “The EXECUTE permission was denied on the object ‘sp_start_job’,database ‘msdb’,schema’dbo’.

SOLUTION:

1) Go to MSDB– Security –Users — UserName — User Name Properties — Assigned the ‘TargetServerRole’ in msdb

2)     Run the query

select pr.name, dp.permission_name, dp.state_desc

 from   msdb.sys.database_permissions dp

 join   msdb.sys.objects o on dp.major_id = o.object_id

 join   msdb.sys.database_principals pr

 on dp.grantee_principal_id = pr.principal_id

 where  o.name = ‘sp_start_job’

 

3)     Grant  Execute permission to concern user to SP_Start_Job and SP_Stop_Job and it resolved the issue.

Unable to install Windows Installer MSP fileExit Code Returned: 11032

SYMPTOM

Service Pack 4 for SQL Server Database Services 2005 ENU (KB2463332)’ could not be installed.

ERROR

  • ————————————————————————————————

Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 2221
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log
Error Number              : 11032
Error Description         : Unable to install Windows Installer MSP file. fileExit Code Returned: 11032.
————————————————————————————————

SOLUTION:

1)     Go to the location.


Continue reading “Unable to install Windows Installer MSP fileExit Code Returned: 11032”

Question: Green arrow missing in Object Explorer Window in SSMS

Question

After connecting to SQL Server using SQL Server Management Studio, I am not able to see the green play arrow on the SQL Server DB icon before the instance name. It just shows white circle.

Resolution

Make sure that the Windows account with which you opened SQL Server Management studio have got admin privilege on the operating system and can retrieve service running status.

In Windows 2008, Even if your account has admin privilege on the OS, you need to run SSMS (for SQL Server 2008) and sqlwb (SQL Server 2005) from command prompt opened with “Run as Administrator”.

Tip: SQL Server Single user mode in Windows 2008 Cluster

At first I thought starting SQL Server in Single user mode is easy in Windows 2008 fail-over cluster. But I was wrong, If I just add ‘;-m’ in start-up parameter and starts it, the SQL server will start in single user mode via cluster. But we wont be able to log in. The cluster service logs in before us. And so we might see an error like below.

Login failed for user ‘Domain\user’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: xx.xx.xx.xx]

Now what do we do? Below is how I overcame this problem.


Continue reading…

Shortcut: Creating Install media for SQL Server 2008 (any edition) + Service pack – Slip Stream method

Why do we need this?

By default, you might be having the stock media package which will give you a SQL server without the latest service pack. You will have to install service pack separately after installing the instance (Double work and time consuming). So if you follow below method you can update your install media with the latest service pack so that you can directly install SQL server + latest service pack in one shot. This method is also called as slip stream.

Prerequisites

Here lets take the example of adding Service Pack 2 to a SQL Server 2008 Enterprise Edition x64 install media.

  1. SQL Server 2008 Install Media.
  2. x86,ia64 and x64 versions of Executable for latest Service pack. They can be downloaded at http://www.microsoft.com/download/en/details.aspx?id=12548. Download all three files listed below.
    • SQLServer2008SP2-KB2285068-IA64-ENU.exe
    • SQLServer2008SP2-KB2285068-x64-ENU.exe
    • SQLServer2008SP2-KB2285068-x86-ENU.exe

Procedure

  1. Create a new folder name D:\SQL2008EEPlusSP2 and extract/copy all the files of the install media to that folder. Now you will have below list of files and folders in that New Folder.
    • ia64 (Folder)
    • x64 (Folder)
    • x86 (Folder)
    • autorun.inf
    • MediaInfo.xml
    • Microsoft.VC80.CRT.manifest
    • msvcr80.dll
    • Readme.htm
    • setup.exe
    • setup.rll

Continue reading…

Error: Could not find any IP address that this SQL Server instance depends upon.

Problem:

We had a requirement to change the SQL Server Fail over cluster group Virtual server name. We did that change through the cluster administrator. That SQL Server was 2008 Enterprise running on  Windows 2008 Enterprise cluster. The change was successfully made from one node and the SQL server came online after that. But when we did fail over testing, we noticed that the SQL Server service is not coming online on the other node. The SQL Server error-log gave below errors while trying to start-up.

  • Could not find any IP address that this SQL Server instance depends upon. Make sure that the cluster service is running, that the dependency relationship between SQL Server and Network Name resources is correct, and that the IP addresses on which this SQL Server instance depends are available. Error code: 0x103.
  • Error: 17182, Severity: 16, State: 1.
  • TDSSNIClient initialization failed with error 0x103, status code 0xa. Reason: Unable to initialize the TCP/IP listener. No more data is available.
  • Error: 17182, Severity: 16, State: 1.
  • TDSSNIClient initialization failed with error 0x103, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. No more data is available.
  • Error: 17826, Severity: 18, State: 3.
  • Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
  • Error: 17120, Severity: 16, State: 1.
  • SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Click here to continue reading this post and resolution

Changing SQL Server Collation on a SQL Server 2008 Failover Cluster

  1. Log-in to the active node.
  2. Trigger a full backup for all System and User Databases.
  3. Script out all log-ins.
  4. If there are any user databases, you will have to export the tables using BCP utility.
  5. Copy SQL Server 2008 install media folder to some local directory. For Eg: D:\SQL2008SP2
  6. Open command prompt and execute below command. Caution: This step will replace your current system databases with new ones with required collation. So please make sure you will be able to restore all logins and the user databases back after this activity.
    D:\SQL2008SP2\Setup.exe /QUIET /ACTION=REBUILDDATABASE
    /INSTANCENAME=MSSQLSERVER
    /SQLSYSADMINACCOUNTS=”DOMAIN\ntid” /SAPWD=strongpassword
    /SQLCOLLATION=New_Collation_Name

    Note: Please replace the values for /INSTANCENAME, /SQLSYSADMINACCOUNTS, /SAPWD and /SQLCOLLATION

    Here you go !. Your SQL server has got new collation settings now. Please let me know if incase you experienced any issues.

  7. Now restore all logins and userdatabases using the scripts generated in step 3 and the tables exported in Step 4.

Reference: http://msdn.microsoft.com/en-us/library/ms179254.aspx