Printable View
Hey Chaps,
Anyone got any suggestions:
I need to backup and restore a database to a new server. I don't want the log (which is over 1 gig in size). I back up and restore fine it takes a long time though. I'm wondering what I can do T-SQL wise or thru the manager to backup the DB without a log (will that work on restore ????)
Cheers
Chubby..
You really should truncate the log - so it's shrunken down to a good size - before the backup...
I've seen so many postings on MS Tech Net about this - and I still use a crazy system of my own to make a log shrink.
My logs go from huge to 1024KB with this technique.
In query analyzer do:
[edit] DO NOT FOLLOW the steps in this POST - go to POST # 21 for proper instructions!!!!!!!
[edit] - SEE POST #21 below - this TRUNCATE ONLY is dangerous!!!
[edit] - SEE POST #21 below - this TRUNCATE ONLY is dangerous!!!Code:BACKUP LOG FUNDS WITH TRUNCATE_ONLY
In the example above the DB name is FUNDS.
This does not do a backup - it simply cleans up and makes ready the log for truncate.
Then - this is the crazy part (and maybe someone knows a better technique - vbDBA??)
I go into ENTERPRISE MANAGER - right click on the DATABASE
ALL TASKS>SHRINK DATABASE>
Do not PRESS OK on this SCREEN - it will shrink the MDB
Click the [FILES...] button
Change the drop-down box to the LOG file.
Click the radio-button that says "Truncate free space from the end of the file"
Click OK - the log file is now 1024KB
Make sure to click CANCEL to get off the screen - as you are right back to the SHRINK DATABASE window!
Seems crazy - but it takes a multi-gig log and makes it 1024KB.
Now you can do you BACKUP with the standard backup/restore commands - like...
Code:--BACKUP DATABASE Funds
-- TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_NB_May21.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\acs desktop\Funds_May28.bak'
RESTORE DATABASE Funds
FROM DISK = 'c:\acs desktop\Funds_May28.bak'
WITH MOVE 'Funds_data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Funds.mdf',
MOVE 'Funds_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Funds.ldf'
GO
:eek: That is crazy..... but it works right? Hmmmm..... I think we have a couple of clients for which this could be handy.
Tg
(I'd rate, but I musta have done that for you recently as it won't let me at the moment - ggrrr!)
Quote:
Originally Posted by szlamany
Whoah !
That seems like it would work but I'm sh-scared of crashing a live client database :eek: ......
There is an option called NO LOG or something I read somewhere too....
Will more than likely use your example tho as I've no real time to kick this about...
Thanks!
Chubby..
TG and Chubby - it does work - I've done it on production machines with live data...
I'm sure the ENTERPRISE MANAGER steps could be turned into a simple QA T-SQL command - I simply don't know what it is.
All the MS Tech Net articles I read say that the nightly maintenance plan is supposed to keep the logs shrunk - but I don't see that happen often at my customer sites!
That is pretty "baked", but if it works (and I'm going to try it), it is worth the effort.
I'm "bookmarking" this thread!
Same thing happened to me. Double ggrrrQuote:
Originally Posted by techgnome
I found this script from one of the SQL Server sites that I frequent, can't remember which one though. The comments says that it's for 7.0, but I've tweaked it to work on SQL 2000. I use this quite regularly and have never had a bit of problem with it. That being said, use at your own risk.
You can either shrink the log to a desired size (I use 2MB as my default) or you can shrink it by a certain percentage.
Code:--CREATE proc sp_force_shrink_log
--------------------------------------------------------------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
-- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
-- [email protected]
--------------------------------------------------------------------------------
-- These were the input parameters
Declare @target_percent tinyint
, @target_size_MB int
, @max_iterations int
, @backup_log_opt nvarchar(1000)
--as
set nocount on
--initialize the input parameters
Set @target_percent = 0
Set @target_size_MB = 2
Set @max_iterations = 1000
Set @backup_log_opt = 'with truncate_only'
declare @db sysname
, @last_row int
, @log_size decimal(15,2)
, @unused1 decimal(15,2)
, @unused decimal(15,2)
, @shrinkable decimal(15,2)
, @iteration int
, @file_max int
, @file int
, @fileid varchar(5)
select @db = db_name()
, @iteration = 0
--drop table #loginfo
--drop table #logfiles
create table #loginfo (
[id] int identity
, FileId int
, FileSize numeric(22,0)
, StartOffset numeric(22,0)
, FSeqNo int
, Status int
, Parity smallint
, CreateLSN float
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( [id] int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid )
select convert( varchar, fileid )
from sysfiles
where status & 0x40 = 0x40
select @file_max = @@rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId
, FileSize
, StartOffset
, FSeqNo
, Status
, Parity
, CreateLSN )
exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
select @log_size = sum( FileSize ) / 1048576.00
, @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00
, @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select @unused1 = @unused -- save for later
select 'iteration' = @iteration
, 'log size, MB' = @log_size
, 'unused log, MB' = @unused
, 'shrinkable log, MB' = @shrinkable
, 'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
while @shrinkable * 100 / @log_size > @target_percent
and @shrinkable > @target_size_MB
and @iteration < @max_iterations
begin
select @iteration = @iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @file = 0
while @file < @file_max
begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
end
exec( 'backup log [' + @db + '] ' + @backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId
, FileSize
, StartOffset
, FSeqNo
, Status
, Parity
, CreateLSN )
exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
select @log_size = sum( FileSize ) / 1048576.00
, @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00
, @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @iteration
, 'log size, MB' = @log_size
, 'unused log, MB' = @unused
, 'shrinkable log, MB' = @shrinkable
, 'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end
if @unused1 < @unused
select 'After ' + convert( varchar, @iteration ) + ' iterations the unused portion of the log has grown from ' +
convert( varchar, @unused1 ) + ' MB to ' + convert( varchar, @unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where @unused > 10
union all
select 'you may try running this procedure again with a higher number of iterations.' where @unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10
else
select 'It took ' + convert( varchar, @iteration ) + ' iterations to shrink the unused portion of the log from ' +
convert( varchar, @unused1 ) + ' MB to ' + convert( varchar, @unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
GO
vb_dba - I'm sad - I figured you would come along and give us a simple one line T-SQL to take the place of the mess of steps I'm doing in EM...
Why is it so hard to shrink a LOG FILE?
Well....you could create a stored procedure out of that, then you'd have your one-line TSQL statement. ;)
It's an issue that everyone who uses SQL Server has to deal with and unfortunately, MS doesn't provide an easy solution to deal with it. Maybe Yukon or SQL 2005 or whatever they are calling it now, will provide an easier method to shrink the log.
Very funny...Quote:
Originally Posted by vb_dba
Oh well - I guess I'll stick with my crazy EM multi-step nightmare - you can use your 45 page SPROC with iterations ( ;) )- and we will all just wait till MS gives us a better answer!
The really bad thing about all this is that I've got customers that end up with 7 gig log files - start running out of room - and they don't have the comfort level or experience to want to do either technique!
When you do a full database backup (BACKUP DATABASE) it doesn't backup the transaction log.Quote:
Originally Posted by Chubby
Is it? Have you tried DBCC SHRINKFILE after you have truncated the log? I think that is what EM executes when you choose to truncate the log file.Quote:
Originally Posted by szlamany
But why do you really want to shrink the log file anyway, unless disk space is low or the file has grown extremely large due to many transactions since last backup.
Feel free to give me the exact syntax that would be used after the BACKUP LOG {dbname} WITH TRUNCATE_ONLY that would physically change the LOG size to 1024KB.Quote:
Originally Posted by kaffenils
I will test it - I've got several servers running SQL right now - and get back to you with my results.
According to BOL the syntax would beQuote:
Originally Posted by szlamany
But I'm not sure if it will shrink the file if the log file is in use.Code:DBCC SHRINKFILE (MyDB_Log,1)
Perfect - this appears to work.
Shrunk the LOG to 1024KB.Code:backup log acctfiles with truncate_only
go
DBCC SHRINKFILE (acctfiles_Log,1)
go
And I was in query analyzer connected to that DATABASE - so my guess is that it will work with the DB in use.
You obviously need to know the name of the "log" for the database - this can be found several ways - they are not always DBNAME_LOG.
Rep points for KAFFENILS!! :)
[edit] Apparently I have given to KAFFENILS recently - I cannot rep again - argh!
Most of the time it's because I am ripping a copy of a production DB and putting it on my laptop or our test server at the shop.Quote:
Originally Posted by kaffenils
Also, I'm not always so sure about the "shrinking" due to backup/maintenance plans. I just looked at a log at one of my customer sites - they do a backup twice a day (so no users in DB since yesterday - at least one backup done since then) - the log is 1.5 gig. That's an absurd size for a log.
Well, rules are not always fair :cry:Quote:
Originally Posted by szlamany
But who cares. The main goal is to help each other, right! ;)
The log file for our document management system has stabilized at about 3,5GB event though I do hourly transaction log backups and these files are only about 100K. I have no idea either why the log file grows to this absurd size.Quote:
Originally Posted by szlamany
I found this for 7.0...
After working some threads today and doing some research I found that...
TRUNCATE_ONLY is dangerous - it leaves the database in a non-recoverable state - a full backup is needed as soon as possible to protect the DB.
It seems a proper method to TRUNCATE a log file is to:
This successfully truncated the logfile to 1 MB on this test file.Code:BACKUP LOG ACCTFILES TO DISK='C:\somefilename'
GO
DBCC SHRINKFILE (ACCTFILES_LOG,1)
GO
edit: see this link http://www.vbforums.com/showpost.php...70&postcount=8