Log file toooo big.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Log file toooo big....

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-06-01 : 04:51:09

Hi all,

firstly thanks for looking...the log file of one our Database's growth to 23G.

I have tried....

USE[DB]
GO
DBCC SHRINKFILE (N'DB_log' , TRUNCATEONLY)
GO

and.....

--Shrink Database
USE[db]
GO
DBCC SHRINKDATABASE(N'db' )
GO

and...

USE[db]
GO
DBCC SHRINKFILE (N'db_log' ,320, TRUNCATEONLY)
GO

finall thought as the above hasnt worked yet.....

If I change the database from Full Recovery mode to Simple Mode, the log file should shink. Am I correct???

then backup db and change it back to FULL; will that work?

Kind regards

Pete

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 05:05:21

You can't shrink the logfile until the content is emptied.
Do a full backup of the database. The logfile will be emptied then.
After this, you can shrink to logfile.

E 12°55'05.63"
N 56°04'39.26"

petek
Posting Yak Master

192 Posts

Posted - 2009-06-01 : 05:09:57

Thanks peso

i did a full backup and then ran a tlog backup; and the tlog backup is still the same size. any ideas??

Kind Regards

Pete.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-01 : 06:02:04

Log backups don't shrink the log. They truncate it. If you want to shrink it (as a once-off operation) use shrink file.
Query sys.databases and check what the value of log_reuse_wait_descr is for this database. Also run DBCC OPENTRAN in that database and see what it comes back with.

Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

p.s. Shrinking the database will have fragmented all of your indexes. Suggest that you schedule an index rebuild as soon as is convenient, for all indexes.

--
Gail Shaw
SQL Server MVP

petek
Posting Yak Master

192 Posts

Posted - 2009-06-01 : 06:18:34

i have tried......

USE[db]
GO
DBCC SHRINKFILE (N'db_log' ,100, TRUNCATEONLY)
GO

but the file remains the same size.....

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 06:51:35

You can't shrink the file to a size smallar than originally set.

E 12°55'05.63"
N 56°04'39.26"

petek
Posting Yak Master

192 Posts

Posted - 2009-06-01 : 07:05:26

what if i do as in my post earlier, can i change the recovery to simple and then change it back to full?

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 07:47:35

Yes, you can, but you will break the backup chain.
Doing this, you can only restore to the latest point in time closest to changing the log mode.
But still, you can't set the size for the log file to a smaller size than it was when originally created.

E 12°55'05.63"
N 56°04'39.26"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-01 : 10:00:25

What's the value of log_reuse_wait_descr in sys.databases?
What does DBCC OPENTRAN return?

Truncate only is not a valid parameter for shrink file when it's the log file. Truncateonly only applies to data files.

--
Gail Shaw
SQL Server MVP

petek
Posting Yak Master

192 Posts

Posted - 2009-06-01 : 10:03:31

Oldest active transaction:
SPID (server process ID): 94
UID (user ID) : -1
Name : UPDATE
LSN : (33798:3317:1)
Start time : Jun 1 2009 3:01:55:173PM
SID : 0x0105000000000005150000008502921f9a94d7f043527648fb470000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

and log_reuse_wait_descr returned.....

LOG_BACKUP

Kind Regards

Pete.

petek
Posting Yak Master

192 Posts

Posted - 2009-06-01 : 10:04:59

DBCC OPENTRAN now says.....

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Kind Regards

Pete.

lightsql
Starting Member

17 Posts

Posted - 2009-06-02 : 01:40:16

DO NOT just shrink transaction logs. Here are the steps you should do.

1. CHECKPOINT -- issue this statement to synchronize log with data
2. BACKUP LOG database_name WITH TRUNCATE_ONLY --this will remove the in-active portion of the log
3. DBCC SHRINKFILE('logical_name_of_log_file', 1000) -- this will shrink the file to 1GB but before that you need to verify the current size of log after step 2 and then add a buffer.

hope this helps!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-02 : 04:19:32

Do not truncate the transaction log. It breaks the log chain and will leave you unable to do point in time restores afterwards until a full backup is taken

What you need to do is run a normal log backup to disk. That's what the sys.databases view is telling you. Once you've run a log backup you should be able to shrink the log. I won't advice you want size, it depends on how much activity you see in the DB and how often you back the log up.

Once you've got things manageable, make sure that you set up regular log backups so that this won't happen again.

And read the article that I linked to above.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources