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 123»»»

Log space issue Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 4:41 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 8:57 PM
Points: 139, Visits: 404
Hi friends,

here i need some information regarding Log space.

Today i have faced one issue. for one my database log file is occupying 390GB space due to that less space in drive.

so checked in DBCC SQLPERF(LOGSPACE) here log space used is 2.8Gb
select
log_reuse_wait_desc from sys.databases
where name = 'Database' here Nothing is showing.here we can't do shink because this is my production database.

so can anyone suggest me on this how can i fix this issue

my database is in simple recover model
Post #1506070
Posted Friday, October 18, 2013 4:45 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: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
Monitor the log usage for a couple of weeks (especially after maintenance). See from that how large the log needs to be to support the app. Shrink the log to a little larger than that.


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 #1506074
Posted Friday, October 18, 2013 7:20 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 8:57 PM
Points: 139, Visits: 404
thanks for your reply.

How can i monitor the log file. we have 390gb of log file so if i shink we can do it up to 350gb or we can do it more.

Post #1506149
Posted Friday, October 18, 2013 8:18 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: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
The same command you specified in your initial post works fine. Monitor on a regular basis for a couple of weeks, see how big the log needs to be, then you know how large the log needs to be.


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 #1506185
Posted Friday, October 18, 2013 9:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:13 PM
Points: 31,214, Visits: 15,660
You are performing log backups, correct?

These help manage the size of the log.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1506239
Posted Friday, October 18, 2013 10:12 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 8:57 PM
Points: 139, Visits: 404
Hi Steve

here we have a database with simple recovery model
Post #1506256
Posted Friday, October 18, 2013 10:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:13 PM
Points: 31,214, Visits: 15,660
In that case, follow Gail's advice. Record the peak log usage and shrink to that (leave a pad).







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1506260
Posted Friday, October 18, 2013 10:34 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 8:57 PM
Points: 139, Visits: 404
We can monitor but presently we dont have the space in drive we have only 3 gb left. i have to fix this immediately.

can you please advice how can i increase the space for temporarily.then we can monitor the log size.
Post #1506264
Posted Friday, October 18, 2013 10:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:13 PM
Points: 31,214, Visits: 15,660
You can add another log file to the filegroup.

http://technet.microsoft.com/en-us/library/ms189253.aspx#TsqlProcedure







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1506266
Posted Saturday, October 19, 2013 7:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 8:57 PM
Points: 139, Visits: 404
thank you all for your valuable information.

here i have shrink the log now we got 30 gb space. here we have 360gb log now. can i reduce it up to 300gb.

i want to know if we reduce it how it will effect to our database.
Post #1506396
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse