| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-05-03 : 10:45:35
|
| Fernando writes "Dear Expert..I have an important issue , i want to execute a batch file or Sql file that maintain my database only when the SQL Server Service start , the purpose is to check some data consistency after the server is starting and before the user connect to database , if u have way that help me in this senario , please send to me the solution , and i'll be very thankfull..Regards." |
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-05-03 : 12:38:07
|
| I have a similar need. I need to know if the SQL SERVER is running before my scheduled jobs run. I recently realised that the SQLSERVER AGENT seems to run the jobs when the server is stopped. Is this possible and if so how may I check for the available server using TSQL?All help appreciated. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-03 : 12:57:55
|
| You can create a job in the SQL Server Agent that starts when SQLSERVERAGENT service starts. You can find this option in the Schedule tab of the job when you click new job or edit. You'll want to select the first option in the Schedule type. Some replication jobs for instance start up automatically when the service starts.rohans, if the server is stopped, SQLSERVERAGENT service can not start ANY jobs as it would be down too since that service is dependent on MSSQLSERVER service being started.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-05-03 : 18:14:06
|
| The server is still running as I now see but I still receive errors for my backup of the database log. I think I found the problem. It is not that the database server has stopped but the backup to tape may have locked the database file that I am trying to backup a log for. Here is the new approach,How may I check if the tape backup is running or if the database isn't locked by another user or job before I execute my job?All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-03 : 18:15:50
|
| What is the command that your job is running currently? You should be backing up the database to disk first then copying the .BAK file to tape. Backing up directly to tape or over a network is not recommended.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-05-03 : 18:25:29
|
| The operators backup to tape directly as the tape drive is attached to the server. I have a scheduled backup for the log file and would like to automatically know when the operator is backing up (the time varies) and if so then don't run my log backup. Once the operator tape backup is finished I want my logs to continue running. How do I work that out?All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-03 : 18:28:14
|
| What are the operators backing up? The databases or files? Your job should not be colliding with theirs. Are you trying to access the same file? How is the tape backup scheduled? Or is it manual?Why would the database be locked? How does one lock the database in the first place?Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-05-03 : 18:36:41
|
| They are manually backing up the database not the files as far as I know. The backup runs from within EM.I am guessing it is loocked because SQLSERVER send me mail telling me that my backup for the log fails. So I am guess I cannot access the DB once the Tape gets mounted. Below is the mount line in the SQL log2004-05-03 17:02:40.46 kernel Tape 'NAME' (Family ID: 0x2e74a50c, sequence 1) mounted on tape drive '\\.\Tape0'.After this line the scheduled logs seem to fail.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-03 : 18:39:08
|
| You need to let the operators know that they should not be backing up the database directly to tape. They should be copying your .BAK and .TRN files to tape. If you really want to see if a backup is running, you'll need to check master.dbo.sysprocesses. That's going to get pretty complex for something that shouldn't be occurring in the first place.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-05-03 : 18:47:18
|
| Ok Tara, I will be trying the following:I realised that my logs run every 10 minutes starting from 07:00 (lets say) and there is a maintenance backup that runs on the hour at 17:00. I modified my logs to start 5 past the hour in the hope that it will not collied. I hope this works but I will not be able to tell until tomorrow. I will watch and see.I really don't want to change the operators' routine as that backup has to be done in case anything fails, it is a backup before a critical change is done to through an application. So I rather them doing it. But tell me this, are backups of the *.MDF and *.LDF files better than the *.BAK file. This is assuming we will re-attach the MDF and LDF files using SP_ATTACHDB. I get this feeling as I think the MDF and LDF files preserve the entire integrity, structure and security info where as the BAK files don't. Like the ownership info and who is allowed what roles and what not. Just a curious mind seeking knowledge.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-03 : 18:57:52
|
| Backups of the MDF and LDF file are useless as you rarely can restore/attach your database using them. In order for them to be of value, the service would have had to be stopped or sp_detach_db would have had to be run. This is why almost everyone uses online backup using BACKUP command to disk.Why are you running full backups every hour? That is too often IMO. Full backups should be run once a day since you've got transaction log backups running more often. If you need them more regular, then consider differential backups..BAK has everything that the MDF file has that you'll need in case of a failure. .TRN files will have the data changes since the last .BAK was taken. Things not in the .BAK or MDF file are jobs, logins, DTS packages, etc... These can be recovered by restoring the system databases.You really should take control of this system and not have the operators run backups through Enterprise Manager. Backups should be scheduled to go to disk. Then a tape process job can copy the .BAK and .TRN files to tape.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-05-04 : 08:04:01
|
| I am not running full backups every hour, sorry if I confused you. I meant that the full backup runs at 17:00 and the logs are done every 10 minutes starting at 07:00, which is now changed to 07:05.I hear your thoughts on the tape situation but I doubt they will listen to me as this has been the practice for years and unless I can seriously prove it to them , they may not listen as people don't like too many changes, especially when it is from a new DBA with no years of experience.All help appreciated. |
|
|
|
|
executing batch or Sql file on Sql server service start.