January 21, 2009 at 3:03 pm
I have tried to shrink my sql 2008 DB using both a sql query and via sql server mgmt studio and it says it has 98% free space but it will not release the space.
Any ideas ?
Thanks
January 21, 2009 at 3:39 pm
you cannot shrink past the active portion of the log. if the log has an active portion near the end of the file it will not shrink beyond that no matter how much free space.
how much space does the data and log file currently occupy
BTW it would help in future if you post in the correct forum for your sql version
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 21, 2009 at 3:39 pm
What file (data or log) are you trying to shrink?
What SQL command did you use to shrink the files?
[note: shrinking a production database is usually a bad idea, unless you have problems with space]
.
January 21, 2009 at 3:45 pm
data -23 GB
log -105 GB
Not looking for an argument, but why does MS offer a shrink capability if it is a bad idea ?
So how do i reduce the log file ?
I have a backup so i dont need it.
January 22, 2009 at 12:36 am
ifila (1/21/2009)
Not looking for an argument, but why does MS offer a shrink capability if it is a bad idea ?
Because there are times when it's necessary. Note that Jacob said it's usually a bad idea, not that it's always a bad idea
So how do i reduce the log file ?
This may be because the virtual logs at the end of the file are in use. Try waiting a while and then shrinking again. It may have given time for the log records to cycle round.
Just shrink the log file, not the database. Use shrinkfile. If you shrink the data file, you'll fragment all of your indexes requiring a rebuild.
What does the following say for the DB in question?
DBCC SQLPERF(logspace)
Considering the size of the log and the size of the DB, what's the recovery model and are you doing log backups? What's the allowable data loss for this system in case of a disaster?
I have a backup so i dont need it.
Yes, you do. The log is not an optional component of the system. You may not need the data inside, but you do need the log itself.
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
January 22, 2009 at 12:48 am
In addition to what Gail said,
are you sure that you are taking TRN LOG backups? Or just database backups only?
You might need to take a transaction log backup, to truncate the log.
.
January 22, 2009 at 5:30 am
log size - 98.7 GB
log space used - 0.61%
status - 0
I did a log file backup prior to the attempted shrink.
I realize that i need the log file, my comment was based on the fact that if the log file was damaged, i could do a DB restore and everything would be fine.
I run a full database backup each day, and have about one weeks worth of daily backups.
Thanks for the help!
January 22, 2009 at 5:40 am
ifila (1/22/2009)
I run a full database backup each day, and have about one weeks worth of daily backups.
Log backups? In full recovery, full backups alone are not sufficient.
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
January 22, 2009 at 5:47 am
Dumb question, but why do you need seperate full and log backups ?
In previous restores from full backups it has restored all files.
Thanks
January 22, 2009 at 6:07 am
regular log backups would stop the transaction log getting huge if in full recovery mode. They also provide point in time restore capability. If all you need is a backup at the end of each day then simple recovery would be more suitable for you
it would pay you to lookup backup, restore and recovery in books online so you understand a little more, also transaction log architecture would be worth reading too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2009 at 6:07 am
regular log backups would stop the transaction log getting huge if in full recovery mode. They also provide point in time restore capability. If all you need is a backup at the end of each day then simple recovery would be more suitable for you
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2009 at 6:10 am
ifila (1/22/2009)
Dumb question, but why do you need seperate full and log backups ?
I'm not saying you need to separate them. I'm saying they're separate things.
Please read through this - Managing Transaction Logs[/url], then go to Books Online and read up the sections BACKUP DATABASE and BACKUP LOG
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
January 22, 2009 at 6:31 am
Update - I tried the dbcc shrinkfile again and it did not reduce the size, so i ran another translog backup. The translog backup from yesterday was 105 GB but the one today was 1.5 GB, so i guess the log cycling had worked. I then ran another DBcc shrinkfile and this time it did reduce the size of the log file to 50 MB.
Thanks for everyone help !
January 22, 2009 at 6:36 am
Great. Now give some though to good log management so that this won't happen in the future.
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
January 23, 2009 at 2:34 am
ifila (1/21/2009)
I have tried to shrink my sql 2008 DB using both a sql query and via sql server mgmt studio and it says it has 98% free space but it will not release the space.Any ideas ?
Thanks
TAKE LOG BACKUP WITH TRUNCATE ..AND SHRINK LOG FILE...
AFTER THAT DON'T FORGOT TO TAKE FULL BACKUP..
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply