SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


shrinking logfile in SQL 2008


shrinking logfile in SQL 2008

Author
Message
Nikhil-843675
Nikhil-843675
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 99
free_mascot (2/9/2009)
If your database is Production than after changing db back to Full recovery mode do not forget to take full backup.




Please do reply over here..that after changing model to full,were u able to shrink it or problem persists...
terryj30
terryj30
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 261
if this is not a production db then maybe your best option will be to kill all the connections backup the db detach the database delete the tranlog manullay and re-attach I really wound not recomend this but it will create you a new tranlog that is empty. Or back up the db in simple and dump the log with no_log then run dbcc shrinkfile.

the dbcc opentran will show you the connections type
kill spid where spid = say 17

terry
GilaMonster
GilaMonster
SSC Guru
SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)

Group: General Forum Members
Points: 414804 Visits: 47140
terryj30 (2/9/2009)
I really wound not recomend this but it will create you a new tranlog that is empty.


I would not recommend that under any circumstances. It's the quickest way to get a corrupt and unusable database.

Or back up the db in simple and dump the log with no_log


In simple recvery,no backup log is necessary. A checkpoint truncates the log.
Backup log ... with no_log is no longer available in SQL 2008. It will throw an error saying unrecognised backup option.

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


EdVassie
EdVassie
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25988 Visits: 3918
Repeated shrinking and growing a database file will harm your performance. You should not shrink a database file unless you know there will be a permanent reduction in the space needed.

There are lots of forum postings on this subject, and the harm that repeated file shrinking does to your performance. It is worth using Google, etc, to find them and take the time to understand what they are saying.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
terryj30
terryj30
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 261
ed is quite right however, I am guessing you are shrinking as you are running out of disk-space as that is the only reason I can think off. 2008 is rather tricky I am wondering if you have also looked into the compression aspect of sql20008

Terry
EdVassie
EdVassie
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25988 Visits: 3918
If you are having to shrink files because you are running out of disk space then you need to get more disk space. Repeated shrink and grow of database files will harm your performance, whatever the reason is for why you are doing it.

If your log file is growing 'too big', how often are you doing a log backup? You may find that doing a log backup every 3 hours (or more often if needed) will stop your transaction log getting 'too big'.

If you are using Full Recovery or Bulk Logged Recovery, then you need to do log backups as well as database backups. Books Online (BOL) explains why this is necessary, and there are lots of forum posts on this subject you can find with Google.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
DBA in Unit 7
DBA in Unit 7
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1679 Visits: 1124
Are the transactional logs backed up regularly?

If not, do a transactinal log backup first (regular transactional log backup), then shrink log will work.
If the logs are not backed up, they will not be shrinkable, coz they are deemed as being in use.
terryj30
terryj30
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 261
try this
dbcc opentran database
go
kill spid
go
alter database database set recovery simple
go
checkpoint
go
alter database database set recovery full
go
backup database pubs to disk = 'c:\database.bak' with init
go
dbcc shrinkfile (N'database_log' , 1)
go
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 627
Hi - I know this topic is a little dead, because it was posted in February, However I am going through this same problem now as we just completed a 2008 upgrade.

We backup our database nightly and take transaction log backups every half hour. One db we have is about 5 gigs and the log file is usually about 2 - 2.5 gigs. If a large transaction occurs though, like in loading a new table or something of that nature, the log will grow an equivalent size. As this is not the normal size of the log file, it makes sense to shrink it to conserve disk space. I know some folks would love to throw more disk space at the problem, but another philosophy is to mange the files. If you're shrinking your log file every day that's a problem, but if this is an occasional thing, that makes sense to me. Also, I don't think that your log file should exceed your database size, which will inevitably happen if you leave it alone.

It seems ridiculous that you should need to change your recovery model in order to complete this task. This is not the way that it worked in 2005 and it seems a shame that you should have to jump through hoops to do this simple task.

I would love to hear if anyone comes up with a more reasonable work around.
m
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34812 Visits: 10097
mark (5/12/2009)
Hi - I know this topic is a little dead, because it was posted in February, However I am going through this same problem now as we just completed a 2008 upgrade.

We backup our database nightly and take transaction log backups every half hour. One db we have is about 5 gigs and the log file is usually about 2 - 2.5 gigs. If a large transaction occurs though, like in loading a new table or something of that nature, the log will grow an equivalent size. As this is not the normal size of the log file, it makes sense to shrink it to conserve disk space. I know some folks would love to throw more disk space at the problem, but another philosophy is to mange the files. If you're shrinking your log file every day that's a problem, but if this is an occasional thing, that makes sense to me. Also, I don't think that your log file should exceed your database size, which will inevitably happen if you leave it alone.

It seems ridiculous that you should need to change your recovery model in order to complete this task. This is not the way that it worked in 2005 and it seems a shame that you should have to jump through hoops to do this simple task.

I would love to hear if anyone comes up with a more reasonable work around.
m


Why would you need to manage the files? This should be a dedicated LUN for the log file only - with enough space to handle all of the transactions your system runs. Repeated shrinking and growing of the transaction log will cause performance issues.

If this is not a dedicated LUN - and you want to be able to 'manage' the file, then what happens when you run your transaction that needs additional space available in the log and there is no space on the disk to grow? I can tell you, your system will come to a screeching halt and no further transactions will be possible until you clear the transaction log and/or add space.

It really does not make any sense at all to be shrinking the log file.

Now, if there is an extraordinary event that you do not anticipate happening again (ever...), then yes it is okay to shrink. Again, if this happens on a regular basis you are much better off keeping the transaction log the max size it needs (actually, make it a bit larger).

Please review the article in my signature on managing transaction logs.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search