unable to shrink logfile - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

unable to shrink logfile

Author

Topic

csaha
Yak Posting Veteran

52 Posts

Posted - 2011-05-09 : 16:39:26

USE [xxx xxx]
GO
DBCC SHRINKFILE (N'xxx xxx_log', 0,TRUNCATEONLY)
GO
Log files shrink with no error, however I wanted to build it with the maintenance plan as a scheduled task but have error messages on running the following query.
USE [xxx]
GO
DBCC SHRINKFILE (N'xxx_log', 0, TRUNCATEONLY)
GO
USE [xxxx]
GO
DBCC SHRINKFILE (N'xxxx_log', 0,TRUNCATEONLY)
GO
Error Executing the query DBCC SHRINKFILE (N'xxx_log', 0, TRUNCATEONLY) failed with the following error:

"Could not locate file 'xxx_log' for database 'master' in sys.database_files.
The file either does not exist, or was dropped. ".
Possible failure reasons: Problems with the query, "ResultSet" property not set
correctly,parameters not set correctly, or connection not established correctly.

How do I correct this error? The logfile names that I have used are correct.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-09 : 16:45:11

You correct it by not shrinking the files! Do not shrink on a schedule as you are causing problems (fragmentation and performance). The database needs this space, so why bother shrinking it?

From the error, it seems you are in the master database which is not correct.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

csaha
Yak Posting Veteran

52 Posts

Posted - 2011-05-09 : 16:56:49

The application slows down because of the log file so I had to shrink.Following is the command that I used
use [IMSA FUND]
go
select * from sys.database_files
go
DBCC SHRINKFILE ('IMSA Fund_log',2, TRUNCATEONLY)

Is this not right?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-09 : 17:02:31

The application is not slowing down because of the log file.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-09 : 17:06:58

Large log files do not cause performance problems. Poorly written queries and poor indexing causes poor performance.

--
Gail Shaw
SQL Server MVP

csaha
Yak Posting Veteran

52 Posts

Posted - 2011-05-09 : 17:16:26

I understand but the log file also grows and they are almost the same size as the database. This is wring right. The vendor had asked me to keep the database in simple mode but I changed that to full mode to do the differential and transactional log backup. Do you think that could cause an issue? Do I change back to simple to avoid the growth of the log file.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-09 : 17:57:10

there are multiple things here and somewhat connected:

(1) How much of data can you afford to lose? If you are in simple mode you cannot restore to a point in time. You can only do full backups. So if your system crashed, you can only restore the most recent full backup and lose all the data entered/modified after the backup was done.
(2) NEVER shrink data files. As several people commented, it will cause data fragmentation and performance problems.
(3) shrinking log files is ok but should not be the first thing you do. If you are in simple recovery mode, you cannot take log backups anyway..
(4) once an appropriate recovery model is in place, take more frequent log backups.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

csaha
Yak Posting Veteran

52 Posts

Posted - 2011-05-09 : 23:28:39

All your comments are useful but I am not sure how I will be resolving my issue. Does the log file grow if the indexing and queries are poorly written.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-10 : 04:28:09

If the DB is in simple recovery, it doesn't need log backups, the log will be marked as reusable on a regular basis.

If you're OK with losing all data back to the last full or diff in the case of a disaster, then it can stay in simple, otherwise consider full recovery and log backups. Depends on your situation.

Large data changes and/or maintenance operations (index rebuilds) cause the log to grow. In general, you should find the size it needs to be for regular operation and leave it at that size. Shrinking will just cause it to grow again and that log grow operation will slow the DB down as operations have to wait until it is complete.

--
Gail Shaw
SQL Server MVP

csaha
Yak Posting Veteran

52 Posts

Posted - 2011-05-11 : 13:15:27

My database is in Full recovery mode and according to the vendor I have to truncate the log file after full backup. I also take differential and transactional log backup. Full backup is successful but I get the following error message. Is it good to truncate log after the full backup.
Failed:(-1073548784) Executing the query "DBCC SHRINKFILE (N'IMSA Fund_log', 0,TRUNCATEONLY)
" failed with the following error: "Could not locate file 'IMSA Fund_log' for database 'master' in sys.database_files. The file either does not exist, or was dropped. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 13:20:57

Your vendor is wrong.

It is NOT good to truncate the log after the full backup. There should be no reason to do this. Your vendor needs to do some reading about SQL Server backups as they clearly are not experienced. Makes me question their knowledge of SQL Server completely.

How often are you running tlog backups?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 13:21:35

And you are getting that error because you are running the statement in the master database. Run it in the right database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

csaha
Yak Posting Veteran

52 Posts

Posted - 2011-05-11 : 14:03:59

I guess I will talk to the vendor that whatever they are suggesting is wrong. I run tlog backup every hour. I have added a task after the fullbackup
USE [IMSA]
GO
DBCC SHRINKFILE (N'IMSA2_log', 0, TRUNCATEONLY)
GO
USE [IMSA Fund]
GO
DBCC SHRINKFILE (N'IMSA Fund_log', 0,TRUNCATEONLY)
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 14:15:34

NO! Don't add a task after the full backup. You are completely misunderstanding backups.

Never truncate the transaction log in full recovery model. If your tlog is growing too big, then run tlog backups more often. We run ours every 15 minutes. If we ran them hourly, we'd have huge tlogs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

csaha
Yak Posting Veteran

52 Posts

Posted - 2011-05-11 : 14:18:27

Thank you! I will schedule my tlogs every 15 mints.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 14:24:07

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-11 : 14:42:21

Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-11 : 15:49:22

To ADD to what has already been said, TRUNCATEONLY is applicable only to data files and not log files so you can remove that option from your command.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

csaha
Yak Posting Veteran

52 Posts

Posted - 2011-05-20 : 16:53:13

My databases are in full recovery mode, and I am taking full backup ones a day, differential backup every 8hrs and transactional log backup every hr and the log files grows to the size of the datafile. If I take frequent transactional log backup (15 mints), will my log files grow less?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-20 : 17:54:48

In general, yes. But it depends if there's a long-running transaction that is causing it. And it depends how big that thing is.

So it depends, but in general, yes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-20 : 19:27:41

If you log files are growing to the same size as the data files - my guess is that you are performing a rebuild/reorganize of every single index in the database. More frequent transaction log backups may not be able to mark the virtual log files as available because of this process.

It is going to depend on how long it takes to either rebuild or reorganize the index. If a single index takes more than 15 minutes to rebuild - the space used in the transaction log will not be marked as reusabled because of the transaction that is open for that process.

Jeff

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources