SQL Server Performance Forum – Threads Archive

Healthcheck needed on a script

Hi, can anyone tell me how the errors can be fixed on this script?? Errors: The CREATE DATABASE process is allocating 0.75 MB on disk ‘aspstate’. The CREATE DATABASE process is allocating 0.49 MB on disk ‘aspstate_log’. Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93 Login ‘usraspstate’ is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12 User ‘usraspstate’ does not exist in the current database. Granted database access to ‘usraspstate’. ’usraspstate’ added to role ‘db_datareader’. ’usraspstate’ added to role ‘db_datawriter’. ’usraspstate’ added to role ‘db_owner’. (1 row(s) affected) Type added. (1 row(s) affected) Type added. (1 row(s) affected) Type added. (1 row(s) affected) Type added. (1 row(s) affected) Type added. Server: Msg 2714, Level 16, State 6, Line 7 There is already an object named ‘ASPStateTempSessions’ in the database. Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ‘CreateTempTables’. The stored procedure will still be created. Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 57 The login ‘usraspstate’ already exists. ’usraspstate’ added to role ‘db_datareader’. ’usraspstate’ added to role ‘db_datawriter’. And here’s the script, a tad long…. /*****************************************************************************/ /*This script is called by a Startup Stored Procedure in the Master Database */ /*to enable session state for usraspstate in the aspstate database. */ /*The startup stored procedure is called aspstate_reinstate. */ /*King David 22/06/2004. */ /*****************************************************************************/ /*************************************************************/ /*Drop & destroy the exisiting aspstate database completely. */ /*************************************************************/ IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N’aspstate’) DROP DATABASE [aspstate] GO /****************************************************************************/ /*Create the new database, using the default model, therefore no file names */ /*required or collation required, or individual objects to be destroyed. */ /****************************************************************************/ CREATE DATABASE aspstate GO /*******************************/ /*Set the database properties. */ /*******************************/ exec sp_dboption N’aspstate’, N’autoclose’, N’false' GO exec sp_dboption N’aspstate’, N’bulkcopy’, N’false' GO exec sp_dboption N’aspstate’, N’trunc. log’, N’false' GO exec sp_dboption N’aspstate’, N’torn page detection’, N’true' GO exec sp_dboption N’aspstate’, N’read only’, N’false' GO exec sp_dboption N’aspstate’, N’dbo use’, N’false' GO exec sp_dboption N’aspstate’, N’single’, N’false' GO exec sp_dboption N’aspstate’, N’autoshrink’, N’false' GO exec sp_dboption N’aspstate’, N’ANSI null default’, N’false' GO exec sp_dboption N’aspstate’, N’recursive triggers’, N’false' GO exec sp_dboption N’aspstate’, N’ANSI nulls’, N’false' GO exec sp_dboption N’aspstate’, N’concat null yields null’, N’false' GO exec sp_dboption N’aspstate’, N’cursor close on commit’, N’false' GO exec sp_dboption N’aspstate’, N’default to local cursor’, N’false' GO exec sp_dboption N’aspstate’, N’quoted identifier’, N’false' GO exec sp_dboption N’aspstate’, N’ANSI warnings’, N’false' GO exec sp_dboption N’aspstate’, N’auto create statistics’, N’true' GO exec sp_dboption N’aspstate’, N’auto update statistics’, N’true' GO if( ( (@@microsoftversion / power(2, 24) = <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) ) exec sp_dboption N’aspstate’, N’db chaining’, N’false' GO /*********************************************************/ /*Ensure all activity is carried out in the aspstate db. */ /*********************************************************/ use aspstate go /*********************************/ /* Destroy the user usraspstate. */ /*********************************/ exec sp_droplogin ‘usraspstate' go exec sp_dropuser ‘usraspstate' go /**************************/ /*Create the usraspstate usr. */ /**************************/ if not exists (select * from master.dbo.syslogins where loginname = N’usraspstate’) BEGIN declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N’aspstate’, @loginlang = N’us_english' if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb) /************************************/ /*Default to the aspstate database. */ /************************************/ select @logindb = N’aspstate' if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N’us_english’) select @loginlang = @@language exec sp_addlogin N’usraspstate’, ‘usraspstate’, @logindb, @loginlang END GO /*************************/ /*Grant database access. */ /*************************/ if not exists (select * from dbo.sysusers where name = N’usraspstate’ and uid < 16382) EXEC sp_grantdbaccess N’usraspstate’, N’usraspstate' GO /******************************/ /*Grant the role permissions. */ /******************************/ exec sp_addrolemember N’db_datareader’, N’usraspstate' GO exec sp_addrolemember N’db_datawriter’, N’usraspstate' GO /*********************************************/ /* Ensure usraspstate is the database owner. */ /*********************************************/ sp_addrolemember ‘db_owner’,’usraspstate' go se tuser GO /***********************************/ /*Add the user defined data types. */ /***********************************/ EXEC sp_addtype N’tAppName’, N’varchar (280)’, N’not null' GO setuser GO setuser GO EXEC sp_addtype N’tSessionId’, N’char (32)’, N’not null' GO setuser GO setuser GO EXEC sp_addtype N’tSessionItemLong’, N’image’, N’null' GO setuser GO setuser GO EXEC sp_addtype N’tSessionItemShort’, N’varbinary (7000)’, N’null' GO setuser GO setuser GO EXEC sp_addtype N’tTextPtr’, N’varbinary (16)’, N’null' GO setuser GO /*****************************************************/ /* create the tempdb table objects. */ /* creation takes place in the usraspstate database. */ /*****************************************************/ CREATE TABLE tempdb..ASPStateTempSessions ( SessionId CHAR(32) NOT NULL PRIMARY KEY, Created DATETIME NOT NULL DEFAULT GETDATE(), Expires DATETIME NOT NULL, LockDate DATETIME NOT NULL, LockCookie INT NOT NULL, Timeout INT NOT NULL, Locked BIT NOT NULL, SessionItemShort VARBINARY(7000) NULL, SessionItemLong IMAGE NULL, ) CREATE TABLE tempdb..ASPStateTempApplications ( AppId INT NOT NULL IDENTITY PRIMARY KEY, AppName CHAR(280) NOT NULL, ) CREATE NONCLUSTERED INDEX Index_AppName ON tempdb..ASPStateTempApplications(AppName) go CREATE PROCEDURE DeleteExpiredSessions AS DECLARE @now DATETIME SET @now = GETDATE() DELETE tempdb..aspstateTempSessions WHERE Expires < @now RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[DeleteExpiredSessions] TO [usraspstate] GO CREATE PROCEDURE DropTempTables AS IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = ‘aspstateTempSessions’ AND type = ‘U’) BEGIN DROP TABLE tempdb..aspstateTempSessions END IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = ‘aspstateTempApplications’ AND type = ‘U’) BEGIN DROP TABLE tempdb..aspstateTempApplications END RETURN 0 /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[DropTempTables] TO [usraspstate] GO CREATE PROCEDURE ResetData AS EXECUTE DropTempTables EXECUTE CreateTempTables RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[ResetData] TO [usraspstate] GO CREATE PROCEDURE TempGetAppId @appName tAppName, @appId INT OUTPUT AS SELECT @appId = AppId FROM tempdb..aspstateTempApplications WHERE AppName = @appName IF @appId IS NULL BEGIN INSERT tempdb..aspstateTempApplications (AppName) VALUES (@appName) SELECT @appId = AppId FROM tempdb..aspstateTempApplications WHERE AppName = @appName END RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempGetAppId] TO [usraspstate] GO CREATE PROCEDURE TempGetStateItem @id tSessionId, @itemShort tSessionItemShort OUTPUT, @locked BIT OUTPUT, @lockDate DATETIME OUTPUT, @lockCookie INT OUTPUT AS DECLARE @textptr AS tTextPtr DECLARE @length AS INT DECLARE @now as DATETIME SET @now = GETDATE() UPDATE tempdb..aspstateTempSessions SET Expires = DATEADD(n, Timeout, @now), @locked = Locked, @lockDate = LockDate, @lockCookie = LockCookie, @itemShort = CASE @locked WHEN 0 THEN SessionItemShort ELSE NULL END, @textptr = CASE @locked WHEN 0 THEN TEXTPTR(SessionItemLong) ELSE NULL END, @length = CASE @locked WHEN 0 THEN DATALENGTH(SessionItemLong) ELSE NULL END WHERE SessionId = @id IF @length IS NOT NULL BEGIN READTEXT tempdb..aspstateTempSessions.SessionItemLong @textptr 0 @length END RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempGetStateItem] TO [usraspstate] GO CREATE PROCEDURE TempGetStateItemExclusive @id tSessionId, @itemShort tSessionItemShort OUTPUT, @locked BIT OUTPUT, @lockDate DATETIME OUTPUT, @lockCookie INT OUTPUT AS DECLARE @textptr AS tTextPtr DECLARE @length AS INT DECLARE @now as DATETIME SET @now = GETDATE() UPDATE tempdb..aspstateTempSessions SET Expires = DATEADD(n, Timeout, @now), @lockDate = LockDate = CASE Locked WHEN 0 THEN @now ELSE LockDate END, @lockCookie = LockCookie = CASE Locked WHEN 0 THEN LockCookie + 1 ELSE LockCookie END, @itemShort = CASE Locked WHEN 0 THEN SessionItemShort ELSE NULL END, @textptr = CASE Locked WHEN 0 THEN TEXTPTR(SessionItemLong) ELSE NULL END, @length = CASE Locked WHEN 0 THEN DATALENGTH(SessionItemLong) ELSE NULL END, @locked = Locked, Locked = 1 WHERE SessionId = @id IF @length IS NOT NULL BEGIN READTEXT tempdb..aspstateTempSessions.SessionItemLong @textptr 0 @length END RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempGetStateItemExclusive] TO [usraspstate] GO CREATE PROCEDURE TempInsertStateItemLong @id tSessionId, @itemLong tSessionItemLong, @timeout INT AS DECLARE @now as DATETIME SET @now = GETDATE() INSERT tempdb..aspstateTempSessions (SessionId, SessionItemLong, Timeout, Expires, Locked, LockDate, LockCookie) VALUES (@id, @itemLong, @timeout, DATEADD(n, @timeout, @now), 0, @now, 1) RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempInsertStateItemLong] TO [usraspstate] GO CREATE PROCEDURE TempInsertStateItemShort @id tSessionId, @itemShort tSessionItemShort, @timeout INT AS DECLARE @now as DATETIME SET @now = GETDATE() INSERT tempdb..aspstateTempSessions (SessionId, SessionItemShort, Timeout, Expires, Locked, LockDate, LockCookie) VALUES (@id, @itemShort, @timeout, DATEADD(n, @timeout, @now), 0, @now, 1) RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempInsertStateItemShort] TO [usraspstate] GO CREATE PROCEDURE TempReleaseStateItemExclusive @id tSessionId, @lockCookie INT AS UPDATE tempdb..aspstateTempSessions SET Expires = DATEADD(n, Timeout, GETDATE()), Locked = 0 WHERE SessionId = @id AND LockCookie = @lockCookie RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempReleaseStateItemExclusive] TO [usraspstate] GO CREATE PROCEDURE TempRemoveStateItem @id tSessionId, @lockCookie INT AS DELETE tempdb..aspstateTempSessions WHERE SessionId = @id AND LockCookie = @lockCookie RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempRemoveStateItem] TO [usraspstate] GO CREATE PROCEDURE TempResetTimeout @id tSessionId AS UPDATE tempdb..aspstateTempSessions SET Expires = DATEADD(n, Timeout, GETDATE()) WHERE SessionId = @id RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempResetTimeout] TO [usraspstate] GO CREATE PROCEDURE TempUpdateStateItemLong @id tSessionId, @itemLong tSessionItemLong, @timeout INT, @lockCookie INT AS UPDATE tempdb..aspstateTempSessions SET Expires = DATEADD(n, Timeout, GETDATE()), SessionItemLong = @itemLong, Timeout = @timeout, Locked = 0 WHERE SessionId = @id AND LockCookie = @lockCookie RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempUpdateStateItemLong] TO [usraspstate] GO CREATE PROCEDURE TempUpdateStateItemLongNullShort @id tSessionId, @itemLong tSessionItemLong, @timeout INT, @lockCookie INT AS UPDATE tempdb..aspstateTempSessions SET Expires = DATEADD(n, Timeout, GETDATE()), SessionItemLong = @itemLong, SessionItemShort = NULL, Timeout = @timeout, Locked = 0 WHERE SessionId = @id AND LockCookie = @lockCookie RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempUpdateStateItemLongNullShort] TO [usraspstate] GO CREATE PROCEDURE TempUpdateStateItemShort @id tSessionId, @itemShort tSessionItemShort, @timeout INT, @lockCookie INT AS UPDATE tempdb..aspstateTempSessions SET Expires = DATEADD(n, Timeout, GETDATE()), SessionItemShort = @itemShort, Timeout = @timeout, Locked = 0 WHERE SessionId = @id AND LockCookie = @lockCookie RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempUpdateStateItemShort] TO [usraspstate] GO CREATE PROCEDURE TempUpdateStateItemShortNullLong @id tSessionId, @itemShort tSessionItemShort, @timeout INT, @lockCookie INT AS UPDATE tempdb..aspstateTempSessions SET Expires = DATEADD(n, Timeout, GETDATE()), SessionItemShort = @itemShort, SessionItemLong = NULL, Timeout = @timeout, Locked = 0 WHERE SessionId = @id AND LockCookie = @lockCookie RETURN 0 GO /**********************************/ /* Grant usraspstate permissions. */ /**********************************/ GRANT EXECUTE ON [dbo].[TempUpdateStateItemShortNullLong] TO [usraspstate] GO /********************************************************/ /* Grant usraspstate permissions on the tempdb objects. */ /********************************************************/ use tempdb go if not exists (select * from dbo.sysusers where name = N’usraspstate’ and uid < 16382) EXEC sp_grantdbaccess N’usraspstate’, N’usraspstate' GO exec sp_addlogin ‘usraspstate’,’usraspstate' GO exec sp_addrolemember N’db_datareader’, N’usraspstate' GO exec sp_addrolemember N’db_datawriter’, N’usraspstate' GO GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [usraspstate] GO GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [usraspstate] GO GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [public] GO GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [public] GO /************************************/ /*Now resize the .MDF & .LDF Files. */ /************************************/ USE master GO ALTER DATABASE aspstate MODIFY FILE (NAME = ‘aspstate’,SIZE = 20MB) GO ALTER DATABASE aspstate MODIFY FILE (NAME = ‘aspstate_log’,SIZE = 5MB) GO /*******/ /*DONE */ /*******/ France. The finest wines. The tastiest cuisine. The most delicious women.

KBAhttp://support.microsoft.com/kb/223999 Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

]]>