| Author |
Topic |
|
wided
Posting Yak Master
218 Posts |
 Posted - 2012-11-28 : 04:11:14
|
| HelloI use SQL since version 6 and 7 through 2000 and 2005. Today I am using sql 2008 r2.My problem is that I can not find the équivanent query that will delete the transaction log before truncating it.On earlier versions, I use this:DUMP TRANSACTION WITH NO_LOG MyDatabaseI do not need to save the log.Thank you to answer me |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-28 : 04:33:23
|
Set the database recovery model to simple if you don't need to save the log and then you don't have to bother about log entries of finished transactions.
Too old to Rock'n'Roll too young to die. |
 |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-11-28 : 06:10:46
|
| Use the following query to minimize the transaction log fileDBCC SHRINKFILE (LogFileName, 100)Where LogFileName is the transaction log file name and 100 is the amount of space you are shrinking to. For e.g. if the transaction log file is 2GB and you if you give 100, it reduces the file to 100MB. |
|
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2012-11-28 : 10:41:53
|
| SHRINKFILE reduces fileThis is possible even if it is full? |
|
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2012-11-28 : 10:42:30
|
| SHRINKFILE reduces fileThis is possible even if it is full? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-28 : 11:14:16
|
quote:
Originally posted by wided
SHRINKFILE reduces fileThis is possible even if it is full?
As far as I know SHRINKFILE removes the unused space so the answer is no.
Too old to Rock'n'Roll too young to die. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-28 : 22:20:45
|
| Do you backup Log? How frequently?? |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-11-29 : 00:57:39
|
| Yes, Shrink file reduces even if log file is full. Unused space cannot be removed from Sql server database/tables using Shrink File, we have to alter the table with rebuild or create Cluster Index on the tables. We shrink log files from 500GB to 100MB at times as long as log is not using the space. |
|
|
|
johnson_ef
Starting Member
16 Posts |
Posted - 2012-11-30 : 04:44:59
|
| What I understood from the query is:Your DB's Log usage is an issue for you. It seems the DB is in 'FULL' recovery mode. If it's in 'Simple', you might not raise this issue. Because SQL Engine (The transaction log is implemented as a separate file or set of files in the database. The log cache is managed separately from the buffer cache for data pages, which results in simple, fast, and robust code within the Database Engine.) will take care of it.Since its in Full recovery mode\model, you can opt out either 2 option1)Make the DB recovery into 'Simple' and let SQL manage it its own2)Setup a Log backup Job, which will flush out committed transactions logs into Transaction Log backup file.What I understood from your query (I do not need to save the log), I assume you are least bothered about Log backup. In that case, you can overwrite the backup file, so that, this will also save the backup file storage space.Shrinking is not advisable in well managed environment. You can use it for workaround to gather some space for some reason.-Johnson |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-30 : 05:32:47
|
| >> delete the transaction log before truncating itWhat do you mean by this?You shouldn't be reducing the size of the log except in exceptional circumstances - it will only grow again and just use resources. In the worst case something else will grab the space and you are dead in the water.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-05 : 19:46:52
|
| If your log is large, you used to be able to call a command to "kill it". Now you can't.You can either change to simple recovery and shrink it. Or like I needed to, back it up/ shrink it, and repeat that a second time. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-06 : 04:05:04
|
| As a last resort, if you have a single database and log file, you can detach the database, delete the log file then attach the database. A new log file will be created - do a full backup first.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2012-12-19 : 09:32:24
|
| thank you to everyoneI tried DBCC SHRINKFILE, but it does not workNobody told me how empty this paper, it was possible before with the DUMP TRANSACTION command. If I find the equivalent it will solve my problems |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-26 : 09:32:02
|
quote:
I tried DBCC SHRINKFILE, but it does not work
No, Shinkfile on it's own will not work. You need to back it up first.Shinkfile will get of unused "stuff" in the file. But if you don't back it up, SQL Server stance is, that it is holding important information in the log file, so it won't let you release it with Shrinkfile.Please try my suggestion, I re-pasted it:You can either change to simple recovery and shrink it. Or like I needed to, back it up/ shrink it, and repeat that a second time. |
|
|
|
ovc
Starting Member
35 Posts |
Posted - 2013-01-02 : 16:59:49
|
| there are 3 options:1.set the recovery mode to simple2.back up the transaction log and save the t-log3.back up the transaction log and not save it to any file (if you do not want to save the transaction log you can backup to the null device)BACKUP LOG [dbname] TO DISK = 'NUL:' |
|
|
|
|
Transaction Log