Solved

Will I lose recovery of database if I run this command?

Posted on 2012-03-19
15
224 Views
Last Modified: 2012-06-27
I issued this command and i want to know if we will lose point-in-time recovery if we do this OR can we recover data from the log file even if i run this:

DBCC SHRINKFILE(<TransactionLogName>, 1)

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)
0
Comment
Question by:Camillia
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 37737382
No you should not, shrink only removes the extra white space from the file.

If you did a truncate it would remove all the data from the file.
0
 
LVL 7

Author Comment

by:Camillia
ID: 37737444
I did this as well:
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

so, it didnt truncate the data?
0
 
LVL 25

Expert Comment

by:jogos
ID: 37737595
Truncate only removes space at the end of the file .... if there is something still in use there nothing will be removed.

Check also the file-definitions of that logfile, if it has it's initial size it won't shrink either.

Why do you want to shrink?
What's size, it's initial size and the size of the datafile (.mdf and .ndf's if the exist) and the highest and size of the transaction logs.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 7

Author Comment

by:Camillia
ID: 37737626
>>Check also the file-definitions of that logfile
how do I do this?
0
 
LVL 7

Author Comment

by:Camillia
ID: 37737642
>>What's size, it's initial size and the size of the datafile (.mdf and .ndf's if the exist) and the highest and size of the transaction logs.

LDF file.
Initial size was like 36 GB
I ran those commands and it went to 1024 KB. It's growing again but it's at 56MB something.

So, I didnt lose anything by running those 3 commands? only removed the space? I'm not a DBA and my manager is asking if we lost log data.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37737648
>> BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY <<

That command WILL reset the entire log.  The parts of the log that were in-use will be marked as not in use, and can then be reused / overwritten.  

You CANNOT recover data that was on the log that had not been backed up before the command was issued.

[The SHRINKFILEs do not cause data to be lost, but the truncate of the log DOES.]


You need to take a FULL backup of the database.  At least that way you will have a starting point for a recovery going forward.


Is the database in FULL or BULK-LOGGED recovery mode?



>> Truncate only removes space at the end of the file .... if there is something still in use there nothing will be removed. <<

I think that's completely wrong, as I stated above.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37737676
And if you find the transaction log is growing to big... you can always reduce the time between the transaction log backups (if not recovery model Simple).

And shrinking your log to 1Mb means that after a not so great number of transactions your log file again has to grow. No a good idea... take a fair size.
0
 
LVL 7

Author Comment

by:Camillia
ID: 37737936
so, the short answer is i ran the command and no log data is there anymore, correct?

What should i had done to backup the log first?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37737961
>> so, the short answer is i ran the command and no log data is there anymore, correct? <<

Correct.  [That is my view at least, other posters seem to disagree.]
 

Instead, to keep the log contents:

BACKUP LOG <DatabaseName>
TO DISK = 'd:\path\to\backup\file\DatabaseName_YYYYMMDD_HHMM.TRN'


The default extension for a tran log backup is ".TRN".  Just for consistency with everyone else in the world :-), I suggest you use it too, although of course technically it can be anything you want.
0
 
LVL 7

Author Comment

by:Camillia
ID: 37737970
ah, but i used "truncate_only". I should've backed it up, then truncated and shrinked? correct?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37738010
If you didn't want to lose any log activity, yes.

If the db is in FULL or BULK-LOGGED, SQL does not free log used space until either:
1) you backup the log  OR
2) you truncate the log.
SQL will not re-use/overwrite used log space until one of those two things is done.

Once you do either of those, SQL marks the used log space as unused and it can be re-used/overwritten by SQL.

So, once you issued the truncate command, all the used space was marked as unused.  Even if you take a log backup now, you won't get all that old activity, because it has already been marked as unused, and SQL only backs up used space during a log backup.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37739730
<<ah, but i used "truncate_only". I should've backed it up, then truncated and shrinked? correct?>>
Repeat "And if you find the transaction log is growing to big... you can always reduce the time between the transaction log backups (if not recovery model Simple).

And shrinking your log to 1Mb means that after a not so great number of transactions your log file again has to grow. No a good idea... take a fair size.
"

Check your recovery model of your db, if it is FULL then  you must always take transaction log backups. So you have ability to have a point in time restore and your logs relaese the used space (internal) so it does not have to grow on disk. http://msdn.microsoft.com/en-us/library/ms189275.aspx

Why do you want to truncate and shrink?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37739834
jogos:

Do you retract your statement:
>> Truncate only removes space at the end of the file .... if there is something still in use there nothing will be removed. <<

Or is the affect of a log truncate still in dispute?
0
 
LVL 7

Author Comment

by:Camillia
ID: 37739990
>>Why do you want to truncate and shrink?

We didnt have enough disk space so I ran those commands.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37741104
<<We didnt have enough disk space so I ran those commands.>>
Your database actions need space in the logfile so trowing that space away is only temporary. And having to expand log file when short on space will cause one day or another the error 'unable to allocate space ...' and your transaction will be aborted and everybody will be stuck.. And expanding log file when disk nearly full is definitly generating disk fragmentation.

Do read and respond on suggestions
- what is your recovery model of your databases
- are you taking transaction log backups on databases with recovery model FULL
- what is size of database and size of your log file
- make a plan on how to get more diskspace (logfiles on other disk?)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL help 5 56
Table create permissions on SQL Server 2005 9 43
Strange msg in the SSMS pane 13 58
View SQL 2005 Job package 16 70
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question