August 12, 2008 at 8:02 pm
We're using SQL 2005 SP2, build 3233, on Windows Server 2003 SP2.
We have a SQL job that reads sysdatabases and backs up each DB to a fileshare.
It runs at 10pm nightly on the servers in our Dev domain.
DECLARE @db varchar(100), @bkdevice varchar(200)
DECLARE DBs CURSOR FOR
select name from sysdatabases
where name not in ('tempdb') order by name
OPEN DBs
FETCH NEXT FROM DBs INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @bkdevice = '\\Dev-FILES\sqlbackups$\QA-Stuff\' + @db + '_db.bak'
BACKUP DATABASE @db TO DISK=@bkdevice WITH INIT, format
FETCH NEXT FROM DBs INTO @db
END
CLOSE DBs
DEALLOCATE DBs
Some nights it does not back up all DBs. Here's an example from one server of which DBs were backed up each evening:
8/6: ABCDB, master
8/7: ABCDB
8/8: ABCDB, master
8/9: ABCDB
8/10: ABCDB, master, model, msdb, ReportServer, ReportServerTempDB
8/11: ABCDB
There are several servers in the Dev domain, and most show this behavior, with no obvious consistency. The SQL Server logs show no errors; they just show ABCDB backed up but not others, for example.
In our production domain, no servers show this behavior.
The main difference (I think) may be with the service account that SQL runs under. It's a Windows domain account called ServiceSQL. On Dev domain servers, Dev\ServiceSQL is not a member of local Administrators on the server. On the production domain servers, Prod\ServiceSQL is a member of local Administrators. (We will be revamping our security so the service account is different on each server and is not a local Admin.)
Has anyone seen the like before? I'm speculating that it's a combination of a race condition and a security issue. The race condition may be that while the SELECT queries sysdatabases, it locks something (or something else is locking it), so that the SELECT does not return all DBs. But sometimes it does return all DBs. I notice that if it fails, it usually (but not always) fails at or before master DB.
The security issue may be that when SQLService runs the SELECT, it doesn't have enough authority to override the lock when it's not an Admin (or otherwise doesn't have enough authority to get the whole list).
As I said, there are no error messages, like "Can't back up the DB". On one server, I told the job step to output its log to a table, and it shows no errors either. No errors in the Windows Event Log. Are there traces I could turn on to dig more deeply? (Yes, the logs are backed up too, in a subsequent job step.)
Sorry if these are newbie questions or I'm using SQL Server terminology incorrectly. I was an IBM DBA, now doing MS SQL Server, and still getting up to speed on all this. Thanks in advance for any help.
August 13, 2008 at 12:35 am
Please don't cross post. Many of us read all the forums. 2005-related questions should please only be posted in the 2005 forums to avoid confusion.
No replies to this thread please. Direct replies to:
http://qa.sqlservercentral.com/Forums/Topic551546-357-1.aspx
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply