July 31, 2006 at 3:58 pm
I ran this app on our sql 2000 databases. Here's what I got:
sql 2005 Upgrade Advisor:
1. Anytime Upgrading will cause Full-text Search to use instance-level, not
global, word breakers and filters by default
Affected objects:
Component: .asx
Component: .css
Component: .hta
Component: .htt
Component: .idq
Component: .pps
Component: .xsl
** We don't use full text search on this development box and the above files are related to web-technolgoy. Any idea what the issue is?
2. Large backup or history tables make UPgrade appear to hang.
...Use sp_delete_backuphistory
** This is true but sp is ridiculously slow in practice. Any workarounds come to mind?
3. Outer join operators *= and =* are not supported in 90 compatibility mode.
Afected objects:
Source Type: Database
Database: RGD123
Object Name: pHEFHome
Object Type: P
** I looked at the stored proc but was unable to find the archaic join constructs. Any idea what the advisor is complaining about?
TIA,
Bill
August 1, 2006 at 7:35 am
I don't know about the first 2, but I think I know what's happening for the 3rd one. Look for a statement similar to this:
SELECT a.col1, b.col2
FROM a, b
This is the same as using the *= operator.
Good luck with the first two!
James
August 1, 2006 at 11:48 am
The sp_delete_backuphistory procedure uses a cursor to go through one backup event at a time and delete all related records in backuphistory, backupfile, backupset, backupmediaset, backupmediafamily, restorehistory, restorefile, and restorefilegroup. It's not too bad if you run it frequently and it has little to do, but if you're trying to clean out a lot of history it takes forever. The way to speed it up is to delete all unwanted records from each table in turn, ordering the deletes to avoid foreign key violations.
I use this proc to erase all backup history past a certain age in days on SQLServer 2000 systems. Scheduling it to run every week or so keeps backup history under control.
CREATE
PROC [dbo].[usp_DeleteBackupHistory] (@DaysToRetain int) AS
SET NOCOUNT ON
DECLARE @Err int
DECLARE @rc int
DECLARE @oldest datetime
SET @oldest = cast(getdate() as int) - @DaysToRetain
CREATE TABLE #msid (media_set_id INT NOT NULL PRIMARY KEY CLUSTERED)
CREATE TABLE #bsid (backup_set_id INT NOT NULL PRIMARY KEY CLUSTERED)
CREATE TABLE #rhid (restore_history_id INT NOT NULL PRIMARY KEY CLUSTERED)
INSERT INTO #msid
SELECT media_set_id FROM msdb..backupset
GROUP BY media_set_id HAVING MAX(backup_finish_date) < @oldest
ORDER BY media_set_id
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
INSERT INTO #bsid
SELECT backup_set_id FROM msdb..backupset b
INNER JOIN #msid m ON b.media_set_id = m.media_set_id
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
INSERT INTO #rhid
SELECT restore_history_id FROM msdb..restorehistory r
INNER JOIN #bsid b ON r.backup_set_id = b.backup_set_id
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
BEGIN TRAN
DELETE FROM msdb..restorefile
WHERE restore_history_id IN (SELECT restore_history_id FROM #rhid)
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
DELETE FROM msdb..restorefilegroup
WHERE restore_history_id IN (SELECT restore_history_id FROM #rhid)
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
DELETE FROM msdb..restorehistory
WHERE restore_history_id IN (SELECT restore_history_id FROM #rhid)
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
DELETE FROM msdb..backupfile
WHERE backup_set_id IN (SELECT backup_set_id FROM #bsid)
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
DELETE FROM msdb..backupset
WHERE backup_set_id IN (SELECT backup_set_id FROM #bsid)
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
DELETE FROM msdb..backupmediafamily
WHERE media_set_id IN (SELECT media_set_id FROM #msid)
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
DELETE FROM msdb..backupmediaset
WHERE media_set_id IN (SELECT media_set_id FROM #msid)
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
COMMIT TRAN
SET @rc = 0
GOTO isp_DeleteBackupHistory_Exit
Error_Exit:
ROLLBACK TRAN
SET @rc = -1
isp_DeleteBackupHistory_Exit:
IF OBJECT_ID('tempdb..#msid') IS NOT NULL DROP TABLE #msid
IF OBJECT_ID('tempdb..#bsid') IS NOT NULL DROP TABLE #bsid
IF OBJECT_ID('tempdb..#rhid') IS NOT NULL DROP TABLE #rhid
RETURN @rc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply