Script to Verify the litespeed backup file

Use the below script to verify the litespeed backup file

exec master.dbo.xp_restore_verifyonly
@filename = N'\\backups\full\mydbbackup.full.BAK',
@filenumber = 1,
@logging = 0


Script to list out important properties of database

Use below script to list out the important properties of the database

 


select
sysDB.database_id,
sysDB.Name as 'Database Name',
syslogin.Name as 'DB Owner',
sysDB.state_desc,
sysDB.recovery_model_desc,
sysDB.collation_name,
sysDB.user_access_desc,
sysDB.compatibility_level,
sysDB.is_read_only,
sysDB.is_auto_close_on,
sysDB.is_auto_shrink_on,
sysDB.is_auto_create_stats_on,
sysDB.is_auto_update_stats_on,
sysDB.is_fulltext_enabled,
sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid


Script to change the database compatibility level

Use below script to change the database compatibility level

ALTER DATABASE DatabaseName
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel DatabaseName, 90;
GO
ALTER DATABASE DatabaseName
SET MULTI_USER
GO


SQL Server Version	Compatibility Level
SQL Server 6.5	65
SQL Server 7.0	70
SQL Server 2000	80
SQL Server 2005	90
SQL Server 2008	100

Login failed for user XYZ The user is not associated with a trusted SQL Server connection.

Problem:Login failed for user XYZ The user is not associated with a trusted SQL Server connection.

Resolution: Login is failed due the the authentication mode is Windows Authentication and it is not allowing SQL Server authentication for SQL Login.

Change the Authentication Mode of the SQL server from Windows Authentication Mode (Windows Authentication) to Mixed Mode (Windows Authentication and SQL Server Authentication)

Right Click on Server and Select the Server Properties from pop up.

Unable to install Windows Installer MSP file

Problem: Unable to install Windows Installer MSP file 

Description: I got the above error while installing Service Pack3 on SQL Server 2005 box. 

Product                                    : Database Services (INST2)
Product Version (Previous)     : 3257
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

 Resolution:  

Step 1:  Check the setup log file from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log 

Step 2: Search for “return value 3” and scan next +/- 10 lines for error details 

Step 3: During scan I found the below error details “Fail create file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData  folder” I have created the required folder at desired location and has resolve the issue. 

There are other causes as well for the failure to resolved it you can follow the below steps

-          Copy the service pack from network location to installation location or on SQL Server box

-          Check for the registry parameters 

Check below KB documents to resolve error

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

 

Msg 7391, Level 16, State 2, Line 23 - Error while running Linked Server

Error Description 

OLE DB provider "SQLNCLI" for linked server "MYView" returned message "The transaction manager has disabled its support for remote/network transactions.".

 Msg 7391, Level 16, State 2, Line 23

 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "MYView" was unable to begin a distributed transaction.

 Resolution:

1. Check whether DTC is blocked by firewall, if it is blocked by firewall release it.

2. Check or configure DTC to allow network connection as below.

 

Go to Control Panel ->Administrative Tools -> Component Services -> Expand the Computer Node -> Right click on My Computer) -> Click on Properties -> MS DTC Tab -> Security Configuration

 

Script - Generate DBCC ShrinkFile for each datbase files

You can use below query to generate DBCC Shrikfile command for data and log file.

Query

select 'use ' + db_name(dbid) + char(13) + 'dbcc shrinkfile (' + quotename(sf.name,'''') + ' ,truncateonly)' from sysaltfiles sf
inner join sys.databases sd on sf.dbid = sd.database_id
where state_desc = 'online'


 

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Error:  Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Error clearly state that login is not associated with the trusted connection.

What is trusted connection and what is the advantage of it? Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.

Advantage of this process is that we don’t have to store credential with the connection string.

However sometimes you will see the error above error.

Resolution

1. Change the authentication mode to mixed mode from SSMS or Enterprise Manager

2. Check the user/login is associated with the appropriate SQL login

3. Host is in the same domain or Host machine is network, sometime rebooting of the host machine will resolve this issue.

DBCC PAGE

DBCC PAGE 

We can use the undocumented DBCC PAGE command to view the page header, data rows, and row offset table for any data page in a database. You have to turn on traceflag 3604 before running this command.  You can also use this command when you find the IO bottleneck and query is waiting Page Latches.

Permission: System Admin rights

Syntax

dbcc page ( {'dbname' | dbid}, file number, page number [, print opt={0|1|2|3} ])

 You can pass the below print option parameter as per the requirement

• 0 - print just the page header


• 1 - page header plus per-row hex dumps and a dump of the page slot array (unless it’s a page that doesn't have one, like allocation bitmaps)


• 2 - page header plus whole page hex dump


• 3 - page header plus detailed per-row interpretation

Trace Flag – 3604 Trace flag 3604 is the most commonly used trace flag. It sends the output of a trace to the client. For example, before you can run DBCC PAGE, which views data page information, you must run this trace flag. 

DBCC traceon(3604)

DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache] ) 

The output from DBCC PAGE is divided into four main sections: Buffer, Page Header, Data, and Offset Table (really the Offset Array).