SQL Server execution plan as text

Sometimes it's more useful to display the execution plan as text format .
Even though Grapical format is the easiest to read , a lot of the operator data is visible - except by looking into Property windows.

In diagnosing complex execution plans this is where the text format is useful.

To execute the Estimated text execution plan - use:

SET SHOWPLAN_ALL ON;

To turn it off use:

SET SHOWPLAN_ALL;

In order to view the profile information of the Actual execution plan, use:

SET STATISTICS PROFILE ON;

and to turn it off:

SET STATISTICS PROFILE OFF;

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server - Backup data is incorrectly formatted

I tried to complete a SQL server 2005 database Restore from disk,this information message was displayed in the error logs:

The backup data at the end of "K:\backups\mybackup.bak" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.

I ran this command:

RESTORE VERIFYONLY
FROM DISK='K:\backups\mybackup.bak'

after 1 hr there were still no response - on a 20 GB database.

As a way of troubleshooting - I issued the BACKUP DATABASE command again , with the the FORMAT option . This time , I issued the RESTORE VERIFYONLY   and got a positive response within a short while. I was able to complete the RESTORE.

The important point here was that the initial backup was reported as successfull , in the Logs, but then failed on the RESTORE. This indicates a  filemark in the backup device could not be read.With some research the most likely reason for the initial RESTORE failure was that SQL server reported the BACKUP as OK - even though a IO write failed.

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Restoring a sql server 2005 master database on sql server 2005 error

I received an email outlining a restore problem. In attempting to restore a SQL Server 2000 master database onto a SQL Server 2005 as a user database , the

following error message appeared:

Restore failed for Server 'MyServer\MyInstance' .
There is already an object named sysnsobjs in the database

The solution is to restore a system database only to a SQL Server edition & version that are exactly the same as the source SQL Server

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server 2005 and SQL Server 2008 Upgrade tasks

Upgrading from SQL Server 2000 to SQL Server 2005 (or SQL Server 2008) requires some organisation. It is important to create and maintain a task list.

I use the general list of tasks as a basis or template. I  customise the list for every SQL Server instance. By the end of the consultation the list can grow to over 100 tasks , depending on the database server size , the amount of application layer built in to the database and the server environment.

 The example below is a real-world example of a task list. It was a SQL Server 2005 upgrade .

I manage the task list through Microsoft Excel. Every task is associate with categories:

When  (PreConfig,Post Config)

Source  (Monitoring,Backup,DTS,Databases,Server)

Owner   (Main reference for task)

Start Time

End Time

Status

Comments

I also have detailed upgrade plans for every component – such as Backup devices plan, SysMessages , Replication, DTS, Pre Upgrade, Post Upgrade etc.

 

Confirm RSA support

MYDB  delete tables

Allow dummy drives(H: I: J: )

Confirm existing sql server 2000 installation scripts

Confirm sql server 2000 binaries are in location

Prepare SQL Server Installation scripts

TSM Client Drivers for SQL Server 2005

Install\Confirm Monitoring drivers for 2005

usage of cname is done - server1.mydomain.net

Default language settings for English - check script

Confirm DTS tested on 2005

Confirm Jobs tested on 2005

Analyze system databases for user-defined objects

Develop “Fix upgrade issues in SQL Tasks” script

usage of cname  - server1.mydomain.net

Script Linked Server Configuration

Script Jobs

Script Alerts

Script Operators

Script Backup Devices

Script User-defined messages

Script Users, Roles and Logins

Backup all databases to TSM

Change backup schedule change  for the weekend

Backup all databases to to Local Disk

Confirm from owner it is OK to proceed with upgrade

Request mirrorrs are broken

Uninstall BMFMySQL on Server1

Detach databases

Uninstall server1\myinstance

Install SQL Server 2005 - Standard Edition

Install SQL Server 2005 - Service Pack 2

DTS Designer Install

Restore databases   via attach

Execute Script Linked Server Configuration

Execute Script Jobs

Execute Script Alerts

Excute Script Operators

Execute Script Backup Devices

Execute Script User-defined messages

Execute Script Users, Roles and Logins

Execute scripts - \config\*

UPDATE STATISTICS with FULL SCAN

updateusage

recompile all objects

refreshallViews

SQL Server Index Maintenance Strategy

Implement Database Backup Maintenance Plans

Check Monitoring is connecting user 'itmsql'

hand over to sword

Signed of by Application Owner

Run ETL

Notify storage to reset mirrors

 As I progress through the upgrade , the task list is altered to reflect  new issues or tasks. For example, there may be a requirement to complete a SQL Server security audit or a DTS to SSIS .

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)