truncate log as maintenance task

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

Transact-SQL (2005)

truncate log as maintenance task

Author

Topic

Elidas
Starting Member

33 Posts

Posted - 2008-06-23 : 07:39:52

In my maintenance plan task I have added a final step, an execute T-SQL statement, the code is simple, just a truncate of the database log to free space in the server, the code I am using is this on:

use NAV001
GO
DBCC SHRINKFILE(NAV001_Log, 1)
BACKUP LOG NAV001 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(NAV001_Log, 1)

The problem is that I get an error when the maintenance task reach this step, the error log says that:

DBCC SHRINKFILE(NAVISION_PRUEBAS_Log, 1)" failed with the following error: "Could not locate file 'NAV001_Log' for database 'master' in sys.database_files. The file either does not exist, or was dropped.

Why is it trying to use master database if I am telling sql server to "use NAV001" in the first sentence?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 07:59:18

might be of help:-

http://dynamicsuser.net/forums/p/20223/92474.aspx

Elidas
Starting Member

33 Posts

Posted - 2008-06-23 : 08:08:58

Thanks visakh16, but the link you gave doesn't resolve the problem. I still can not execute that sentence as a maintenance plan

Elidas
Starting Member

33 Posts

Posted - 2008-06-24 : 04:05:54

anybody?

what I am asking is why the maintenance task is being executed in master database instead of my database

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 04:09:22

Did you put a checkmark for all databases you want included in the maintenance plan?

E 12°55'05.25"
N 56°04'39.16"

Elidas
Starting Member

33 Posts

Posted - 2008-06-24 : 05:13:40

quote:
Originally posted by Peso

Did you put a checkmark for all databases you want included in the maintenance plan?

how do I do that?

ppshoo
Starting Member

1 Post

Posted - 2008-07-05 : 17:11:29

Step1:
use <database name>
go
Step2:
select * from sys.database_files
go
-- The above result will give you the file_id and virtual <name> of the log not the physical name of the log file.
Step3:
DBCC SHRINKFILE (<name>, <file_id>, TRUNCATEONLY)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-05 : 23:09:53

Why shrink log file in schedule in first place?

jgregory
Starting Member

1 Post

Posted - 2009-03-06 : 10:22:47

I realize this is an old post but I believe what you're looking for, assuming that your logfile Logical name is 'NAV001_Log' and your database is named 'NAV001', is as follows:

use NAV001
GO
DBCC SHRINKFILE('NAV001_Log', 1)
BACKUP LOG NAV001 WITH TRUNCATE_ONLY
DBCC SHRINKFILE('NAV001_Log', 1)

---
The only thing missing was the single quotes around the logical name of the log file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources