Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1119

Re: Oops, transaction log created too large (SS2005)

Show all headers | View raw

On Tuesday, June 12, 2012 11:29:37 AM UTC+1, Erland Sommarskog wrote:
> Erland Sommarskog (esquel@sommarskog.se) writes:
> > I'll ask around to see if there is a better trick that saves you from
> > recreating the database.
> 
> Paul was quick to answer, 

Thanks to you and to Paul Randall!  This server
isn't Enterprise Edition (although we have one),
but the "ATTACH_REBUILD_LOG" method worked.

What I had in mind for a crude useful script is
to examine database metadata and generate SQL 
statements to perform useful tasks, instead 
of using GUI or manual SQL editing from scratch.
What I came up with is what follows; you can 
provide a database name, an optional new 
database name, and up to three search and 
replaces, all of which are substituted in 
OS file names and paths, as well as changing 
logical file names.  I suppose it may be a 
good idea to start with "BACKUP", which I 
skipped because we've got one in this case.
The script doesn't execute the commands, 
that is up to you.  ;-)

SET NOCOUNT ON

DECLARE
      @dbname  sysname, @newdb sysname
    , @part1   varchar(max), @part2 varchar(max)
    , @part3   varchar(max), @part4 varchar(max)
    , @part5   varchar(max), @part6 varchar(max)


SET @dbname = N'FruitDB'

SET @newdb  = N'FruitAndVegDB'

SET @part1 = 'K:';  SET @part2 = 'L:'
SET @part3 = 'M:\'; SET @part4 = 'M:\LOGS\'
SET @part5 = 'LDF'; SET @part6 = 'XDF'


PRINT 'ALTER DATABASE [' + @dbname + '] SET OFFLINE'


SELECT 
      N'ALTER DATABASE ['+ @dbname + '] ' +
      N'MODIFY FILE ( NAME = [' + name + N'],' + 
      CASE 
        WHEN ( @dbname <> @newdb ) THEN 
            N' NEWNAME = [' + REPLACE(name, @dbname, @newdb) + N'],' 
        ELSE
            N'' 
        END +
      NCHAR(13) + NCHAR(10) +
      N'    FILENAME = ''' + 
      REPLACE(REPLACE(REPLACE(REPLACE(
              physical_name
            , @dbname, @newdb)
            , @part1,  @part2)
            , @part3,  @part4)
            , @part5,  @part6) + 
      ''' )' + 
      NCHAR(13) + NCHAR(10) + NCHAR(13) + NCHAR(10) +
      N'EXEC master.dbo.xp_cmdshell ' + NCHAR(13) + NCHAR(10) + 
      N'''move "' + physical_name + N'" "' + 
      REPLACE(REPLACE(REPLACE(REPLACE(
              physical_name
            , @dbname, @newdb)
            , @part1,  @part2)
            , @part3,  @part4)
            , @part5,  @part6) + 
      N'"''' +
      NCHAR(13) + NCHAR(10) + NCHAR(13) + NCHAR(10)
--  SELECT *
FROM sys.master_files WHERE ( database_id = DB_ID(@dbname) )
ORDER BY
      type
    , 
      file_id


IF ( @dbname <> @newdb )
BEGIN
    PRINT ''
    PRINT 'ALTER DATABASE [' + @dbname + '] MODIFY NAME = [' + @newdb + N']'
END


PRINT ''
PRINT 'ALTER DATABASE [' + @newdb + '] SET ONLINE'

PRINT ''
PRINT '-- To detach a database'
PRINT ''
PRINT 'EXEC sp_detach_db @dbname = N''' + @dbname + N''''


PRINT ''
PRINT '-- To attach a database'
PRINT ''
PRINT 'CREATE DATABASE [' + @newdb + N']' 
SELECT 
      CASE WHEN ( file_id = 1 ) THEN 'ON ' ELSE ' , ' END +
      N'(NAME = [' + REPLACE(name, @dbname, @newdb) + N'], ' +
      N'FILENAME = ''' + 
      REPLACE(REPLACE(REPLACE(REPLACE(
              physical_name
            , @dbname, @newdb)
            , @part1,  @part2)
            , @part3,  @part4)
            , @part5,  @part6) + 
      ''' )' + 
      NCHAR(13) + NCHAR(10) 
FROM sys.master_files WHERE ( database_id = DB_ID(@dbname) )
ORDER BY
      type
    , 
      file_id

PRINT N'FOR ATTACH -- WITH <service_broker_option> -- FOR ATTACH_REBUILD_LOG '


PRINT ''
PRINT '-- To test database'
PRINT ''
PRINT 'DBCC CHECKDB (N''' + @newdb + N'''')' 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Oops, transaction log created too large (SS2005) rja.carnegie@gmail.com - 2012-06-11 08:00 -0700
  Re: Oops, transaction log created too large (SS2005) "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-06-11 11:44 -0400
  Re: Oops, transaction log created too large (SS2005) Erland Sommarskog <esquel@sommarskog.se> - 2012-06-11 23:32 +0200
    Re: Oops, transaction log created too large (SS2005) rja.carnegie@gmail.com - 2012-06-11 20:22 -0700
      Re: Oops, transaction log created too large (SS2005) Erland Sommarskog <esquel@sommarskog.se> - 2012-06-12 10:32 +0200
        Re: Oops, transaction log created too large (SS2005) Erland Sommarskog <esquel@sommarskog.se> - 2012-06-12 12:29 +0200
          Re: Oops, transaction log created too large (SS2005) rja.carnegie@gmail.com - 2012-06-13 07:15 -0700

csiph-web