Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SLQ transacion log file too large

Posted on 2011-02-16
17
Medium Priority
?
646 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34910662
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 40

Expert Comment

by:lcohan
ID: 34911248
0
 

Author Comment

by:Fig68
ID: 34917069
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 40

Expert Comment

by:lcohan
ID: 34917906
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
ID: 34918370
Do I need to stop any services/anything or just run it in the Query?

Thanks for all your help.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 34918523
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
ID: 34918867
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 40

Expert Comment

by:lcohan
ID: 34919273
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
 

Author Comment

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

Thanks for all the help :)
0
 
LVL 40

Expert Comment

by:lcohan
ID: 34919947
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
ID: 34926111
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 40

Expert Comment

by:lcohan
ID: 34926540
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 40

Expert Comment

by:lcohan
ID: 34926656
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
ID: 34926793
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 40

Expert Comment

by:lcohan
ID: 34926976
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
ID: 34927028
So what would you recommended to get this log file under control?
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 34927206
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

971 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