Truncate Log

Question

Post reply

Truncate Log

Viewing 15 posts - 16 through 30 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Krishna Reddy-239657 Old Hand Points: 361 More actions · Answer 1

Follow the below two steps

Step 1.

BACKUP LOG HR_database WITH TRUNCATE_ONLY

Step 2.

DBCC SHRINKFILE(HR_database_log,5)

Your log should come to 5 MB

Cheers

Krishna

SQLBill SSC Guru Points: 51440 More actions · Answer 2

C,

You must not have SQL Server 2000. DUMP is only included for backwards compatibility with earlier versions of SQL Server (6.5, 7).

-SQLBill

C-262216 SSC-Addicted Points: 430 More actions · Answer 3

C-262216

SSC-Addicted

Points: 430

January 20, 2006 at 8:37 am

#616112

SQLBill,

DUMP will work with SQL Server 2000.

Francisco Lopez SSCommitted Points: 1645 More actions · Answer 4

C,

What is the compatability level on the database you are executing "dump tran with no_log" ?

{Francisco}

SQLBill SSC Guru Points: 51440 More actions · Answer 5

C and Francisco,

You both are following up a post that ended in October of 2005.

C,

As for the DUMP command, yes it works in SQL Server 2000, but you should be very careful using a command that is ONLY included for backwards compatibility. BACKUP LOG dbname WITH TRUNCATEONLY is the SQL Server 2000 command.

-SQLBill

ksh SSC Veteran Points: 298 More actions · Answer 6

Have you tried setting the recovery model to simple, then shrinkig the log file?

C-262216 SSC-Addicted Points: 430 More actions · Answer 7

I forgot to mention, I only use the DUMP command in emergency situations.

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 8

This is probably pretty late but if you never found the answer this will work on 2k

DBCC SHRINKDATABASE (N'Hr_database', 0,TRUNCATEONLY)

freddie-541522 Valued Member Points: 67 More actions · Answer 9

Since I got the same problem I use this old thread instead of starting a new one.

I got a SQL 7 server with the problem that the truncate log doesn't shrink after backup.

I got a couple of databases on it but to make it easy lets just talk about one of the databases (solve the problem on one and the solution should work on all of them).

database: riskeraBRT20010418

transactionlog: riskeraBRT20010418_log

Size: 347 Mb

Space availible: 322 Mb

Database options: normal (if this info is of any use).

I tried the following.

1) Made a maintanence plan where I did a full backup of the database + backup of the transaction log. But riskeraBRT20010418_log.ldf is still 353 Mb

2) Tried truncate log in Enterprise manager, made backup manually both full and of the transaction_log, used shrink database (with re-organaize database and shrink database files) It said in the info Allocated 347 Mb, used 22.28Mb). result: database succesfully shrunk to 347 Mb. And no change on the size of the ldf-file.

3) On properties, changed to auto-shrink on the database. No change on the size. (Removed auto-shrink after I saw that nothing changed)

4) Started Query-Analyzer and ran two scripts:

a) BACKUP LOG riskeraBRT20010418 WITH TRUNCATE_ONLY

result: The command(s) completed successfully.

b) DBCC SHRINKFILE (riskeraBRT20010418_log,20)

result:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

-------------------------------------------------------------------------

9 2 44152 63 44152 56

1 row(s) affected

DBCC execution completed. If DBCC printed errormsg contact you sys adm.

And still no change on the size of the ldf-file.

5) I tried on one database that is not used anymore and shut down the SQL and manually deleted the ldf-file. And when I started it had created a new file. Is this a valid way to go (even if I find it really strange that shrinking doesn't work from the Enterprise manager).

Also I have this behaviour on another SQL server (version 2000 this time). the mdf-file being 8Gb and the ldf-file being 9Gb. But let's focus on the SQL7 for the moment

Edit: forgot to mention, recovery model to simple is mentioned, I found that on the SQL 2k server but couldn't find it on the SQL7 server.

/Freddie

CSF01 SSC Journeyman Points: 81 More actions · Answer 10

USE database

GO

DBCC SHRINKFILE (database_log, 1000)

GO

DBCC SHOWFILESTATS

go

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 11

I think sql 7 and 2000 is the same when it comes to truncate and shrink. Just be informed that in most cases database didn't shrink or truncate because it is set (the database) in simple recovery mode. SImple recovery mode in simple explanation is automatically truncating the log thus we can not truncate the log anymore (using EM or QA) not unless you change the recovery mode from simple to full then you can truncate and shrink the tlog using QA. Scripts that are used for truncate and shrink are mentioned in the previous post in this topic so i will not mention this to you. SOme other things that affect the database in truncate and shrink issue is because of its behavior on how it works it is best to know the process of the truncate function (the process and how logs are moved when truncating)

"-=Still Learning=-"

Lester Policarpio

Jim Howell Ten Centuries Points: 1010 More actions · Answer 12

Nita--

This is what we use:

USE your_database

GO

CHECKPOINT

GO

BACKUP LOG your_database WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE (your_database_log, 2) -- this needs to be the name of the log_file from EM

Thx --Jim

jptocker SSC Enthusiast Points: 199 More actions · Answer 13

Hi there,

New to sql server... So I found a 2gb database with a 45gb transaction log. The backup mode is full but its showing no backup of the transaction log.

So I presume I back the file up, then truncate it? Or do I use EM and shrink the file?

Cheers,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Two options. which you take depends on how critical the data is in this database. Do you need to be able to restore the database right up to the second it failed?

Option 1

For if the data isn't so critical, and restoring to last full backup is acceptable.

Switch the database into simple recovery mode

Run a checkpoint (from a query window, select the database, type Checkpoint, run the query)

Shrink the log down to a reasonable size (DBCC shrinkfile ...)

In simple recovery mode the database will truncate automatically on a checkpoint. The truncation will allow space in the log to be reused.

Option 2

For if you do need to do point in time restores.

Back up the transaction log. (backup log to disk ....)

Shrink the transaction log to a reasonable size (DBCC shrinkfile)

Set up a regular job that backs up the transaction log.

Transaction log backups truncate the inactive portion of the log, and allow the space to be reused.

There's no need to truncate the transaction log yourself. It's not something that is recomended.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EdVassie SSC Guru Points: 60476 More actions · Answer 15

shahgols (10/4/2005)
Question for the experts here, is dbcc shrinkfile('log_file', truncateonly) the same as the option "Compress pages and then truncate free space from the file" in EM

The answer is no. The truncateonly option just releases any free space at the end of the file. This applies if the file is a log or data file.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara