SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Transaction Log
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wided
Posting Yak Master

190 Posts

Posted - 11/28/2012 :  04:11:14  Show Profile  Reply with Quote
Hello

I 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 MyDatabase

I do not need to save the log.

Thank you to answer me

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 11/28/2012 :  04:33:23  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 11/28/2012 :  06:10:46  Show Profile  Reply with Quote
Use the following query to minimize the transaction log file

DBCC 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.
Go to Top of Page

wided
Posting Yak Master

190 Posts

Posted - 11/28/2012 :  10:41:53  Show Profile  Reply with Quote
SHRINKFILE reduces file
This is possible even if it is full?
Go to Top of Page

wided
Posting Yak Master

190 Posts

Posted - 11/28/2012 :  10:42:30  Show Profile  Reply with Quote
SHRINKFILE reduces file
This is possible even if it is full?
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 11/28/2012 :  11:14:16  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by wided

SHRINKFILE reduces file
This 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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/28/2012 :  22:20:45  Show Profile  Reply with Quote
Do you backup Log? How frequently??
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 11/29/2012 :  00:57:39  Show Profile  Reply with Quote
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.

Edited by - srimami on 11/29/2012 00:58:46
Go to Top of Page

johnson_ef
Starting Member

India
16 Posts

Posted - 11/30/2012 :  04:44:59  Show Profile  Reply with Quote
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 option
1)Make the DB recovery into 'Simple' and let SQL manage it its own
2)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

Edited by - johnson_ef on 11/30/2012 04:47:12
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/30/2012 :  05:32:47  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
>> delete the transaction log before truncating it
What 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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
581 Posts

Posted - 12/05/2012 :  19:46:52  Show Profile  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/06/2012 :  04:05:04  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

wided
Posting Yak Master

190 Posts

Posted - 12/19/2012 :  09:32:24  Show Profile  Reply with Quote
thank you to everyone

I tried DBCC SHRINKFILE, but it does not work

Nobody 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



















Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
581 Posts

Posted - 12/26/2012 :  09:32:02  Show Profile  Reply with Quote
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.

Go to Top of Page

ovc
Starting Member

Romania
35 Posts

Posted - 01/02/2013 :  16:59:49  Show Profile  Reply with Quote
there are 3 options:
1.set the recovery mode to simple
2.back up the transaction log and save the t-log
3.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:'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000