October 27, 2011 at 1:35 am
Run DBCC UPDATEUSAGE against one of the databases and then te run CHECKDB, let us know how you get on.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 27, 2011 at 2:48 am
Ran the DBCC UPDATEUSAGE but that did not make any difference.
Mmmmm... I just realized that I posted this in the SQL 2005 section, but the server on which this occurs, is SQL 2008 (10.0.2531)
I don't know if this makes a huge difference??
October 27, 2011 at 3:36 am
How is the job being executed? Is it a SQL Maint plan, or T-SQL statements?
Do you get the same result when you run the command manually?
Could you execute sp_who2? Do you see any transaction with the Command like 'DBBC'?
One more bit of information gathering. The error message states that the transaction can not obtain a lock on the DBs. As you said MSDB is failing, could you run the below code and post the output?
SELECT * FROM master.sys.dm_tran_locks WHERE resource_database_id = DB_ID('msdb')
I'm not so experienced with investigating locks so if someone else has got a better way to check, then it might be a good place to investigate.
October 27, 2011 at 4:12 am
Jobs are executed with TSQL.
The (Exclusive Lock) is not the real problem. DBCC wants a lock on the database because it can't make the snapshot. So, the real question is: why can't it make the snapshot.
On the non system databases the CHECKDB should work if I disconnect all sessions, but with the msdb, that is not a (good) option.
October 27, 2011 at 4:38 am
spidey73 (10/27/2011)
That's not what I asked about. Does the SQL Server service account have full control (in Windows) to the files and folders involved? (all of them)
The Service account has got full access to the share in which all the SQL files are located.
Share? A network share?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2011 at 5:27 am
Sorry, I wasn't clear about that.
The database files are on the SQL server itself.
The SQL service account is a Active Directory account that got access to the database file location through a share.
example, the file location on the server is:
D:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA
A share is made on D:\MSSQL (and its subfolders), so the service account has got all the permissions needed. (full access)
October 27, 2011 at 6:06 am
Share permissions are irrelevant, they're only used by something accessing the server from the network.
Please browse to the folder and check the NTFS permissions on the folder and files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2011 at 1:31 am
The NTFS permissions are also correct. Full control for the service account.
This does not surprise me, because other databases, which are in the same folder and got the same security settings, do not have a problem with the checkdb.
mmmm, I'm going to try and schedule a reboot. If that should solve it, it will not be satisfactory, because then I don't know the cause of the problem.
October 28, 2011 at 5:39 am
Unless Gails recommends a reboot don't do it. The situation might just get worse.
For NTFS you might also check each file separately (in case the permissions are not inherited). Especially compare 1 that works vs 1 that fails. I've had a situation where I had moved the file to a usb drive and I had to retake ownership everytime I changed computer. Maybe you have something similar to that.
October 28, 2011 at 5:46 am
Ninja's_RGR'us (10/28/2011)
Unless Gails recommends a reboot don't do it. The situation might just get worse.
Reboot - not necessary. Restart of SQL may help, or may do nothing
For NTFS you might also check each file separately (in case the permissions are not inherited).
That's what I've been asking about for several posts...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2011 at 5:55 am
GilaMonster (10/28/2011)
Ninja's_RGR'us (10/28/2011)
For NTFS you might also check each file separately (in case the permissions are not inherited).That's what I've been asking about for several posts...
I know, but from the last answer he posted I'm not sure he checked each files instead of the folder...
October 28, 2011 at 6:33 am
spidey73 (10/27/2011)
Sorry, I wasn't clear about that.The database files are on the SQL server itself.
The SQL service account is a Active Directory account that got access to the database file location through a share.
example, the file location on the server is:
D:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA
A share is made on D:\MSSQL (and its subfolders), so the service account has got all the permissions needed. (full access)
go to a command prompt in the location above and run
dir /a:hr
What results do you see?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 2, 2011 at 6:32 am
dir /a:hr returns no files.
So no hidden and read only files.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply