October 18, 2005 at 8:02 am
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
October 18, 2005 at 8:50 am
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
January 20, 2006 at 8:37 am
SQLBill,
DUMP will work with SQL Server 2000.
January 20, 2006 at 10:54 am
C,
What is the compatability level on the database you are executing "dump tran with no_log" ?
{Francisco}
January 20, 2006 at 1:04 pm
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
January 20, 2006 at 1:09 pm
Have you tried setting the recovery model to simple, then shrinkig the log file?
January 26, 2006 at 3:33 pm
I forgot to mention, I only use the DUMP command in emergency situations.
August 2, 2006 at 8:20 am
This is probably pretty late but if you never found the answer this will work on 2k
DBCC SHRINKDATABASE (N'Hr_database', 0,TRUNCATEONLY)
November 6, 2007 at 6:19 am
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
November 8, 2007 at 8:08 am
USE database
GO
DBCC SHRINKFILE (database_log, 1000)
GO
DBCC SHOWFILESTATS
go
November 11, 2007 at 6:13 pm
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
November 12, 2007 at 2:19 pm
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
November 19, 2007 at 7:45 pm
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,
November 19, 2007 at 11:54 pm
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
November 20, 2007 at 5:30 am
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
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply