Solved

SLQ transacion log file too large

Posted on 2011-02-16
17
608 Views
Last Modified: 2012-05-11
I have a SLQ server with one database on it and the transacion log is growing. Is there any way to get this file down in size without changing the recovery model? Its set to full right now. Ive also read conflicting stories on shrinking the log file.

I need some expert advice as Im running out of room :(
0
Comment
Question by:Fig68
  • 9
  • 7
17 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
only be having regular transaction log backups running, and then doing once a dbcc shrinkfile on the t-log file. it might not work on the first try, but on subsequent attempts, while transactions occuring the db + the transaction logs => moving the internal log writer pointer, it shall complete.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
0
 

Author Comment

by:Fig68
Comment Utility
I'm going to try to shrink the log files but before I do it could someone verify the Query for me, a bit nervous about doing this.

USE Stackvision
GO
DBCC SHRINKFILE(<Stackvision_Log>, 1)
BACKUP LOG <Stackvision> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<Stackvision_Log>, 1)
GO

So if I;m reading this right I'm going to shrink the log, Backup the DB then shrink the log again?
Also what does the "1" stand for at the end of the DBCC SHRINKFILE? SQL
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Here's what I suggest and usually do and please keep in mind that immediately after this operation you should do a FULL database backup:

USE Stackvision
GO
--check file size
SELECT name, size, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO

CHECKPOINT -- commit all uncommited transactions
GO
BACKUP LOG Stackvision WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(Stackvision_Log)
GO

--check file size after shrink
SELECT name, size, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO
0
 

Author Comment

by:Fig68
Comment Utility
Do I need to stop any services/anything or just run it in the Query?

Thanks for all your help.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
You don't need to stop any service however you brought a very good point - you should run this command during a downtime if possible and not while the database is online accessed heavily by users. At least you should find a low activity period and you can run it at that time - weekend?

Having in mind comments bellow from SQL BOL below please run the commands individualy not all together.

http://msdn.microsoft.com/en-us/library/ms189493.aspx

"DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.

When a DBCC SHRINKFILE operation fails an error is raised.

The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run the instance of SQL Server in single-user mode to shrink the system databases."




0
 

Author Comment

by:Fig68
Comment Utility
This database records our emissions so its running 24/7 Should I take the SQL server offline and run the Query?
If so would I just dismount the DB? If not how would I go about taking it offline?

Thanks for the help, I'm very new to SQL servers sorry for all the questions. I just dont want to screw anything up.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Do not take it on-line but there must be a period of time with lower activity against it and run the shrink at that time one command at a time. Remember that the

"DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained."

so nothing bad should happen - just monitor your SQL to ensure normal activity is ok.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Fig68
Comment Utility
I'm sorry do you mean offline? "Do not take it on-line"

Thanks for all the help :)
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Yeah - my bad....DB must stay on-line as you run SQL statement against it and you don't need to put it in single user mode either.
0
 

Author Comment

by:Fig68
Comment Utility
Icohan,
I ran the query and got this error.

(4 row(s) affected)
Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'Stackvision_Log' for database 'StackVision' in sys.database_files. The file either does not exist, or was dropped.

(4 row(s) affected)

Any idea?
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Right and is all my fault. NO_LOG and TRUNCATE_ONLY were discontinued in SQL 2008...
What's the recovery model? If it is full you should be still able to shrink your log by performing following but as per above error you must know your T-log logical file name and replace it in the stmnt below:
Check db properties in SSMS and se what's the file name.

USE [DBname]
GO
DBCC SHRINKFILE (N'DBname_log' , 0, TRUNCATEONLY)
GO


0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
If your DB is in full recovery mode you should perform following if the previous
             DBCC SHRINKFILE (N'DBname_log' , 0, TRUNCATEONLY)

did not shrinked the file enough:

--full backup
BACKUP DATABASE [dbname] TO  DISK = N'E:\SQLBackup\dbname.bak' WITH FORMAT, INIT,  NAME = N'dbname-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--t-log backup
BACKUP LOG [dbname] TO  DISK = N'E:\SQLBackup\dbname_Log.bak' WITH FORMAT, INIT,  NAME = N'dbname-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


--shrink file
DBCC SHRINKFILE (N'TheArchive_log')

--check file size
SELECT name,size,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
0
 

Author Comment

by:Fig68
Comment Utility
Lcohan,  

Thank you for your help I ran this query and has only shrunk the file only mb
84839.6 1st run
84797.2  2nd run
84712.4  3rd run

Shouldnt it shrink more than that?

--check file size
SELECT name, size, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO

USE Stackvision
GO
DBCC SHRINKFILE (StackVision_NEW_Log , 0, TRUNCATEONLY)
GO

--check file size after shrink
SELECT name, size, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO

0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
The file is pretty big and in my opinion should be shrinked and kept (not capped) under control by regular backups at about 2GB instead of 84GB. Your DB is only 17GB and is very likely the log accumulated due to lack of maintenance not soo muchu IO.
0
 

Author Comment

by:Fig68
Comment Utility
So what would you recommended to get this log file under control?
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
First - what is the recovery model of your DB?

If it is FULL then you should do a full backup daily and few T-Log backups a day - like every six hours. This should keep your T-log under control after was shrinked to a more reasonable/manageable size. You dont want to empty it completely because autogrow operations might be costly.

If your DB recovery mode is bullk logged or simple than you have the option to do FULL daily backups and hopefully this should be enough to keep your log under control. If not then you could do a few Differential a day similar to the above T-logs.

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now