Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

shrinking logfile in SQL 2008 Expand / Collapse
Author
Message
Posted Monday, February 9, 2009 4:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 28, 2011 8:38 PM
Points: 53, 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...
Post #652696
Posted Monday, February 9, 2009 4:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 29, 2013 1:32 AM
Points: 1,118, 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
Post #652700
Posted Monday, February 9, 2009 10:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #653035
Posted Tuesday, February 10, 2009 3:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174
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 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #653505
Posted Tuesday, February 10, 2009 7:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 29, 2013 1:32 AM
Points: 1,118, 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
Post #653727
Posted Tuesday, February 10, 2009 7:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174
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 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #653737
Posted Tuesday, February 10, 2009 9:42 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:13 PM
Points: 195, Visits: 1,080
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.

Post #653916
Posted Wednesday, February 11, 2009 11:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 29, 2013 1:32 AM
Points: 1,118, 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
Post #655414
Posted Tuesday, May 12, 2009 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:34 PM
Points: 17, Visits: 572
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
Post #715064
Posted Tuesday, May 12, 2009 8:04 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:12 PM
Points: 4,386, Visits: 9,502
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #715093
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse