Compatibility Level supported in SQL Server 2008
Compatibility Level is the propery set for databases which tells SQL Server how to interpret T-SQL statements. In each compatibility level Microsoft has removed or added some features of T-SQL statements, setting this would allow SQL server to interpret those T-SQL commands and work properly. In SQL server 2008 the following are the compatibility levels supported.
- 80 (SQL Server 2000)
- 90 (SQL Server 2005)
- 100 (SQL Server 2008)
Hence those who are planning to migrate to SQL Server 2008 should change their T-SQL statements (i.e in procedures or functions) if they have any database with compatibility level less than 80 to work well and good in SQL 2008.You can change compatibility level of the database either in GUI under database properties or else using the below system SP "sp_dbcmptlevel".
Script to CHECK Startup procedures in SQL Server
Today while checking a server for SQL Server startup performance issue, I'm in thought of listing the startup procedures and to analyze those procedures for any issues. But when I google I couldn't find any script to list out the startup procedures, hence I've written a script to list the startup procedures. I'm sharing the same with you all. You can get the script from below link.
Just for your knowlege, SQL Server startup procedures will exists only in master database. You can't enable startup option for a procedure which resides in any other database other than master db.
Applies to:
SQL Server 2000
SQL Server 2005
SQL Server 2008
SQL Server 2008 RTM Released
The day has come, Microsoft has released SQL Server 2008 RTM Version for companies (i.e for manufacturing) yesterday. You can get more details from the link below
SQL Server 2008 Press Materials
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Log Shipping FAQ
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)
-----------------------------------------------------------------------------
sql-articles 1st birthday
Hi Friends,
I'm happy to announce you that coming august 4th is our sql-articles.com first birthday. You know how we started this website? One fine day Sugesh, Me & Deepak was sitting on the beach and discussing about some topics related to SQL Server and that chat went fine. Suddenly Sugesh raised a question, Why can't we start a website for SQL server which would help all others. Then we had an idea of starting a website for SQL server, so we discussed on that and went home (Actually I thought the talking might fly away with the beach air). But On Aug 4th 2007 we have registered our website www.sql-articles.com and started writing simple articles on SQL server that could help the newbie's. Later on, more and more users started visiting our website and this initiates us to study deeper in sql server there by helping all users.
Thanks for all the people who helped us to start the website 