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 2005 Forums
 Transact-SQL (2005)
 Shrink ldf file
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

godspeed
Starting Member

Greece
6 Posts

Posted - 05/10/2012 :  09:21:34  Show Profile  Reply with Quote
Hello,

I am having a big problem with a database created in SQL server 2005. Its current size is 5GB (mdf file) and its log file (.ldf) has reached the size of 80GB. The next time it will increase it will consume almost all my disk space on the server.

So my question is the following:
Should I use the command on the following page I found on the website below?
http://www.sqlcleanup.com/2008/sql-2005-truncating-log-files-and-recovering-space/

Is there a possibility that it will corrupt the database?
Will I experience any performance issues after shrinking both ldf and mdf files?

And last but not least...How long would this procedure take to complete?

I am sure that is some point of time everyone had faced this problem. At least the experienced ones
Obviously I am kind of... the less experienced users.
What do you do in these cases?

Thanks

DonAtWork
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 05/10/2012 :  09:51:39  Show Profile  Reply with Quote
http://www.sqlservercentral.com/articles/64582/

terrible in that Sqlservercental makes you register to read it, but ok that it is free.

This article is by Gail Shaw, who is VERY good at what she does.









How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

godspeed
Starting Member

Greece
6 Posts

Posted - 05/10/2012 :  10:35:11  Show Profile  Reply with Quote
Thanks DonAtWork for your answer! Very good article. I read it all and now I have a better understanding on what I am going to do. Although Gail does not suggest to shrink the log regularly, I think it's time for me to do it. The specific database is up for some years and I believe it is ok to go for it.

Unfortunately it does not say how it do it. What would you do? Would you use the DBCC SHRINKFILE command?


Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 05/10/2012 :  13:29:53  Show Profile  Reply with Quote
I would say re-word your question, and post it in this forum instead: http://www.sqlteam.com/forums/forum.asp?FORUM_ID=36









How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
808 Posts

Posted - 05/10/2012 :  13:41:08  Show Profile  Reply with Quote
Before you decide to shrink the file, have you setup regular transaction log backups? It sounds like your database is in full recovery model - which requires that the transaction log be backed up on a regular basis.

If you don't need point in time recovery, you could switch the recovery model to simple.

Once you have taken care of the above, then you can perform a one-time shrink of the log file. How that will be done depends on whether or not you are going to keep the recovery model as full - or switch it to simple.

Jeff
Go to Top of Page

godspeed
Starting Member

Greece
6 Posts

Posted - 05/11/2012 :  07:19:13  Show Profile  Reply with Quote
@jeffw8713
Yes, my database is in full recovery model. I have schedule the backup to be done every week.
I would like to keep it this way although I know it will grow again in few years.

Is the command DBCC SHRINKFILE safe to use to shrink it?

@DonAtWork
This thread could be in both subforums. If you believe I am in the wrong one, the administrators can transfer it. :)
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 05/11/2012 :  08:36:46  Show Profile  Reply with Quote
I suggested rewording and posting there because the experts in administration look at that sub-forum more so than this one.









How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
808 Posts

Posted - 05/11/2012 :  13:21:44  Show Profile  Reply with Quote
So, you are not running transaction log backups at least every hour (preferably every 15 minutes)? For databases in full recovery model - you are REQUIRED to perform frequent transaction log backups.

Using DBCC SHRINKFILE will not work because the transaction log is full - there is no room to shrink.
Go to Top of Page

godspeed
Starting Member

Greece
6 Posts

Posted - 05/14/2012 :  12:53:28  Show Profile  Reply with Quote
No, I don't think I am...
I have created a maintenance plan that runs every week. This creates backup files every week of every database (files with .bak extension). I do that using SQL Server Management Studio. I do not know any other way of doing proper backup with SQL server 2005.

Anyway, maybe this is the cause of having the log file size so big.
I need a command that shrinks the log file properly. I don't believe that I need to back up everything every 15 minutes or so. And even if I could, I have no idea how to restore these databases at a specific point of time :P . It seems too advanced for me.

I hope what I say is not too funny.
Sorry about my ignorance. I feel really ... noobie now.

Edited by - godspeed on 05/14/2012 12:54:08
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 05/14/2012 :  13:28:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
I don't believe that I need to back up everything every 15 minutes or so
YES YOU DO.

Unless your database is in Simple recovery, you must run regular transaction log backups. Doesn't have to be every 15 minutes, but that's a good interval if you don't have another one that's better.

If you must use maintenance plans, simply modify your existing maintenance plan and add transaction log backups to it. You'll have to make enough disk space available to hold the entire log, so you'll need 80 GB somewhere for this first log backup.

If you absolutely do not need to keep this log backup, you can cheat by using:

BACKUP LOG myDB TO DISK='NUL'

That will run a backup but not store it to disk. DO NOT use this for regular backups, you're only using it here for an emergency backup to shrink the log.

Once that's done you can shrink the log to a reasonable size. Make sure to leave enough room for the log to process its normal functions, and be sure to back up the log regularly.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
808 Posts

Posted - 05/14/2012 :  14:50:15  Show Profile  Reply with Quote
If you really do not want point in time recovery - and losing a full weeks worth of data is okay, then you can switch the recovery model to simple. Once that is done, you can perform a one time shrink of the log file. When you perform this one-time shrink - do not shrink it all the way down.

Just note that running backups on a weekly basis - and not performing additional backups places you at risk of losing up to a week's worth of data in that database. I would not recommend running weekly full backups, without also putting in daily differentials at least. For a database of that size, I would recommend daily full backups at a minimum.
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 05/14/2012 :  15:34:58  Show Profile  Reply with Quote
Since you can afford a data loss of a week which is quite obvious from the fact that you are taking only full backup at end of week I would suggest(which might sound crazy to few people) just detach the database after the full backup,delete the log file and then attach the database by creating a new log file using rebuild log which will create a brand new log file.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 05/14/2012 :  15:38:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Sachin.Nand

Since you can afford a data loss of a week which is quite obvious from the fact that you are taking only full backup at end of week I would suggest(which might sound crazy to few people) just detach the database after the full backup,delete the log file and then attach the database by creating a new log file using rebuild log which will create a brand new log file.




It'll sound crazy to a lot more than just a few people.

Why wouldn't you just suggest SIMPLE recovery model then?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 05/14/2012 :  16:13:53  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

quote:
Originally posted by Sachin.Nand

Since you can afford a data loss of a week which is quite obvious from the fact that you are taking only full backup at end of week I would suggest(which might sound crazy to few people) just detach the database after the full backup,delete the log file and then attach the database by creating a new log file using rebuild log which will create a brand new log file.




It'll sound crazy to a lot more than just a few people.

Why wouldn't you just suggest SIMPLE recovery model then?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Does SIMPLE recovery model works as desired..Lets see


USE master
IF DB_ID('RecoveryModel') IS NOT NULL DROP DATABASE RecoveryModel
GO
CREATE DATABASE RecoveryModel
ON
PRIMARY
(NAME = N'RecoveryModel', FILENAME = N'D:\RecoveryModel.mdf' , SIZE = 5MB , FILEGROWTH = 10%)
LOG ON
(NAME = N'RecoveryModel_log', FILENAME = N'D:\RecoveryModel_log.ldf' , SIZE = 512kb , FILEGROWTH = 10%)
GO

ALTER DATABASE RecoveryModel SET RECOVERY SIMPLE

dbcc sqlperf(logspace)

USE RecoveryModel
GO


CREATE TABLE T1(c1 int identity primary key, c2 char(3)) 
INSERT INTO T1 SELECT TOP 100000 'aaa' FROM syscolumns a CROSS JOIN syscolumns b


dbcc sqlperf(logspace)
checkpoint
dbcc sqlperf(logspace)


The logsize remains the same in both FULL and SIMPLE model.In my case the log size is 26.17 MB after the insert statement in both the models while the original log size at time of database creation is 0.49 MB.The %log space usage goes down after I issued a manual checkpoint.
You can test it by changing the recovery model from SIMPLE to FULL in the above script.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 05/14/2012 :  16:21:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes I know how it works. I certainly don't need to test this. The point is about what happens after the transaction completes.

Deleting the log file to return it to a small amount is a disaster waiting to happen. It should be done in rare circumstances. SIMPLE recovery model, on the hand, is safe and achieves what the OP needs which is to control its size. Now the OP could just do regular TLOG backups such as every 15 minutes, and that's the best solution here, but it's only needed if PIT recovery is needed which it doesn't sound like since the OP is only doing backups weekly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 05/14/2012 :  16:35:12  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Yes I know how it works. I certainly don't need to test this. The point is about what happens after the transaction completes.



So do you agree there is very minimal difference in a SIMPLE and FULL recovery model when it comes to log size.

quote:
Originally posted by tkizer
Deleting the log file to return it to a small amount is a disaster waiting to happen. It should be done in rare circumstances. SIMPLE recovery model, on the hand, is safe and achieves what the OP needs which is to control its size.



Control of its size ?? How is that going to happen when there is hardly any difference in the log size in both the models which I proved with the example I posted.Also it would be helpful for me if you could elaborate that how it is a disaster waiting to happen ?

That's the reason I said at the first that it would sound crazy to a few people when I suggested about deleting the log files.

quote:
Originally posted by tkizer
Now the OP could just do regular TLOG backups such as every 15 minutes, and that's the best solution here, but it's only needed if PIT recovery is needed which it doesn't sound like since the OP is only doing backups weekly.



So are you saying with SIMPLE recovery model a TLOG backup is possible ? Or you saying OP should change the recovery model to FULL and have a Tlog backup in place.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 05/14/2012 :  16:59:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
The transaction log is used the same for both during the transaction. It's what happens after the transaction completes that is different. This is a topic that I am VERY WELL versed in.

It's a disaster waiting to happen because this is not a recommended method and is unsafe. Wait until the database can't attach anymore.

I am absolutely NOT saying that a tlog backup is possible with SIMPLE. You'll get an error of course.

Here is exactly what I am saying:

The OP should switch to SIMPLE recovery model OR start backing up the tlog every 15 minutes. One or the other, not both.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 05/14/2012 17:02:08
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 05/14/2012 :  17:24:55  Show Profile  Reply with Quote
What you mean by transaction in this context ? Though I have EXPLAINED with an EXAMPLE that keeping the recovery model to SIMPLE WILL NOT minimize the size of log file you still are suggesting to change it to SIMPLE.

And of-course attaching and detaching should be done in controlled environment like everything else in sql server.



After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 05/14/2012 :  18:16:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes it will minimize the transaction log for SIMPLE recovery model. Your small example does not cover the difference between the two, and I really am pressed for time to go into this. But here's some short info.

SIMPLE recovery model means that the transaction clears from the tlog after it completes. Your tlog will only be as big as your largest transactions (could be multiples running at the same time though). FULL means it doesn't clear until a tlog backup.

Regardless if you use your approach of deleting the file, you still the need space for the tlog. If the OP uses FULL recovery model, the tlog will be large at the end of the week. However, if the OP switches to SIMPLE or starts backing up the tlog every 15 minutes, the tlog will be a very manageable size.

Sorry I'm just out of time. I wish Gail still posted on SQLTeam. She would be ALL over this thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 05/14/2012 :  23:36:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
Sachin, to do a test to see what I mean, take your largest database and restore it to a test machine. Set the recovery model to FULL and make sure you do not do any transaction log backups even through an automated job. If any tlog backups occur, it invalidates this test and skews our results. Truncate the transaction log and then shrink it down to like 1GB. Now run ALTER INDEX REBUILD on every index. After it's done, check the size of the transaction log file. You may even run out of space with this test as it's going to keep growing and growing.

Now let's do this again. Restore the database again and then set it to SIMPLE recovery model. Truncate the tlog and then shrink it down to like 1GB. Run the ALTER INDEX REBUILD script again and then check the size of the file.

Each ALTER INDEX is a transaction. This could have been INSERT/UPDATE/DELETE, but it's so much easier to show this concept through ALTER INDEX REBUILD on a large database. Hopefully you've got a big enough database with tons of indexes. Otherwise, this test might not show the difference enough.

The tlog is shrunk down to 1GB here just for these tests. I am certainly not recommending this. This is just for this test to show the difference between the two recovery models.

Also, your test doesn't show the difference between the two models because your test only included one transaction.


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 05/14/2012 23:46:50
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 05/15/2012 :  02:38:35  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Yes it will minimize the transaction log for SIMPLE recovery model. Your small example does not cover the difference between the two, and I really am pressed for time to go into this. But here's some short info.


The example might be to small for you but it was quite enough for me and Gail to come to some fruitful conclusion when we had a similar discussion some time back.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=167644

quote:
Originally posted by tkizer
SIMPLE recovery model means that the transaction clears from the tlog after it completes. Your tlog will only be as big as your largest transactions (could be multiples running at the same time though). FULL means it doesn't clear until a tlog backup.



Its not just a tlog backup that clears the log.A FULL backup or a checkpoint will also clear the log in FULL recovery model.So no need to have a Tlog backup if point in time restore is not desired.

quote:
Originally posted by tkizer
Regardless if you use your approach of deleting the file, you still the need space for the tlog. If the OP uses FULL recovery model, the tlog will be large at the end of the week. However, if the OP switches to SIMPLE or starts backing up the tlog every 15 minutes, the tlog will be a very manageable size.

Sorry I'm just out of time. I wish Gail still posted on SQLTeam. She would be ALL over this thread.



I to wish Gail would had been all over this thread.At least she would had pointed out the shortcomings in my SMALL EXAMPLE instead of outright rejecting it and making vague assumptions.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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