Home » SQL Server

Could not locate file 'xxx_Log' for database 'master' in sys.database_files

Hi All,

I am  truncating Transactionlog…   following the steps mentioned in MSKB.. what is the problem?

Use master

Backup log xxx with truncate_only
Dbcc shrinkfile(xxx_Log)

Msg 8985, Level 16, State 1, Line 1
Could not locate file 'xxx_Log' for database 'master' in sys.database_files.
The file either does not exist, or was dropped.

Please  help me.

Thanks in advance


14 Answers Found


Answer 1

SHrinkfile has to be run from the particular database  to be shrinked

 Follow these steps

Use master

Backup log  xxx with truncate_only

Use YourDatbaaseName

Dbcc shrinkfile(xxx_Log)



Answer 2

I am executing exactly as you specified (changing database  context with USE) in a "Maintenance Plan" using an "Execute TSQL Statement Task". This plan is then scheduled to run.
It fails with "...Could not locate  file 'XXX_Log' for database 'master' in sys.database_files. The file  either does not exist, or was dropped."
It seems that it is unable to execute the "USE myDatabase" command correctly.

Here is the TSQL in full ;

USE [myDatabase];
DBCC ShrinkFile('XXX_Log', 3000)

This Maintenance Plan still fails even if I execute it manually (i.e. not in a job).
When I remove the ShrinkFile statement it succeeds i.e. the USE statement seems to be executing.

Any ideas ?


Answer 3

Hi Preet Smile
             Could you pls check your logical file  name of your log  file once again. Execute sp_helpdb 'your db name' from that you can get the logical file name something like 'xxx_log'. May be that might be one of the reason....
- Deepak

Answer 4

Hi Deepak

Logical name is definitely correct (and as returned by sp_helpdb) and the command works in a normal query window.

Can you get it to work in a Maintenance Plan, Execute T-SQL Statement Task ?



Answer 5

What is the Service pack on this box?



Answer 7

To troubleshoot , can you open this maintenance plan in BIDS and see the connection.

(a) Check which database  is it connecting, if its not your database change the connection to your db and see

(b) Check which user is it using and what is the default database of the user. Change the default database to your db and check.



Answer 8

Opened in BIDS. The connection does not specify a database. When I specify the database  it still fails. No specfic error the Progress tab just indicates that the TSQL failed.
I could try changing the default database as you suggest in b) but this will not help if I want to shrink several databases/files.

I decided to put the TSQL commands in a SQL Job. This works fine.

I would be interested to know if anybody has got a similar "Execute TSQL Statement" in a Maintenance plan to work.

Thanks for your help.


Answer 9


I tried to use a similar command to shrink the log  files in a maintenance plan and got the same error after the scheduled execution. It seems like before the DBCC shrinkfile command it switches back to use the master  database. After adding another "USE mydb" just before the dbcc shrinkfile command (without GO between the lines) the plan ran successfully. Try and see if it works for you (if you still need it...).


Answer 10

I dont need it as I have put it in a SQL Job Step but thanks for the tip.

If I use it and I remember, I'll let you know if it worked,


Answer 11

Thanks! This tip works well for me.


Answer 12


 I am having the same problem  and unable to get it to work. I am not setting up a maintenance plane but running as a query for now. i have verified that the logical name is correct. Please hlp










DBCC ShrinkFile (dbESR3_Log, 500)



Msg 8985, Level 16, State 1, Line 2

Could not locate  file 'dbESR3_Log' for database  'dbESR3' in sys.database_files. The file  either does not exist, or was dropped.


Answer 13

Please run sp_helpfile in that database  and verify that you are using the correct file  name while shrinking

Answer 14


Anyone can reproduce it .







log adventureworks withtruncate_only







Reason : you are doing USE master  and doing DBCC shrinkfile for some other database  in master .So none of the logical files assocoated with master will have the AdventureWorks_Log as its logical file  name .So we are getting this error .

So as per BOL :DBCC SHRINKFILE applies to the files in the current database. Switch context to the database to issue a DBCC SHRINKFILE statement referencing a file in that particular database.

therefore you need to use "USE DB" before running it for xxx_Log .Backup log  with truncate will work fine without this need.




I am working in Visual Studio 2010 in a database project.

I imported all the objects from a database and created eleven different References files (.dbschema) because views and stored procedures refer to objects in other databases. Master.dbschema is one of the references in this project.

I've eliminated all warnings and errors except for two cases. I will spare you the first case; I think I have a workaround.

The second case involves references to sys.objects in an external database. The intention in the code is to check whether a certain stored procedure exists, as there is logic to execute either a custom procedure or the standard one.
The check for that procedure looks in sys.objects in the external database, one for which I have a References (.dbschema) file.
Here's the line
     if exists (select * from [EntIntel].[sys].[objects] so where [so].[type] = 'P' and [so].[name] = @spName)
In this case I'm just checking for the existance of a procedure (@SPName) . However, it feels like VS can't find that SP in my EntIntel.dbschema file.
Is this true?
What is the workaround? Do I recode all the SPs that have this statement in it? If so, how? How else do I test for the existence of an SP?

The project builds with this error; I haven't tried deploying this solution yet.
Details - Visual Studio 2010, database project, SQL Server 2005.

Warning    24    SQL04151: Procedure: [dbo].[spChartReservationSourceByDate] has an unresolved reference to object [EntIntel].[sys].[objects].[name].

Thanks for all your help - this forum has been invaluable to helping me work through migration to VS 2010 database projects.




 I am using SQL express and Visual web developer on windows Vista.

When I try to create a new database the following message appears.


CREATE DATABASE permission denied in database master (error 262)

I log on to my computer as an administrator.

Help appreciated






thanks Giovanni sir .......... solved dat one but got one more error i.e.


1>------ Build started: Project: histimg, Configuration: Debug Win32 ------
1>c:\documents and settings\kesar\my documents\visual studio 2008\projects\histimg\histimg\histimg.cpp(8) : fatal error C1083: Cannot open include file: 'sys/cdefs.h': No such file or directory
1>Build log was saved at "file://c:\Documents and Settings\kesar\My Documents\Visual Studio 2008\Projects\histimg\histimg\Debug\BuildLog.htm"
1>histimg - 1 error(s),

 For some reasons I have always used the Windows Azure Storage and now I'd like to test the development storage.

But I get this error at launch time:
Added reservation for '' for user account 'XXX-AD\mike'
Added reservation for '' for user account 'XXX-AD\mike'
Added reservation for '' for user account 'XXX-AD\mike'

Creating database DevelopmentStorageDb20090919
Failed to create database 'DevelopmentStorageDb20090919' : CREATE DATABASE permission denied in database 'master'.

One or more initialization actions failed. The development storage cannot be used without resolving these errors.

Is there anything I should have installed in addition to the usual Azure installers?
I have SQL Server 2008 installed.

Can't give any user special permissions since there is no DB installed. Can I?



Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,

I am running windows Vista Business, SQL SERVER 2005

so what going on here



i am trying to create new web appln in SPS 2010 USING Claims  based authentication. i am belonging to Administartors group[builtin\admin].

But when i click OK it throws me error  "CREATE DATABASE permission denied in database 'master'".

I AM USING NETWORK SERVICE. am beloning to farm administartors group.  in my sql server2008 r2 - mgmtn studio i went to security -logins --.>login properties-->> added myself given the "serverroles" dbcreator,  securityadmin --> checked.  In the usermapping--> master db-->db_owner, public .

even after doing all these settings am getting this error.can any one points me to the rigt direction? where am missing.


I am taking an ASP.Net class and our network configuration is a thin-client architecture (thin client systems connected to a massive server for storage and virtual profiles).  When I try to create a database in VS 2010 for a web site, I get the message...

CREATE DATABASE permission denied in database 'master'.

The server I am trying to log in to, using Windows authentication, is the (server name)\SQLEXPRESS.  Any ideas on how we can work around this for the students in the class (I'm the professor's TA :) ) to be allowed to create databases in VS?


I need to programatically locate the  reporting database -- since the databases can now be spread across more than one server, the problem becomes a bit more difficult.  The server and database name appear in the project server webapp configuration page. I scanned the properties of the webapp and the sitecollection to no avail.. There's nothing in the registry either...  Hints?





I am working on windows device drivers for USB. While looking windows documentation. I came across the loading order of various driver for USB,

Loaidng order is

Miniport Driver( usbuhci.sys, usbohci.sys or usbehci.sys) -> usbport.sys -> usbhub.sys -> client driver

Can anybody explain the Roles and Responsibilty of each driver? Or Can tell some link where I can get this information?

Thanks in advance!!

Note:: Client Driver Roles I know!!  



After running DBCC CHECKDB ('MyDB') get the following error:
Msg 211, Level 23, State 230, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Then After running DBCC CHECKCATALOG ('MyDB') get the following error (it seems that somehow some primary keys and columns were not correctly updated):

Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1726629194) of row (object_id=1726629194,column_id=1) in sys.columns does not have a matching row (object_id=1726629194) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1726629194) of row (object_id=1726629194,column_id=2) in sys.columns does not have a matching row object_id=1726629194) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1726629194) of row (object_id=1726629194,column_id=3) in sys.columns does not have a matching row (object_id=1726629194) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1726629194) of row (object_id=1726629194,column_id=4) in sys.columns does not have a matching row (object_id=1726629194) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1758629308) of row (object_id=1758629308,column_id=1) in sys.columns does not have a matching row (object_id=1758629308) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1758629308) of row (object_id=1758629308,column_id=2) in sys.columns does not have a matching row (object_id=1758629308) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=1726629194) of row (object_id=1742629251) in sys.objects does not have a matching row (object_id=1726629194) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=1758629308) of row (object_id=1774629365) in sys.objects does not have a matching row (object_id=1758629308) in sys.objects.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I am trying to delete those registries in sys.columns and in sys.objects but I can´t even after updating system catalogs (sp_configure'allow updates', 1). I have also tried what it says at http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx
but it doesn´t work either.

Any other ideas on how to delete in those system tables?

Hi All,

While doing some perormance tunning with the table i came across with a strange disparity in the information saved in sys.index and sys.dm (indexes).

To regenrate the problem i am pasting the code and steps

Create a table using the below script





CREATE TABLE [dbo].[UserAccMaster](
	[Cust_AccountNo] [int] IDENTITY(1,1) NOT NULL,
	[CustAccName] [varchar](max) NOT NULL,
	[CustAdd] [nvarchar](max) NOT NULL,
	[CustAdd_Temp] [nvarchar](max) NOT NULL,
	[CustMobile] [bigint] NOT NULL,
	[Cust_AccountNo] ASC
) ON [UserFG]







Now execute the below queries , Q1 and Q2 give the same output for clustered index.

where in executing Q3 and Q4 give output as clustered and nonclusterd index.




*FROMsys.sysindexeswhere id=OBJECT_ID('UserAccMaster')






*FROMsys.dm_db_index_usage_statswhere database_id=DB_ID('CRMDBArch')--and index_id=1






The thing i dont understand is from where this non clustered information is coming in from Q3 & Q4.




Hi Gurus,

I am facing below problem while configuring mirroring between 2 machines in same domain.

I am doing below steps.

1)       Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2)       MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3)       MRRUSR is the MSSQLSERVER service account in both machines

4)       Machine A is gonna be the PRINCIPAL, B the MIRROR.

5)       Log on in my machine as MRRUSR

6)       Open Sql server Studio. Connect to both machines using windows authenticacion.

7)       Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8)       Backup the new DB (Full backup) with .bak extension

9)       Backup the new DB (Transaction log) with .trn extension

10)   Copy the both files to a location in B machine.

11)   Restore .bak into a new DB using NORECOVERY option

12)   Restore .trn into the previous DB using NORECOVERY option

13)   Go to Machine A, open a new query

14)   Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;


15)   Delete existing ones (DROP ENDPOINT [NAME])

16)   Create a new endopoint



17)   Start it

FOR database_mirroring (ROLE = PARTNER);

At these stage i am getting following error.

"Msg 911, Level 16, State 1, Line 1

Could not locate entry in sysdatabases for database 'NAME'. No entry found with that name. Make sure that the name is entered correctly."

any help appreciated.











I put a SliderShowExtender and i created a web service with a function that return AjaxControlToolkit.Slide(), but when a run the project give me the error:

Microsoft JScript runtime error: Sys.Net.WebServiceFailedException: The server method 'returnLogo' failed with the following error: <html>
        <title>Unknown web method 'returnLogo'.<br>Parameter name: methodName</title>



Anybody know why?




I have a main page with several menu choices, one of which launches an ajax enabled window.  The following pattern occurs:

If I:

1) Launch site

2) Select menu choice listed on master page that is not ajax enabled

3) Select menu choice that launches ajax enabled popup.

4) close pages

All is OK.

If I repeat the steps, but in step 2 I select an ajax enabled page and them launch popup, I get the following error:

Message: Sys.ArgumentTypeException: Object of type 'Sys.UI._Timer' cannot be converted to type 'Sys.UI._Timer'.

I have spent days on this and have been unable to solve it.  If I remove the ajax ToolkitScriptManager and update panels from any of the pages and launch the popup, it works. Can anyone offer some guidance?

that are not ajax enabled and then selethat includes an ajax enabled popup window.  T 


I had to uninstall SQL Server 08 when I left my last project, I have reinstalled the management studio but it no longer recognizes the databases that are on my local machine.  How can I reconcile the connection to the preexisting databases? 

When I choose connect, nothing comes up in the options and when i type in the database name it errors out.  I thought it was just that the old files were corrupt but I installed a brand new database and while i can verify that it exists on my machine, I cannot connect through Management Studio.


I have just upgraded our web applications to RC1 (please don't change the namespaces again microsoft!), and are getting the following error on one of our pages:

A Runtime Error has occurred.
Do you wish to Debug?

Line: 3088
Error: Sys.InvalidOperationException: Handler was not added through the Sys.UI.DomEvent.addHandler method.

Has anyone elst seen this error?



I've been a user on SQL Server 2005 for several years now (no experience on prior versions), and just starting to explore some of the under the hood stuff. I recently started looking at the various system views and I have some questions... so much to learn.

For example, I got curious about how sys.database gets its information. So I did sp_helptext 'sys.databases' and it showed me the view definition. Looking at it, I see that the 'name' column of the view comes from master.sys.sysdbreg. But I can't seem to find this object anywhere in master. In fact, if I try to run the select statement that defines the view, I get an error that says,

'sysconv' is not a recognized built-in function name.

And if I resolve that error, I seem to get more errors? Am I trying to dig too deep?



I have a mirror on a DB. I sat the Encryption true on the primary DB, I made sure the certificates exists in both servers/DBs.

I run a failover, now when looking in the Sys.databases the is_encrypted is 0 and when trying to run the following "alter database abc set encryption on" I get error 33107 that the DB is marked for encryption already.

What should I do to make sure the mirrored DB is encrypted, and How will I force the sys.database view to reflect it



I'm currently encountering a problem.

At the moment I have a supervisor role and I wish to extend the role's permissions to allow users who are members of this role to see all entries contained in the system table sys.sql_logins. Is it possible to assign the permission at the databse role, or can it only be done by assigning the permission to each login separately.

The system itself requires users to be created and added to the supervisor role on the fly, so ideally I'd like to have the permission assigned at the point whether the user is added to the supervisor group.

Any help would be fantastic





I am making a database app for my home (maybe public release once I get everything tweaked and it takes off).....

Anyways, my problem is...

I have VB 2010 express on two of my 5 PC on my LAN.

My app works on THOSE two machines with out glitches...

When I try to run it on two of other three, it crashes and the error report from windows says "System.IO.FileNotFoundException"

When I run it on the third, it runs but the data is not displaying from the database, which leads me to belive that THAT is the reason it is crashing on the other two...

How do I set it so the program looks in the "local" directory for the database?



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure