Solved

SLQ transacion log file too large

Posted on 2011-02-16
17
621 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

751 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