SQL-Articles

New article on Index fragmentation posted in main website, check the article in the link below.

Index Fragmentation in SQL Server 2005

SQL Server 2005 Index, SQL Server 2005

SQL Server 2005 SP3 released today after the release of SP3 beta a couple of weeks ago. You can download SP3 from the link below.

DOWNLOAD SQL SERVER 2005 SP3

Above download link is applicable to below products.
* SQL Server 2005 Enterprise
* SQL Server 2005 Enterprise Evaluation
* SQL Server 2005 Developer
* SQL Server 2005 Standard
* SQL Server 2005 Workgroup

For Express edition check the link http://go.microsoft.com/fwlink/?LinkId=64064 for SP3.

For a list of new features and improvements that are included in SQL Server 2005 SP3, review the What’s New document.
Once the server is patched with SP3, SQL Server version will be changed to 9.00.4035. You can use the below query to check it.

SELECT SERVERPROPERTY('ProductLevel')

SELECT SERVERPROPERTY('ProductVersion')

SQL Server 2005 Administration, Service Pack, SQL Installation

Designing \ Editing DTS packages in SSMS is one of the question I’m seeing in forums, this situation occurs when an upgrade happens from SQL 2000 to SQL 2005. When SQL server 2005 was released it doesn’t have a facility for editing \ designing DTS packages after upgrade. Even If you run upgrade advisor in that machine before upgrade you will be shown with a warning message as shown below.

SQL Server 2000 Meta Data Services packages are not supported
Existing Meta Data Services packages should be moved to SQL Server storage (in the msdb database) or to structured storage files before you upgrade. SQL Server 2005 does not install support for SQL Server 2000 Meta Data Services; therefore the Package Migration Wizard will be unable to access Meta Data Services packages after upgrade in the absence of the SQL Server 2000 Client Tools.

Read more…

SQL Server 2005 Administration, DTS, SQL Server 2005

Introduction:

I could see most of them are asking known errors of log shipping in many forums and we are repeatedly providing the same solutions. Hence I thought of consolidating all the know errors and its solutions for log shipping in this post as FAQ. Probably I must pickup all the know errors, If I have missed anything you can very well post in FORUMS section.

Question : IS it possible to log ship database between SQL 2000 & SQL 2005?

Answer: No, thats impossible, In SQL 2005 transaction log architecture is changed compared to SQL 2000 and hence you won’t be able to restore tlog backups from SQL 2000 to SQL 2005 or vice versa.

—————————————————————————–

Question: How to failover in SQL 2005 Log Shipping?

Answer: I can better ask to check out the link Failover in SQL 2005 Log Shipping, Deepak written this article clearly.

—————————————————————————–

Question:I’m getting the below error message in restoration job on secondary server, WHY?

[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

Answer:Was your sql server or agent restarted Y’day in either source or destination ? because the error states there is a mismatch in LSN. A particular tran log was not applied in the destination server hence the subsequent tran logs cannot be applied as a result !

You can check log shipping monitor \ log shipping tables to check the which transaction log is last applied to secondary db, if the next consecutive transaction logs are available in the secondary server share folder you manually RESTORE the logs with NORECOVERY option, Once you restored all the logs automatically from the next cycle the job will work fine.

Incase if you are not able to find the next transaction log in secondary server shared folder, you need to reconfigure log shipping. Try the below tasks to re-establish log shipping again.

• Disable all the log shipping jobs in source and destination servers
• Take a full backup in source and restore it in secondary server using the With Standby option
• Enable all the jobs you disabled previously in step1

—————————————————————————–

Question: Is it possible load balance in log shipping?

Answer:Yes ofcourse its possible in log shipping, while configuring log shipping you have the option to choose standby or no recovery mode, there you select STANDBY option to make the secondary database readonly. For SQL 2005 log shipping configuration check out the link 10 Steps to configure Log Shipping

—————————————————————————–

Question: Can I take full backup of the log shipped database in primary server??

Answer: In SQL Server 2000 you won’t be able to take full backup of log shipped database, because this will break the LSN chain and it directly affects the log shipping.
In SQL Server 2005, yes its possible. You can take full backup of log shipped database and this won’t affect the log shipping.

—————————————————————————–

Question : Can I shrink log shipped database log file??

Answer: Yes ofcourse you can shrink the log file, but you shouldn’t use WITH TRUNCATE option. If you use this option obviously log shipping will be disturbed.

—————————————————————————–

Question : Can I take full backup of the log shipped database in secondary server??

Answer: No chance , you won’t be able to execute BACKUP command against a log shipped database in secondary server.

—————————————————————————–

Question: I’ve configured Log shipping successfully on standby mode, but in the restoration job I’m getting the below error. What I do to avoid this in future??
Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\TEST_20060731131501.trn’ to secondary database ‘TEST’.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Answer: To restore transaction logs to the secondary db, SQL Server needs exclussive access on the database. When you configure it in standby mode, users will be able to access the database and runs query against the secondary db. Hence If the scheduled restore jobs runs at that time, the db will have a lock and it won’t allow SQL Server to restore the tlogs. To avoid this you need to check “Disconnect users in the database when restoring backups” options in log shipping configuration wizard. Check the link 10 Steps to configure Log Shipping.

—————————————————————————–

Question : Can you tell me the pre-requisites for configuring log shipping??

Answer : Check out the link Pre-requisites for Log Shipping.

—————————————————————————–

Question : Suddenly I’m getting the error below, How can I rectify this???
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally

Answer : We had the same issue some time ago, this was related to a new file being created in a filegroup on the source. Don’t know if this applies to your case, but restoring a backup of this new file on the secondary server solved the problem.

—————————————————————————–

SQL Server 2005 Log Shipping FAQ

Introduction:

SQL Backup script is available in all online resources. I couldn’t find a script to take care of retention also, so I’m writing this script as procedure to take care of retention too. This script will take full backup of all the databases.

Applies to:

• SQL Server 2000
• SQL Server 2005
• SQL Server 2008

Pre-requisites:

• Xp_cmdshell procedure should be enabled both in SQL 2005 & SQL 2008

Script:

Dowload the script HERE

Usage:

EXEC master.dbo.usp_backup ‘F:\Bkup\’,1

Output:

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$ SERVERNAME: SAGARSYS — DATE: Jun 28 2008 10:00AM $
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Retention
^^^^^^^^^
Deleting the below backup files as part of retention plan……
output
———————————————————
master_20080627.bak
model_20080627.bak
msdb_20080627.bak
NULL

output
———————————————————-
NULL

Database Backups Started
^^^^^^^^^^^^^^^^^^^^^^^^
************Processing master Backup… **************
Processed 352 pages for database ‘master’, file ‘master’ on file 1.
Processed 3 pages for database ‘master’, file ‘mastlog’ on file 1.
BACKUP DATABASE successfully processed 355 pages in 0.747 seconds (3.887 MB/sec).
Backed up to F:\Bkup\master_20080628.bak
********************************************************

************Processing model Backup… **************
Processed 152 pages for database ‘model’, file ‘modeldev’ on file 1.
Processed 2 pages for database ‘model’, file ‘modellog’ on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.344 seconds (3.655 MB/sec).
Backed up to F:\Bkup\model_20080628.bak
********************************************************

************Processing msdb Backup… **************
Processed 608 pages for database ‘msdb’, file ‘MSDBData’ on file 1.
Processed 2 pages for database ‘msdb’, file ‘MSDBLog’ on file 1.
BACKUP DATABASE successfully processed 610 pages in 0.874 seconds (5.712 MB/sec).
Backed up to F:\Bkup\msdb_20080628.bak
********************************************************

============Backup Completed Successfully============

Discussion:

All your comments are highly appreciated, please post your comments @ FORUMS section

SQL Server 2005, SQL Server 2008, Scripts Backup, T-SQL