Vidhya Sagar – Blog

New article posted on changing collation in sql server 2005. You can check the article from the link below.

In this article we are going to discuss about changing SQL Server 2005 collation at serverlevel. While installing SQL Server 2005 we may miss to choose the right collation and we need to rectify this by changing the collation at serverlevel. You can change the collation of sql server without uninstalling.

How to change Server Collation in SQL Server 2005

Vidhya Sagar SQL Server 2005 Administration

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

Index fragmentation is a phenomenon where the index contents are scattered. Normally the contents are in contiguous fashion which helps in fast retrieval of the underlying data. When the indexes are fragmented the data access becomes time consuming because of the scattered data that needs to be searched and read.

Index Fragmentation in SQL Server 2005

Vidhya Sagar 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')

Vidhya Sagar 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…

Vidhya Sagar SQL Server 2005 Administration, DTS and SSIS, 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.

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

Question : Is it possible to log ship database from SQL server 2005 to SQL server 2008 and vice versa?

Answer : Yes you can log ship database from SQL server 2005 to SQL Server 2008 this will work. However log shipping from SQL Server 2008 to SQL Server 2005 is not possible because you wont be able to restore SQL server 2008 backup to SQL Server 2005 (downgrading version)

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

Vidhya Sagar SQL Server 2005 Log Shipping