November 28, 2008 at 3:00 am
hi all,
i have a problem regarding my data and log files. actually, I am using SQL2K for my production server. my data files is about 37GB and the log file is 13GB. it look like my data and log file is growing. I dont want the data and log grow too much and it will cause disk space, performance, restore and backup. Btw, backup is done daily using tape library.
my question is,
1. can i shrink both of the logs? - I prefer to do using Enterprise Manager to shrink the data and log file. Otherwise, command method will be used.
2. how about the data file? any data loss after shrink the data?
Thanks. any advice?
sham
November 28, 2008 at 4:25 am
Why do you want to shrink? Databases tend to grow as more data gets put in them.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
How much free space is in your data and log file?
What recovery model is the DB in?
How often are log backups been done?
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
November 28, 2008 at 9:34 pm
Log backups will keep the log at a manageable size.
You should have free space in your data files, which is used up as you add data and make changes.
How big are your data backups?
November 29, 2008 at 7:12 pm
Yes, there should be frequent log backups and also use different files for different backups.....
it is always recommended to set the log file value to a size closer to the final value same with the data file.....
As Gila asked, what is your recovery model ?
December 1, 2008 at 2:36 am
Hi,
1st of all, thanks to those who replied my msg.
as requested,
1. disk space as follows, I'm using this command; exec xp_fixeddrives
drive MB free
----- -----------
C 6253
O 18221
V 14334 - data(mdf) is located
W 6751 - log(ldf) is located
(4 row(s) affected)
2. Using Full Recovery
3. Daily backup, dump to tape
any advices are welocome.
Thanks.
December 1, 2008 at 4:02 am
azlisyam79 (12/1/2008)
3. Daily backup, dump to tape
What about transaction log backups? If you're not doing any, there's your problem right there.
If you're not doing log backups, take a look through this article - http://www.sqlservercentral.com/articles/64582/
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
December 1, 2008 at 4:14 am
You can use the below command to shrink the data and log files.This command can be used just before you will taken the Full backup of your database.
DECLARE @Database_Name Varchar(50)
SET @Database_Name = 'My database'
BACKUP LOG @Database_Name WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (@Database_Name, 20);
Now do remember to take a Full Backup of your database.
Thanks
Atul
December 1, 2008 at 4:36 am
atulkumar2 (12/1/2008)
BACKUP LOG @Database_Name WITH TRUNCATE_ONLYDBCC SHRINKDATABASE (@Database_Name, 20);
You neglected to mention that the above statements will
1) break the recovery chain of the database by discarding log records, leaving it impossible to do a point-in-time restore of the database (in the case of disaster) until another full backup is taken
2) completely fragment all of the indexes in the database leading to poor performance until they are all rebuilt.
Also, if the problem is no log backups, that's an interim fix, not a permanent solution.
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
December 1, 2008 at 4:44 am
1. I did mention that the command should be used once the Full Backup is taken. This eliminates the chances of any data loss.
2. Performance will hit, depends on the size and complexity of your database schema. There may be little impact is database is not very huge. One need to take a call based on available resources and best possible solutions.
Regards
Atul
December 1, 2008 at 4:56 am
atulkumar2 (12/1/2008)
1. I did mention that the command should be used once the Full Backup is taken. This eliminates the chances of any data loss.
Actually, it's the other way round (or preferably, both). If a log is truncated and a full backup is not made afterwards, log backups cannot be taken and hence, the best possible recovery (should the DB say fail 6 hours later) is to the full backup made before the truncation. Any log backups made afterwards would have failed (on SQL 2005/2008) or would be completely useless (SQL 2000)
Any time the log is truncated or the recovery model switched to simple and back to full, a full database (or differential database) backup must be taken as soon as possible to restart the log chain and to allow lock backups to run from that point, so that should the database fail, it can be restored to the point of failure.
On shrink, as I mentioned above,
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
Yes, it can be done, but not regularly and not without an understanding of the side effects.
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
December 1, 2008 at 5:05 am
Thanks for pointing out my mistake. Yes, we should truncate the log file just before the Database backup.
I agree with you on usage of Shrink option in database files. One need to understand the side effect.
Thanks
Atul
December 1, 2008 at 5:22 am
atulkumar2 (12/1/2008)
Thanks for pointing out my mistake. Yes, we should truncate the log file just before the Database backup.
I prefer to say that the log should not be truncated at all. Rather backup it up, to disk. The backup log will do the truncation once the backup of the log is complete. Or, if point-in-time recovery is not important, switch to simple recovery.
Lastly, it's deprecated syntax in SQL 2005. It doesn't work at all in SQL 2008.
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
December 1, 2008 at 8:10 am
I agree with Gail. Truncating the log isn't something you want to regularly do. It's kind of an emergency feature, not something you want to use regularly.
December 1, 2008 at 8:43 am
I have to agree as well. Also, if you have the database using full recovery model, you should also be running regularly scheduled transaction log backups. That will control the size of your transaction log.
December 1, 2008 at 7:33 pm
Hi Gila and All of you,
I think this is the actual one compare to to the yesterday.
Database
GeneraAR (Total=39480.81MB); used (32799.56Mb); Free space (6681.25MB)
Transaction Log (Total=20922.73); used (9249.53MB); Free space (11673.2MB)
last database backup was at 2/12/2008; 2.41am
last transaction log backup was at 1/12/200; 9.00pm
Thanks.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply