How to truncate log file in SQL Server 2005

Question

Post reply

How to truncate log file in SQL Server 2005

Viewing 15 posts - 31 through 44 (of 44 total)

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

liewsb SSC-Addicted Points: 482 More actions · Answer 1

liewsb

SSC-Addicted

Points: 482

October 10, 2010 at 8:07 pm

#1234746

Hi Brandie,

Thanks for your reply.

I understand better the simple and full recovery mode at the time being. I think I will consider these two model for my database. Thanks Brandie.

Regards,

liewsb SSC-Addicted Points: 482 More actions · Answer 2

Hi Gail / Brandie,

SO, just to confirm that these are the right steps to carry out:

1. Changing my database recovery model to simple, and immediately run a checkpont. (Is this ok.)

2. Shrink the log file. (When Can I run the command, immediately after execuate the checkpoint command, or ..?)

And Gail, Need your kind advice on this also:

Based on the current size of my log (around 390GB), what is the recommanded or optimal target size that I should shrink to.

You have mentioned that checkpoint need to run for only one time. Let say after I have shrunk down the log file size, do I still need to run the checkpoint in future? For example:

If on Full recovery model, I will need to do a log backups to remove the inactive log records in the log. DO I still need to run a check point ?

If on Simple model, log backups cannot be done. Do I need to run a checkpoint again in order to remove the inactive log records as mentioned in your article. If yes, how frequent do I need to do that.

Again, I would like to appreciate all your helps and please bear all these questions. Thanks...

Regards,

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

In order

Yes

Absolutely no idea (it's your database, you must have some idea how busy it is)

No

Once more: SQL runs checkpoints automatically. You typically do not have to do so manually.

Please, open books online and read up on the commands that we're talking about. It honestly sounds like you haven't done any reading on the subjects at hand. It may well answer many of your questions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

liewsb SSC-Addicted Points: 482 More actions · Answer 4

Hi Gail,

Thanks. Yes, I will need to do more reading on these subjects..

I will do the shrinking of log file tomorrow morning as have a database backup scheduled tonight.

Thanks.

Regards,

GilaMonster (10/10/2010)

In order

Yes

Yes

Absolutely no idea (it's your database, you must have some idea how busy it is)

No

No

No

Once more: SQL runs checkpoints automatically. You typically do not have to do so manually.

Please, open books online and read up on the commands that we're talking about. It honestly sounds like you haven't done any reading on the subjects at hand. It may well answer many of your questions.

shri.rapala Newbie Points: 3 More actions · Answer 5

shri.rapala

Newbie

Points: 3

October 11, 2010 at 5:11 am

#1234895

Hi,

Use databasename

backup log database with truncate_only

dbcc shrinkfile(logfilename) -----> sp_helpdb dbname

Regards,

Sridhar

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

shri.rapala (10/11/2010)

Use databasename

backup log database with truncate_only

dbcc shrinkfile(logfilename) -----> sp_helpdb dbname

No, no, no!

Absolutely terrible advice to be giving, especially without any mention of what those command do.

Please read all the replies on this thread to see why that's a bad idea.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

liewsb SSC-Addicted Points: 482 More actions · Answer 7

Hi Gail,

I will carry out to shrink the log file this morning, here are the steps to carry out:

1. Change the Database recovery model to simple.

2. Execuate checkpoint command;

3. Shrink the log to 15GB (15G = 15360MB):

DBCC SHRINKFILE(HW_Stats_Log, 15360)

Sorry to just confirm that can I proceed with it?

Regards,

GilaMonster (10/11/2010)

shri.rapala (10/11/2010)

Use databasename

backup log database with truncate_only

dbcc shrinkfile(logfilename) -----> sp_helpdb dbname

No, no, no!

Absolutely terrible advice to be giving, especially without any mention of what those command do.

Please read all the replies on this thread to see why that's a bad idea.

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 8

liewsb (10/11/2010)

Hi Gail,

I will carry out to shrink the log file this morning, here are the steps to carry out:

1. Change the Database recovery model to simple.

2. Execuate checkpoint command;

3. Shrink the log to 15GB (15G = 15360MB):

DBCC SHRINKFILE(HW_Stats_Log, 15360)

Sorry to just confirm that can I proceed with it?

If you haven't already, you should be able to proceed. You're following Gail's advice, whereas the last poster was advising you to do something different.

EDIT: Something I forgot. Is the base size of your log file equal to or less than 15360MB? If it's bigger, I believe you won't be able to shrink down your log file that much. You can find out by right clicking the database in SSMS and choosing Properties -> Files. Initial Size is what you're looking for.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

liewsb SSC-Addicted Points: 482 More actions · Answer 9

Hi Gail, Brandie,

I really appreciate the helps granted by both of you to me.

I managed to shrink down my log file yesterday as per advice by Gail.Hence, this is the command I used:

DBCC SHRINKFILE(HW_Stats+Log, 15360)

which I intented to shrink the file down to 15GB.

After shrinking, the file size is 15,768,960KB. The following is the output obtained:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

7 2 1971120 128 1971120 128

And, my database recovery model is currently set at Simple and I have decided to adpot this model.

My understanding and questions:

1. Why the Currentsize shown is 1971120MB and the physical log file shown is 15,768,960KB.

2. Can I shrink down the size of my log file further, say 2GB? Noted that the base size of my database log is set at 15400(MB).

2. Understand that the checkpoint operation is now running at backend, and will periodiclly shrink down the log file by removing inactive logs. Is that means that the log file size will not grow unexpectedly as like before? And, do I still to shrink it down manually in future?

Pls advise. Thanks for bearing with me..

Regards,

michal_marek Old Hand Points: 336 More actions · Answer 10

Hi,

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

7 2 1971120 128 1971120 128

My understanding and questions:

1. Why the Currentsize shown is 1971120MB and the physical log file shown is 15,768,960KB.

2. Can I shrink down the size of my log file further, say 2GB? Noted that the base size of my database log is set at 15400(MB).

3. Understand that the checkpoint operation is now running at backend, and will periodiclly shrink down the log file by removing inactive logs. Is that means that the log file size will not grow unexpectedly as like before?

4. And, do I still to shrink it down manually in future?

1. UsedPages = CurrentSize = 1971120,

Pages, no MB...

One page = 8KB

1971120*8KB = 15768960 KB,

15768960 KB /1024 = 15399,375 MB

15399,375 MB / 1024 = 15 GB

2. Yes, but only if is free space in log_file (when you want run "shrink"), OR if you use recovery_model = SIMPLE

3. Log file size will not grow unexpectedly as like before if you use recovery_model = SIMPLE,

4. Yes, but...

Do you really wont to use recovery_model = SIMPLE ??

Best regards,
Michał Marek

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 11

liewsb (10/13/2010)

After shrinking, the file size is 15,768,960KB. The following is the output obtained:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

7 2 1971120 128 1971120 128

1. Why the Currentsize shown is 1971120MB and the physical log file shown is 15,768,960KB.

It's not. You're not reading the output correctly. It's listing number of 8KB pages, not the size in Megabytes. See Books Online for DBCC Shrinkfile, halfway down the article, for more details on the output results.

liewsb (10/13/2010)

2. Can I shrink down the size of my log file further, say 2GB? Noted that the base size of my database log is set at 15400(MB).

3. Understand that the checkpoint operation is now running at backend, and will periodiclly shrink down the log file by removing inactive logs. Is that means that the log file size will not grow unexpectedly as like before? And, do I still to shrink it down manually in future?

RE #2: You can so long as the log doesn't need that space for other transactions.

RE #3: Don't manually shrink the log file unless something drastic happens, like a runaway transaction that takes up all the disk space. As far as unexpected growth? That depends on the transactions you run.

One important thing to remember: just because you can't back up the transaction log in SIMPLE mode doesn't mean the transaction log isn't being used. Transactions are minimally logged, so the file is still used, and you might just have enough transactions going on that it requires all that space. Which means that the log file can and probably will still grow.

Personally, I'd leave it as it is for a while and monitor it. Better to have just a little extra space than to shrink it too low and have the drive thrash itself to death (and the database performance slow down) every time the log file needs to grow to accommodate your db workload.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

Brandie Tarvin (10/13/2010)

One important thing to remember: just because you can't back up the transaction log in SIMPLE mode doesn't mean the transaction log isn't being used. Transactions are minimally logged, so the file is still used,...

Operations that can be minimally logged will be. There's not that many of those.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saidapur_satish SSC Veteran Points: 268 More actions · Answer 13

saidapur_satish

SSC Veteran

Points: 268

October 18, 2010 at 11:52 pm

#1238837

Hi,

If i take regular log backup then any problem for shrik

With Regards

Satish

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 14

Please start a new thread for your question. Most people consider this one resolved and won't answer.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

homebrew01 SSC Guru Points: 55454 More actions · Answer 15

Back to the OPs backup schedule. I think I read that you are only doing FULL backups 3 times a week ? If data changes frequently, and especially if this is a production database, maybe you should schedule your full backup more freuently. Consider how much data loss is acceptable, and schedule the backups (and recovery model) to match. If it is ok to lose the data in between the backups, then leave it as it is.