DB Upgrade/Schema Changes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DB Upgrade/Schema Changes

Author

Topic

Kowalski
Starting Member

3 Posts

Posted - 2008-04-01 : 04:44:31

Hi, trying to affect schema changes on an SQL DB using however Im encountering a number of errors when trying to do this. Changes are are affected by running a consecutive number of scripts against the DB, see example of script below:

-- PREVIOUSDBVERSION=2.8.2.5
-- The above line must be present in all Upgrade SQL files to catch any gaps in the upgrade chain.
--
--ALL
--
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

PRINT N'Dropping Stats from A DB'
GO
DECLARE @tblname sysname, @statname sysname, @sql nvarchar(2000)

DECLARE c CURSOR FOR
SELECT object_name(id), name
FROM sysindexes
WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1

OPEN c
FETCH NEXT FROM c INTO @tblname, @statname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DROP STATISTICS [' + @tblname + '].[' + @statname + ']'
PRINT @sql
EXEC (@sql)
FETCH NEXT FROM c INTO @tblname, @statname
END

CLOSE c
DEALLOCATE c
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

The database I am upgrading has a growth limit of 2GB, when running the scripts against the DB two things are happening.

1) DB size begins to increase rapidly and eventually breaches 2GB mark
2) LDF file swells to upto 3GB

My first thought is that the schema changes are gathering in the log file and not being commited to the DB, or that there is some kind of open transaction that the script is constantly trying to execute over and over.

Would like to point out im a novice SQL user, if the information above is sketchy please dont hesitate to say and I will endeavour to provide you with more accurate information.

Cheers

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-01 : 05:51:43

Well you have a begin transaction statement so all this will be run in a transaction.
All you are doing is dropping statistics (for some reason) so why do you want a transaction.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources