Query for log shrink

Oh men
I forgot the query for shrink the log in database SQL server
Please you guys help me out!
regards
ErnestoAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Easiest is to use SSMS
Right click on DBname, => tasks => Shrink => Files
Choose transaction log (filename drop down)
Then shrink option should be REORG before releasing unused space.

In T-SQL
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Open in new window

And to find the log name to use
 SELECT name FROM sys.master_files WHERE type_desc = 'LOG'

Open in new window


BUT Please read : https://www.experts-exchange.com/articles/657/Managing-the-Transaction-Log-for-the-Accidental-DBA.html before you begin.
0
 
ErnestoAuthor Commented:
Oh men,
i run this
USE [1010];  
GO  
DBCC SHRINKFILE ([1011_log], 1);  
GO  

Open in new window


But the Log do not reduced
Please Help!
0
 
Mark WillsTopic AdvisorCommented:
what is your recovery model for that DB ?

When was the last LOG backup ?

You should do a full backup and a log backup. the log backup is an important step.....

The T-SQL I posted above has 3 steps, not 1.

Or, try the SSMS approach. If possible give a a screenshot of the shrink files screen
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ernesto, the transaction log won't be truncated if there are active transactions. You should also perform a transactional backup before running the shrink command.
If you know that you won't need the actual transaction logs for a future Restore operation, then you can simply change the Database Recovery Model to Simple, shrink the log file and change again the Database Recovery Model to Full. Do not forget to perform a Full Backup after, to start a new backup chain or the scheduled transactional log backups will fail.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ernesto, is this issue solved?
A feedback will be appreciated.
Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.