Link to home
Start Free TrialLog in
Avatar of Fig68
Fig68

asked on

SLQ transacion log file too large

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 :(
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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.
Avatar of Fig68
Fig68

ASKER

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? User generated image
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
Avatar of Fig68

ASKER

Do I need to stop any services/anything or just run it in the Query?

Thanks for all your help.
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."




Avatar of Fig68

ASKER

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.
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.
Avatar of Fig68

ASKER

I'm sorry do you mean offline? "Do not take it on-line"

Thanks for all the help :)
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.
Avatar of Fig68

ASKER

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


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;
Avatar of Fig68

ASKER

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

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.
Avatar of Fig68

ASKER

So what would you recommended to get this log file under control?
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial