SQL Server Backup Question.-VBForums
Results 1 to 21 of 21

Thread: SQL Server Backup Question.

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2004
    Posts
    308

    SQL Server Backup Question.

    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..

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: SQL Server Backup Question.

    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!!!
    Code:
    BACKUP LOG FUNDS WITH TRUNCATE_ONLY
    [edit] - SEE POST #21 below - this TRUNCATE ONLY is dangerous!!!

    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
    Last edited by szlamany; Oct 26th, 2007 at 07:48 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    PowerPoster
    Join Date
    May 2002
    Posts
    25,961

    Re: SQL Server Backup Question.

    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!)
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2004
    Posts
    308

    Re: SQL Server Backup Question.

    Quote Originally Posted by szlamany
    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:

    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...

    Whoah !

    That seems like it would work but I'm sh-scared of crashing a live client database ......

    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..

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: SQL Server Backup Question.

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: SQL Server Backup Question.

    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!
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  7. #7
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: SQL Server Backup Question.

    Quote Originally Posted by techgnome
    I'd rate, but I musta have done that for you recently as it won't let me at the moment - ggrrr!)
    Same thing happened to me. Double ggrrr
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  8. #8
    Fanatic Member vb_dba's Avatar
    Join Date
    Jun 2001
    Location
    Somewhere aloft between the real world and insanity
    Posts
    1,016

    Re: SQL Server Backup Question.

    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
    -- zanevsky@azdatabases.com
    --------------------------------------------------------------------------------
    
    -- 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
    Chris

    Master Of My Domain
    Got A Question? Look Here First

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: SQL Server Backup Question.

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    Fanatic Member vb_dba's Avatar
    Join Date
    Jun 2001
    Location
    Somewhere aloft between the real world and insanity
    Posts
    1,016

    Re: SQL Server Backup Question.

    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.
    Chris

    Master Of My Domain
    Got A Question? Look Here First

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: SQL Server Backup Question.

    Quote Originally Posted by vb_dba
    Well....you could create a stored procedure out of that, then you'd have your one-line TSQL statement.
    Very funny...

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: SQL Server Backup Question.

    Quote Originally Posted by Chubby
    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..
    When you do a full database backup (BACKUP DATABASE) it doesn't backup the transaction log.

  13. #13
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: SQL Server Backup Question.

    Quote Originally Posted by szlamany
    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?
    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.

    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.
    Last edited by kaffenils; Jun 25th, 2005 at 09:06 AM.

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: SQL Server Backup Question.

    Quote Originally Posted by kaffenils
    Is it? Have you tried DBCC SHRINKFILE after you hav trucated the log?
    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.

    I will test it - I've got several servers running SQL right now - and get back to you with my results.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: SQL Server Backup Question.

    Quote Originally Posted by szlamany
    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.

    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 be
    Code:
    DBCC SHRINKFILE (MyDB_Log,1)
    But I'm not sure if it will shrink the file if the log file is in use.

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: SQL Server Backup Question.

    Perfect - this appears to work.

    Code:
    backup log acctfiles with truncate_only
    go
    DBCC SHRINKFILE (acctfiles_Log,1)
    go
    Shrunk the LOG to 1024KB.

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: SQL Server Backup Question.

    Quote Originally Posted by kaffenils
    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.
    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.

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: SQL Server Backup Question.

    Quote Originally Posted by szlamany
    Perfect - this appears to work.
    [edit] Apparently I have given to KAFFENILS recently - I cannot rep again - argh!
    Well, rules are not always fair
    But who cares. The main goal is to help each other, right!

  19. #19
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: SQL Server Backup Question.

    Quote Originally Posted by szlamany
    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.
    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.

  20. #20
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,829

    Re: SQL Server Backup Question.

    I found this for 7.0...
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  21. #21
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: SQL Server Backup Question.

    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:

    Code:
    BACKUP LOG ACCTFILES TO DISK='C:\somefilename'
    GO
    DBCC SHRINKFILE (ACCTFILES_LOG,1)
    GO
    This successfully truncated the logfile to 1 MB on this test file.

    edit: see this link http://www.vbforums.com/showpost.php...70&postcount=8
    Last edited by szlamany; Oct 19th, 2007 at 08:10 PM. Reason: typo - don't use " quotes around the disk path/filename

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.