Log file bigger than data file

Question

Post reply

Log file bigger than data file

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

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

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 1

Hello,

Try this ...

USE DatabaseName

GO

DBCC SHRINKFILE(<TransactionLogName>, 1)

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)

GO

Hope this helps ...

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Shree_h Mr or Mrs. 500 Points: 538 More actions · Answer 2

Hi...

You can try this steps:

Truncate the log file --- and shrink it to minimum size possible.

This is the code...

USE DatabaseName

GO

DBCC SHRINKFILE(<TransactionLogName>, 1)

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)

GO

Safe to have a full backup before running the above...

Hope this helps..

Thanks

Shree

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

shripati (1/20/2010)
Hi...
You can try this steps:
Truncate the log file --- and shrink it to minimum size possible.
This is the code...
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Safe to have a full backup before running the above...
Hope this helps..
Thanks
Shree

SQL_Quest-825434 (1/20/2010)
Hello,
Try this ...
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Hope this helps ...
\\K

You both know that

1) BACKUP LOG dbname WITH TRUNCATE_ONLY has been depreciated and may not be supported in future versions of SQL Server

2) BREAKS the log chain and requires that a FULL BACKUP be run before any future transaction logs can be run.

Not good advice.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 4

Lynn Pettis (1/20/2010)
shripati (1/20/2010)
Hi...
You can try this steps:
Truncate the log file --- and shrink it to minimum size possible.
This is the code...
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Safe to have a full backup before running the above...
Hope this helps..
Thanks
Shree
SQL_Quest-825434 (1/20/2010)
Hello,
Try this ...
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Hope this helps ...
\\K
You both know that
1) BACKUP LOG dbname WITH TRUNCATE_ONLY has been depreciated and may not be supported in future versions of SQL Server
2) BREAKS the log chain and requires that a FULL BACKUP be run before any future transaction logs can be run.
Not good advice.

Lynn .. the reason I use Truncate_only is that we have to do staging refresh every month, and so while doing refresh I use :

USE DatabaseName

GO

DBCC SHRINKFILE(<TransactionLogName>, 1)

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)

GO

This will truncate and reduce the file size. (I know that it is depreciated but have no other alternative to shrink the db).

Also as it is staging, so after restore, and truncating and shrinking log files, I put the db in simple recovery.

Do you have any better way to achieve this ? (We really dont care about recovery in staging as we can restore from a full backup of production). :hehe:

Thanks,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

wes.r.peterson Grasshopper Points: 13 More actions · Answer 5

Even if the backups are running the file size could be the same because of empty space. You might want to Shrink the log file which will remove all the empty space in the file.

In SSMS select your database and go to tasks and shrink and you can release un-used space.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

SQL_Quest-825434 (1/20/2010)
Lynn Pettis (1/20/2010)
shripati (1/20/2010)
Hi...
You can try this steps:
Truncate the log file --- and shrink it to minimum size possible.
This is the code...
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Safe to have a full backup before running the above...
Hope this helps..
Thanks
Shree
SQL_Quest-825434 (1/20/2010)
Hello,
Try this ...
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Hope this helps ...
\\K
You both know that
1) BACKUP LOG dbname WITH TRUNCATE_ONLY has been depreciated and may not be supported in future versions of SQL Server
2) BREAKS the log chain and requires that a FULL BACKUP be run before any future transaction logs can be run.
Not good advice.
Lynn .. the reason I use Truncate_only is that we have to do staging refresh every month, and so while doing refresh I use :
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
This will truncate and reduce the file size. (I know that it is depreciated but have no other alternative to shrink the db).
Also as it is staging, so after restore, and truncating and shrinking log files, I put the db in simple recovery.
Do you have any better way to achieve this ? (We really dont care about recovery in staging as we can restore from a full backup of production). :hehe:
Thanks,
\\K

If you are doing it in staging, that is one thing. The problem is recommending this without knowing where this may be done. This is NOT something to do in a production environment.

I do a similar thing when restoring production databases to development servers.

christopherdee Valued Member Points: 63 More actions · Answer 7

Hello,

This happens a fair bit, especially when you have your database recovery model set to 'FULL'. If you're certain that you are also backing up your Transaction log files, then you can run SQL similar to the example below to shrink your transaction log files. With this method you don't have to worry with any 'TRUNCATE' issues and the trick is to temporarily set your recovery model to simple, and then revert it back to full. This shouldn't also affect any full backup chains.

/* Shrink Database Transaction log file sizes in SQL Server 2005/2008 */

USE DBNAME -- Data database file name, use [] around the name if you're getting errors locating the database --

GO

Alter Database DBNAME Set Recovery Simple -- Data database file name --

DBCC SHRINKFILE ('DBNAME_log', 200) -- Log file database name and size in MB --

Alter DBNAME Set Recovery Full -- Data database file name --

-- Now check the recovery model for your database to make sure it's back to FULL --

USE Master

SELECT Name, Recovery_Model_Desc FROM Sys.Databases

where Name = 'DBNAME'

jspalding Valued Member Points: 72 More actions · Answer 8

I've had a similar issue. Try the following, not sure what your database name is, for example below yourdbname.

use yourdbname

go

sp_helpdb yourdbname

go

sp_spaceused @updateusage=true

go

/* Look at the name field to get the log file to shrink */

dbcc shrinkfile (yourdbname_Log,100)

go

/* check the size of the log file to see if it's smaller now. */

sp_helpdb yourdbname

go

gaojia2004 SSC-Addicted Points: 409 More actions · Answer 9

USE [master]

GO

--Change DB recovery mode

ALTER DATABASE [DATABASENAME] SET RECOVERY SIMPLE WITH NO_WAIT

GO

USE [DATABASENAME]

GO

-- Shrink DB LOG

DBCC SHRINKFILE (N'DATABASELOGFILENAME' , 0, TRUNCATEONLY)

GO

USE [master]

GO

--Change recover mode back

ALTER DATABASE [DATABASENAME] SET RECOVERY FULL WITH NO_WAIT

Hope this can help you, note that backup dara and log of you database before you shrink log.

Ol'SureHand SSCrazy Points: 2744 More actions · Answer 10

Krasavita (1/18/2010)
My .bak is 20.1 GB, my last for today morning .trn 655 KB

Judging by this, your combined data + log files are not bigger than 20 GB actual data therefore the log file is large as a result of some one-off setup/delete operation or before the tran-log backups started, as Steve guessed as well.

You may need to manually shrink the log file as a one-off operation then see how low it can go.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 11

one thing everyone forgets

make sure your database is checkpointing - the log data is not truncated if no checkpoint has occured

try the following

use mydb

go

checkpoint

go

backup log mydb to disk='xxxxxxxx'

go

dbcc shrinkfile(2,1)

go

MVDBA

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 12

michael vessey (1/26/2010)
one thing everyone forgets
make sure your database is checkpointing - the log data is not truncated if no checkpoint has occured
try the following
use mydb
go
checkpoint
go
backup log mydb to disk='xxxxxxxx'
go
dbcc shrinkfile(2,1)
go

Does this disturb your log chain ?

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 13

nope - a checkpoint marks the log entries as inactive - therforethey will be cleared down when the log is backed up

ini some unusual circuimstances automatic checkpointing can become disabled - it just needs re-starting by issueing a new checkpoint in that database

actually checkpoint does a lot more than that, but as far as you are concerned it may be the issue

http://msdn.microsoft.com/en-us/library/aa226036(SQL.80).aspx

there may be one other cause - if you are using replication and the log reader is not running (or the database is a backup of a database that is a publisher) then you will also have a problem of large log files - as it is waiting for the log reader agent to mark the entries as "good to truncate"

unlikely, but worth considering

MVDBA

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 14

A couple of previous suggestions recommend changing the recovery model to SIMPLE and back to FULL. That should only be used as a last resort because it will break the log chain.

If that is the only way you can get the log truncated so you can perform a shrink, then make sure you immediately follow up the process with a full backup to re-instate the log chain.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

lisbon SSC Enthusiast Points: 102 More actions · Answer 15

Hi,

As noted before, if you always do full backups you can change the recovery model to SIMPLE in database options and the log will shrink after each backup.

Best,

lx