SQL Server 2005 SP2
Using SSMS, I've just backed up my transaction log (which is around 314
megs) on a smallish database. I purposely generated a bunch of dummy
transactions to grow the trans log.
I scripted the action in SSMS, and it said this:
BACKUP LOG [test] TO DISK = N'C:\data\test.trn' WITH NOFORMAT, INIT,
NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS
= 10
GO
However, when its done, my trans log is still 314 megs. Shouldn't the
BACKUP LOG command truncate the log?
I used DBCC LOGINFO to figure out where the VLF w/ status=2 is, and its
at the bottom of the many VLF's which I think explains why the trans log
is not being truncated.
Why is this happening? How do I actually truncate the log?
When/Why would I use DBCC shrinkfile rather than relying on BACKUP LOG
to decrease the size of the log file?
Thanks in advance for any help..
aj 3 3713
Some more info:
dbcc opentran says I have no open transactions
dbcc sqlperf(logspace) says that I am using 4% of my log space for this
DB, w/ a log size of around 314 megs...
aj wrote:
SQL Server 2005 SP2
Using SSMS, I've just backed up my transaction log (which is around 314
megs) on a smallish database. I purposely generated a bunch of dummy
transactions to grow the trans log.
I scripted the action in SSMS, and it said this:
BACKUP LOG [test] TO DISK = N'C:\data\test.trn' WITH NOFORMAT, INIT,
NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS
= 10
GO
However, when its done, my trans log is still 314 megs. Shouldn't the
BACKUP LOG command truncate the log?
I used DBCC LOGINFO to figure out where the VLF w/ status=2 is, and its
at the bottom of the many VLF's which I think explains why the trans log
is not being truncated.
Why is this happening? How do I actually truncate the log?
When/Why would I use DBCC shrinkfile rather than relying on BACKUP LOG
to decrease the size of the log file?
Thanks in advance for any help..
aj
The log file size does not change when it is backed up. The data
inside the log is truncated internally, and that space in the file is
now free. You can shrink the log (DBCC SHRINKFILE) but before you do
that you have to read the article: http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Roy Harvey
Beacon Falls, CT
On Tue, 08 Jul 2008 12:27:06 -0400, aj <ro****@mcdonalds.comwrote:
>SQL Server 2005 SP2
Using SSMS, I've just backed up my transaction log (which is around 314 megs) on a smallish database. I purposely generated a bunch of dummy transactions to grow the trans log.
I scripted the action in SSMS, and it said this:
BACKUP LOG [test] TO DISK = N'C:\data\test.trn' WITH NOFORMAT, INIT, NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
However, when its done, my trans log is still 314 megs. Shouldn't the BACKUP LOG command truncate the log?
I used DBCC LOGINFO to figure out where the VLF w/ status=2 is, and its at the bottom of the many VLF's which I think explains why the trans log is not being truncated.
Why is this happening? How do I actually truncate the log? When/Why would I use DBCC shrinkfile rather than relying on BACKUP LOG to decrease the size of the log file?
Thanks in advance for any help..
aj
Thanks for clarifying this, Roy. I believe I grok it now, and for me,
the key was to understanding the difference between allocated space and
used space. I was mixing the 2 up.
DBCC SHRINKFILE is used to shrink the /allocated/ space, assuming that
there is available non-used (free) space in the file. The allocated
space is the total size of the file on the file system.
A BACKUP LOG is used to back up, and then truncate the /used/ space that
is within the allocated space. This does not change the size of the
allocated space - it only affects how much of that allocated space is
actually used.
A trans log can be 100 megs in size, and you can be using only a few
megs of actual space in it. You can check this w/
DBCC SQLPERF(LOGSPACE).
thanks
aj
Roy Harvey (SQL Server MVP) wrote:
The log file size does not change when it is backed up. The data
inside the log is truncated internally, and that space in the file is
now free. You can shrink the log (DBCC SHRINKFILE) but before you do
that you have to read the article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Roy Harvey
Beacon Falls, CT
On Tue, 08 Jul 2008 12:27:06 -0400, aj <ro****@mcdonalds.comwrote:
>SQL Server 2005 SP2
Using SSMS, I've just backed up my transaction log (which is around 314 megs) on a smallish database. I purposely generated a bunch of dummy transactions to grow the trans log.
I scripted the action in SSMS, and it said this:
BACKUP LOG [test] TO DISK = N'C:\data\test.trn' WITH NOFORMAT, INIT, NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
However, when its done, my trans log is still 314 megs. Shouldn't the BACKUP LOG command truncate the log?
I used DBCC LOGINFO to figure out where the VLF w/ status=2 is, and its at the bottom of the many VLF's which I think explains why the trans log is not being truncated.
Why is this happening? How do I actually truncate the log? When/Why would I use DBCC shrinkfile rather than relying on BACKUP LOG to decrease the size of the log file?
Thanks in advance for any help..
aj
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Andrew E |
last post by:
I just took over DBA responsibilities for an Oracle 8i database
running on Linux. Although I've been working with relational
databases for some time, I'm a bit green on Oracle so forgive me.
My...
|
by: John S |
last post by:
Hi All,
I have several questions about checking backup file :
a. When SQL Server receive a command :
BACKUP DATABASE test to DISK = '\\pc1\netdb\BTEST',
does it verify BTEST file before...
|
by: Jay |
last post by:
I've recently inherited a position where I am responsible for the well-being
of some DBs.
2 (much) more important than others.
The current recovery model, from what I can tell, is to do a full...
|
by: Matt |
last post by:
I run SQL Server 2000 and use thier database maintenance plans to
backup my databases. My questions is what happens if a change is being
made to a database table while a backup is running? Should I...
|
by: jag |
last post by:
hi,
I am thinking about backup strategy. I have couple of questions for my
db2 community brothers.
1. Can i use full online or offline database backup as base for
incremental backup for...
|
by: dcruncher4 |
last post by:
It is possible that we may be asked to restore a production tape, say 3
yrs later. We would prefer
redirect restore for that. I am documenting a process to do a redirect
restore. We take...
|
by: abelbfilho |
last post by:
I have a backup image with name
DADOSADV.0.DB2.NODE0000.CATN0000.20060812230006.001 and I want to
restore it on Windows (in another server).
In Windows Server I created this folder structure:...
|
by: aj |
last post by:
DB2 LUW v8.2 FP 14 RHAS 2.1
I have a DB2 online DB backup that was done w/ the INCLUDE LOGS
option. I am interested in restoring that backup, and rolling
forward ONLY the logs contained in the...
|
by: ARC |
last post by:
Does anyone know if you can add your own items to the round office button in
the top left? I'm guessing the answer is no, and that you can only hide the
default items, but it would be great if you...
|
by: clilush |
last post by:
I've been working with db2 8.2fp14 for over a year now and was
wondering what is a "standard" way of doing a backup to provide 24/7
operations with disaster recovery capabilities?
My thoughts were...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |