December 4, 2009 at 2:18 pm
Another major gotcha for SQL 2008 is the fact that you can no longer truncate and or shrink your database transaction logs. The queries to perform this task are deprecated in SQL 2008.
The deprecated queries (which are supported in SQL 2000 and SQL 2005, but no longer supported in SQL 2008) are as follows:
--To truncate the log file
backup tran <dbname> with no_log
--To Shrink the T-log container:
dbcc shrinkfile(<dbname>empty_log)
The only way to keep the Transaction log small, is to do incremental backups (multiple times during the day) of the transaction logs.
December 4, 2009 at 4:25 pm
Yep, I figured that out the hard way. I had a stored procedure called usp_ShrinkAllLogs, which used the BACKUP LOG <LogName> With Truncate_Only and DBCC SHRINKDATASE(<databasename>, 10). I changed it to instead to set the recovery model to simple, then use the DBCC SHRINKDATASE(<databasename>, 10) to recover the disk space, and then set the recovery model back to FULL. Of course you need to do a full database backup after that to reset the Log Sequence Number. Do a search on usp_ShrinkAllLogs in the scripts section and you'll find my code.
Cheers!
😀
December 7, 2009 at 5:28 am
MS want the correct procedure to truncate logs to be setting recovery mode to simple, hence deprecation of no_log and truncate_only.
However dbcc shrinkfile is still available, it would be a shame to have to use to shrink database if you only want to shrink the log!
http://msdn.microsoft.com/en-us/library/ms189493.aspx
---------------------------------------------------------------------
December 7, 2009 at 11:30 am
One gotcha that got us was testing 2005 databases.
The test we wanted to do was see how a large database performed on the new 2008 machine. We detached it from the 2005 server, attached it to the 2008 server, did some tests and then detached it to move it back to the 2005 server. The 2005 server would NOT let us attach the DB saying that the file version was above what it expected -- ie the files had been changed to the 2008 version, even though we were running in 2005 compatibility mode. It's a one way street...
Had to restore from backups, which were luckily rather fresh.
December 8, 2009 at 9:41 pm
mflynn-549529 (12/7/2009)
One gotcha that got us was testing 2005 databases.The test we wanted to do was see how a large database performed on the new 2008 machine. We detached it from the 2005 server, attached it to the 2008 server, did some tests and then detached it to move it back to the 2005 server. The 2005 server would NOT let us attach the DB saying that the file version was above what it expected -- ie the files had been changed to the 2008 version, even though we were running in 2005 compatibility mode. It's a one way street...
Had to restore from backups, which were luckily rather fresh.
This has been true since at least 2005, so be sure that you don't try doing this going from 2000 to 2005 either. Normally, my peers and I at my company do a backup and restore to 2008, then we effectively have two copies running. Obviously, you'd need enough storage to do that. We also don't like detaching and reattaching because they are the only files for the DB in that case, and we view that as too risky when simply testing out functionality.
Take care,
Steve
March 25, 2010 at 12:13 pm
1
) Reporting Services (RS) is required for the Upgrade Advisor
This is a logical gotcha that kinda makes me mad. Microsoft Best Practices and our corporate security guidelines state that you should not put Reporting Services on the same server as an Operational Data Store (ODS). The crux of the matter is that the SQL Upgrade Advisor (SUA) has to be on the server that it is scanning. It makes much more sense that the SUA be installed on a separate RS server and pointed towards the target server with a connection object. I went ahead and installed RS on the first dev server and then ran the SUA scan.
The scan revealed nothing I didn't already know. Secondly, the SQL upgrade program does a similar scan, which will reveal any show stoppers. I made a judgment call and decided not to run SUA on servers that don't already have RS on them.
Do we need to install SQL Server 2005 Reporting Services OR SQL Server 2008 Reporting Services on where we run SQL Server 2008 upgrade adviser?
thanks
March 25, 2010 at 4:18 pm
The SQL Server 2008 Upgrade advisor is designed to work with SQL Server 2005 Reporting Services.
For more information look here:
http://msdn.microsoft.com/en-us/library/bb677622.aspx
Cheers!
April 7, 2010 at 9:56 pm
There is some good information here but corrections/clarifications that need to be made -
Item #1) Reporting Services is NOT a requirement to run the SQL Server Upgrade Advisor!
a. The location where you install SQL Server Upgrade Advisor depends on what you will be analyzing. Upgrade Advisor supports remote analysis of all supported components except Reporting Services. If you are scanning instances of Reporting Services, you must install Upgrade Advisor on the report server.
b. If you are not scanning instances of Reporting Services, you can install Upgrade Advisor on any computer that can connect to your instance of SQL Server, and that meets the Upgrade Advisor prerequisites.
Additional information - http://msdn.microsoft.com/en-us/library/ms144256.aspx
Item #3) Visual Studio 2008 - SP1 required for any v3.5 .NET components - NOT TRUE!
Visual Studio Sp1 is only required if you have Visual Studio installed and you plan on using Visual Studio after you upgrade to SQL Server 2008. If you don't plan on using it, then, technically, you don't have to upgrade it. If you have not installed Visual Studio 2008 RTM but are getting the error that VS SP1 is required, it is because Visual Studio 2008 Shell (integrated mode) is installed on your system but not at the SP1 level. This is probably because it was left over from a SQL Server 2008 CTP installation.
The binaries needed from VS 2008 SP1 are not related to the .Net Framework 3.5 SP1 update. SQL Server 2008 does require the .Net Framework 3.5 SP1 update but for different reasons. SQL Server 2008 setup will install the released version of .Net Framework 3.5 SP1 if it detects it is not already installed. This is one of the first things SQL Server 2008 setup does when executed. Even if you have the .Net Framework 3.5 SP1 Beta, SQL Server 2008 will update it to the RTM version of .Net Framework 3.5 SP1.
See this article for more information - http://blogs.msdn.com/joaol/archive/2008/08/29/sql-server-2008-visual-studio-2008-sp1-and-net-framework-3-5-sp1-explained.aspx
Finally, to answer the post by SSCrazy - yes, the SQL Server 2008 workstation tools are backwards compatible with SQL Server 2000 & 2005... of course, with limited functionality depending on the versions and features supported on the version you're connecting to.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply