| Author |
Topic |
|
godspeed
Starting Member
6 Posts |
 Posted - 2012-05-10 : 09:21:34
|
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
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-05-10 : 09:51:39
|
| 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.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
godspeed
Starting Member
6 Posts |
Posted - 2012-05-10 : 10:35:11
|
| 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? |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-05-10 : 13:41:08
|
| 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 |
|
|
|
godspeed
Starting Member
6 Posts |
Posted - 2012-05-11 : 07:19:13
|
| @jeffw8713Yes, 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?@DonAtWorkThis thread could be in both subforums. If you believe I am in the wrong one, the administrators can transfer it. :) |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-05-11 : 08:36:46
|
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.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-05-11 : 13:21:44
|
| 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. |
|
|
|
godspeed
Starting Member
6 Posts |
Posted - 2012-05-14 : 12:53:28
|
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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-14 : 13:28:36
|
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. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-05-14 : 14:50:15
|
| 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. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-05-14 : 15:34:58
|
| 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 .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-05-14 : 15:38:33
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-05-14 : 16:13:53
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Does SIMPLE recovery model works as desired..Lets see USE masterIF DB_ID('RecoveryModel') IS NOT NULL DROP DATABASE RecoveryModelGOCREATE DATABASE RecoveryModelONPRIMARY(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%)GOALTER DATABASE RecoveryModel SET RECOVERY SIMPLEdbcc sqlperf(logspace)USE RecoveryModelGOCREATE TABLE T1(c1 int identity primary key, c2 char(3)) INSERT INTO T1 SELECT TOP 100000 'aaa' FROM syscolumns a CROSS JOIN syscolumns bdbcc sqlperf(logspace)checkpointdbcc 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 .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-05-14 : 16:21:32
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-05-14 : 16:35:12
|
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 tkizerDeleting 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 tkizerNow 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 .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-05-14 : 16:59:55
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-05-14 : 17:24:55
|
| 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 .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-05-14 : 18:16:54
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-05-14 : 23:36:51
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-05-15 : 02:38:35
|
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=167644quote:
Originally posted by tkizerSIMPLE 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 tkizerRegardless 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 .... |
|
|
|
Previous Page&nsp;
Next Page
|
Shrink ldf file