Database Upgrade to SQL Server 2008 Tools and Approaches

This is one of the best article to read about Tools and approcaches to upgrade production servers  to SQL Server 2008.

SQL Server Technical Article:

Performing an upgrade is a complex and often risky project. A successful upgrade can provide an organization with a modern platform for development and production. An unsuccessful upgrade can cause lost time and money, and it can create a bad perception for the future.

This paper documents approaches and tools that can help DBAs and developers to achieve successful and mostly painless upgrade of SQL Server databases from Microsoft® SQL Server® 2000 or SQL Server 2005 to SQL Server 2008 (or SQL Server 2008 R2).

In the methodology this paper discusses, a SQL Server workload is captured on customer production environment, and then this identical test workload is run on the old and new versions of SQL Server in a test environment to compare performance and isolate any problem queries. For this approach, we use components from the "RML Utilities for SQL Server" set of tools.

Source : http://msdn.microsoft.com/en-us/library/ff758640.aspx

Error 3117 The log or differential backup cannot be restored because no files are ready to rollforward

-- Part 01
-- Check backup information
RESTORE FILELISTONLY   
  FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_FULL.bak'

-- Restore Database with FULL Backup
RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_FULL.bak'
WITH NORECOVERY

-- Part 02
RESTORE FILELISTONLY   
  FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_DIFF.bak'

-- Restore Database with  Differential Backup
RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_DIFF.bak'
WITH NORECOVERY

-- Part 03
RESTORE FILELISTONLY   
  FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_LOG_01.Log'

-- Restore Database with Log Backup 
RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_LOG_01.log'
WITH NORECOVERY

RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_LOG_02.log'
WITH NORECOVERY

-- Final log with Recovery
RESTORE DATABASE AdventureWorksR2
FROM DISK = 'E:\MSSQL\Backup\AdventureWorksR2_LOG_03.log'
WITH RECOVERY

Error: 3041, Severity: 16, State: 1

Message:

BACKUP failed to complete the command BACKUP DATABASE db_name. Check the backup application log for detailed messages

Solution:  Make sure to take another full database backup.

Database RESTORE error Msg 3183, Level 16, State 2, Line 1

When you try to restore/verifyonly SQL backup you may be found this error some time because the backup may be corrupted.

Solution : Make sure to copy backup file into another device then restore, The issue is on tape/usb/disk

RESTORE VERIFYONLY FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'

Msg 3242, Level 16, State 2, Line 2
The file on device '\\websql2\SQLBackup\MYDBTEST_2011-06-21-2040.FUL' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.


RESTORE DATABASE MYDBTEST
FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'
WITH RECOVERY,
MOVE 'MYDBTEST_Data' TO 'E:\\MSSQL\DATA\MYDBTEST.mdf',
MOVE 'MYDBTEST_Log' TO 'E:\ MSSQL\DATA\MYDBTEST_log.ldf',
STATS = 5


05 percent processed.
.
.
.
.

85 percent processed.
90 percent processed.

Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database "MYDBTEST" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

How to find the reporting server ExecutionLog information

Use ReportServer
go
SELECT TimeDataRetrieval,TimeStart,TimeEnd,InstanceName,ReportID,
UserName,RequestType,Format,Parameters,TimeProcessing,
TimeRendering,Source,[Status],ByteCount,[RowCount]
FROM ExecutionLog with (nolock)
ORDER BY TimeStart DESC

How to capture deadlock on your sql error logs regularly

--Find the trace flag currently running

DBCC TRACESTATUS(-1)
GO
-- Enable Trace ID 1204 for deadlock
DBCC TRACEON (1204)
GO

Upgraded to SQL 2008 tempdb from earlier version like SQL2005/2000

When you upgraded to SQL 2008 from SQL 2005/2000 make sure to change tempdb database Page_verify_option set to CHECKSUM.


By default in SQL server 2008 Page_verify_option is set CHECKSUM but in SQL 2005 the default is NONE.

You can  run the below query to check:

select name,Page_verify_option from sys.databases where name='tempdb'

0 - NONE
1 - TORN_PAGE_DETECTION
2 - CHECKSUM
 
source: http://msdn.microsoft.com/en-us/library/ms175527.aspx

Do not use select count(*) from table_name to find the number of rows in a table

If you have multi million/billion rows in your table. Do not use select count(*) from table_name to find the number of rows in a table instead you can try to use sp_spaceused 'table_name' It is much quicker than select count(*).

Auto create stats turned on sql database

If you have a query execution issues first thing you should look at your database  to check auto_create and auto_update stats is turned on or off?

select name, is_auto_create_stats_on ,is_auto_update_stats_on

from sys.databases
where name = 'AdventureWorks2008' -- your db name here

0 - turned off
1 - turned on

The following query will list out all dbs

SELECT name AS "DbName",

is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO

Find all the database information from sys.databases

when i connect to SQL Server 2005/2008 first time, I always run this query to find  the databases information.

SELECT name ,database_id,create_date,compatibility_level,collation_name,user_access,is_read_only,is_auto_close_on,is_auto_shrink_on,state,state_desc,recovery_model,recovery_model_desc,page_verify_option,page_verify_option_desc,is_auto_create_stats_on,is_auto_update_stats_on
FROM sys.databases

High Availability with SQL Server 2008 by Paul S. Randal

Paul S. Randal written white paper about High Availability with SQL Server 2008.
This white paper describes the technologies available in SQL Server 2008 that can be used as part of a high-availability strategy to protect critical data.

Source : Microsoft White paper
http://msdn.microsoft.com/en-us/library/ee523927.aspx

Find the sp_configure values for instance of sql server

exec sp_configure 'Show Advanced Options', 1
go
Reconfigure
go
exec sp_configure

Find available space on Data & Log file on sql server database

-- Find free data space on master database
use master

go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

-- Find free data space on database
use AdventureWorks2008 -- ( yourdbname )
go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

-- Find free log space on database
DBCC SQLPERF(LOGSPACE);

Find the all active user connections and internal tasks

SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =1
AND session_id <>@@SPID

Find all system and user-defined error messages in the Database Engine

There are 8866 error messages in SQL Server 2008

SELECT message_id, language_id, severity, is_event_logged, text
FROM sys.messages
WHERE language_id = 1033

How to find the blocking transaction on SQL Server

SELECT t.resource_type,
t.resource_database_id,
t.resource_associated_entity_id,
t.request_mode,
t.request_session_id,
w.blocking_session_id
FROM sys.dm_tran_locks as t
INNER JOIN sys.dm_os_waiting_tasks AS w
ON t.lock_owner_address =w.resource_address