Robert Stewart
robert at webedb.com
Thu Sep 10 15:42:15 CDT 2009
John,
What you might want to do is shrink the log file by truncating it
between each of the 10 SPs that you are running. This will keep the
bloat down to a minimum and in the end, drop it down to it's smallest size.
The simplest way of doing it is to do a backup/restore. Then make the
changes oyu want to in it.
Robert
At 12:00 PM 9/10/2009, you wrote:
>Date: Thu, 10 Sep 2009 12:24:56 -0400
>From: jwcolby <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] SQL Server 2005 - Copying a database
>To: Discussion concerning MS SQL Server
> <dba-sqlserver at databaseadvisors.com>
>Message-ID: <4AA92858.3010907 at colbyconsulting.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>Francisco,
>
>Thanks for the response. The situation is that I have a small
>"template" database. The total thing
>is only a few megabytes, but it has a couple of empty "template
>tables" as well as about 30 views
>and about 15 stored procedures.
>
>I then copy that database to a new name, the name of an order such
>as XYZ1209. I go in and tweak a
>couple of pre-existing views and run a stored procedure which in
>turns runs about 10 other stored
>procedures. Those stored procedures build tblOrderData up from
>scratch with field names specific to
>that order, and populates tblOrderData with data pulled from the
>"database from hell".
>
>It is that process of populating tblOrderData, and later doing
>updates to a couple of fields in
>tblOrderData that can cause the log file to swell.
>
>When I am done with this whole process I pretty much just leave that
>order database alone, in fact
>after a couple of months I usually detach the db. I MIGHT have to
>reference tblOrderData in a
>subsequent order to not use records used in a previous order, stuff
>like that. But nothing that
>would ever swell the log file again.
>
>So I need to:
>
>1) Copy the template to a new name
>2) Get the log file in a different drive ("set of spindles") for
>performance reasons.
>3) Shrink the log file at the very end when I am done with the order.
>
>I need to do this from Access - and eventually from C#.Net. Right
>now I have an Access database
>that does all this stuff for me, allows me to execute stored
>procedures in these order databases
>etc. Right now I do those three pieces manually, and it would be
>nice to automate those pieces as well.
>
>I will look at your code to get this done.
>
>Much appreciated.
>
>John W. Colby
>www.ColbyConsulting.com
>
>
>Francisco Tapia wrote:
> > John,
> > It seems that to copy a database it might be easier if you simply backup
> > and restore a database and it could be much quicker and you avoid the log
> > issue, I'm wondering if the log file grows to a ridiculous size because you
> > literally copy data from the existing database to the new
> database. If this
> > is the case doing a restore would allow you to simply restore the
> last known
> > good backup + differential, if you don't to this now, you might consider
> > having your sproc perform a simple step to backup your db as a DIFFERENTIAL
> > backup, then simply run a restore
> >
> > --DIFFERENTIAL BACKUP
> >
> > Declare @Disk AS VARCHAR(1000)
> > SET @Disk = 'E:\DBBAckups\DIFF_DBName_'+ CAST(YEAR(GETDATE()) AS
> > VARCHAR(4)) + RIGHT('0'+ CAST(MONTH(GETDATE()) AS VARCHAR(4)), 2) +
> > RIGHT('0' +CAST(DAY(GETDATE()) AS VARCHAR(4)) ,2)+ '.Bak'
> > BACKUP DATABASE [DBName] TO DISK = @Disk WITH INIT , NAME = N'DBNameDB
> > bkup', NOSKIP WITH DIFFERENTIAL
> >
> >
> > --Then to restore the database you'll need to Restore the FULL
> database with
> > NORECOVERY which puts it into a restore state so you can also apply your
> > DIFFERENTIAL RESTORE.
> >
> >
> > CREATE TABLE #tmpFile (PID INT IDENTITY(1,1)NOT NULL, Result VARCHAR(255))
> > INSERT INTO #tmpFile (Result)
> > EXEC master.dbo.xp_cmdshell 'DIR E:\DBBackups\\FULL*.sqb /B /O-d'
> >
> > Declare @RestoreDate AS DateTime
> > Declare @Disk AS NVARCHAR(1000)
> > SELECT TOP 1 @Disk = 'E:\DBBackups\' + Result FROM #tmpFile
> >
> > --FULL DB RESTORE WITH NORECOVERY
> > SET @Disk = '-SQL "RESTORE DATABASE [DBNAME] FROM DISK = '''+ at Disk+'''
> > WITH NORECOVERY, MOVE ''DataBaseName1'' TO ''D:\Program
> Files\Microsoft SQL
> > Server\MSSQL\Data\DatabaseName1.MDF'', MOVE ''DatabaseName2'' TO
> > ''d:\Program Files\Microsoft SQL
> Server\MSSQL\Data\DataBaseName2.mdf'', MOVE
> > ''DatabaseName_log'' TO ''d:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\DatabaseName_log.ldf'', REPLACE"'
> > EXEC master..sqlbackup @Disk
> >
> > DROP TABLE #tmpFile
> >
> > --Now Restore the DIFFERENTIAL BACKUP
> >
> > CREATE TABLE #tmpFile (PID INT IDENTITY(1,1)NOT NULL, Result VARCHAR(255))
> > INSERT INTO #tmpFile (Result)
> > EXEC master.dbo.xp_cmdshell 'DIR E:\DBBackups\DIFF*.sqb /B /O-d'
> >
> > Declare @RestoreDate AS DateTime
> > Declare @Disk AS NVARCHAR(1000)
> > SELECT TOP 1 @Disk = 'E:\DBBackups\' + Result FROM #tmpFile
> >
> > --DIFF DB RESTORE WITH RECOVERY
> > SET @Disk = '-SQL "RESTORE DATABASE [DBNAME] FROM DISK = '''+ at Disk+'''
> > WITH RECOVERY, MOVE ''DataBaseName1'' TO ''D:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\DatabaseName1.MDF'', MOVE ''DatabaseName2'' TO
> > ''d:\Program Files\Microsoft SQL
> Server\MSSQL\Data\DataBaseName2.mdf'', MOVE
> > ''DatabaseName_log'' TO ''d:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\DatabaseName_log.ldf'', REPLACE"'
> > EXEC master..sqlbackup @Disk
> >
> > DROP TABLE #tmpFile
> >
> >
> >
> >
> > To shrink your log files:
> > BACKUP LOG DATABASENAME WITH TRUNCATE_ONLY ---<<< this just clears the
> > checkpoints from your log file
> > DBCC SHRINKFILE ('Data_LogFile', 1) ---- performs the actual shrinking of
> > the log file which won't happen if you have uncommitted checkpoints in your
> > log file.
> >