Possible "Create Database" problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Possible "Create Database" problem?

Author

Topic

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-04 : 04:20:34

Hi all,

Got a problem with a stored proc that deletes and recreates a database when the cluster fails over or the server is restarted, the problem is as follows, in bold:

Deleting database file 'D:\Program Files\Microsoft SQL
Server\MSSQL$LBBSQL01\data\aspstate_log.LDF'.
Deleting database file 'D:\Program Files\Microsoft SQL
Server\MSSQL$LBBSQL01\data\aspstate.mdf'.
Server: Msg 5170, Level 16, State 1, Line 7
Cannot create file 'E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\\aspstate_log.LDF'
because it already exists.

This database has been foisted onto me, there is no documentation on it. Here's the procedure that incurs the error, abridged, and as in bold, I think the problem lies with the default mappings in MSDB for the log files:

/*****************************************************************************/
/*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 Solomon 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.
*/
/****************************************************************************/

/************************************/
/*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 */
/*******/

I may make any changes to mappings as necessary to ensure this issue doesn't recur. I'm told the build is SUPPOSEDLY...dbms on C:, data on D: and logs/backups on E:

Advice appreciated,

JB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 04:22:33

It seems the old log file already exists.
Rename the old log file before creating a new database.

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-04 : 04:26:54

quote:
Originally posted by Peso

It seems the old log file already exists.
Rename the old log file before creating a new database.

Peter Larsson
Helsingborg, Sweden

Cheers Pete, but is there any way to rewrite the code to rename/delete it for me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 04:30:22

use xp_cmdshell function with ordinary command prompt style rename

xp_cmdshell "rename E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF.old"

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-04 : 05:02:56

quote:
Originally posted by Peso

use xp_cmdshell function with ordinary command prompt style rename

xp_cmdshell "rename E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF.old"

Peter Larsson
Helsingborg, Sweden

Looks good so far mate, but I fear this will fail on the second running, when first run it will do the rename to *.ldf.old, but then next time the procedure will find an existing *.ldf.old file, it'll return essentially the same error message as before.

JB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 05:22:32

declare @s varchar(8000)

select @s = 'rename E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log(' + convert(varchar, getdate(), 112) + ').LDF'

xp_cmdshell @s

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-04 : 05:55:32

quote:
Originally posted by Peso

declare @s varchar(8000)

select @s = 'rename E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log(' + convert(varchar, getdate(), 112) + ').LDF'

xp_cmdshell @s

Peter Larsson
Helsingborg, Sweden

I think we're almost there, just getting an "Incorrect syntax" error at xp_cmdshell @s. Also, I've placed the code just before the "Create Database" statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 06:13:57

declare @s varchar(8000)

select @s = 'rename "E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF" "E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log(' + convert(varchar, getdate(), 112) + ').LDF"'

xp_cmdshell @s

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-04 : 06:29:30

Nope, still the same error message, but this time at Line 11, here's the code as it stands:

/*****************************************************************************/
/*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

/*******************************/
/*Rename the old logfiles. Edited by King JB, 4-12-2006 */

/*******************************/

declare @s varchar(8000)

select @s = 'rename "E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF" "E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log(' + convert(varchar, getdate(), 112) + ').LDF"'

xp_cmdshell @s

/****************************************************************************/
/*Create the new database, using the default model, therefore no file names
*/
/*required or collation required, or individual objects to be destroyed.
*/
/****************************************************************************/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 07:23:53

Well... Is LBBSV01 the name of the server, or is this a directory name on drive e: ?

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-04 : 08:01:35

quote:
Originally posted by Peso

Well... Is LBBSV01 the name of the server, or is this a directory name on drive e: ?

Peter Larsson
Helsingborg, Sweden

It's both the folder's name on E: and also the physical server name.

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-04 : 11:19:29

It was pretty simple, just needed the 'exec' placed in front of XP_cmd.

Many, many thanks!!

Skol!!

:)

JB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 11:22:11

Cheers back at you!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources