| Author |
Topic |
|
dingster
Starting Member
2 Posts |
 Posted - 2004-07-14 : 10:57:31
|
| Hi,I am setting up SQL to do intraday transaction log backups every 15 min. What is the industry standard practice all you DBAs use?I want to have the process interfere the users as little as possible.Currently I set up 3 steps in the job.1. ALTER DATABASE <DBNAME> SET SINGLE USER WITH ROLLBACK IMMEDIATE2. BACKUP LOG3. SET DATABASE <DBNAME> SET MULTIUSERThe question is if I use the option "ROLLBACK IMMEDIATE" in step 1. A user using an application in the middle of a transaction would get a failure on that transaction correct? Would it be better to use the "ROLLBACK AFTER xx SECONDS"?Another thing is I encountered a problem on step three. SQL server could not set the DB back to multiuser mode because there were connected users. I am assuming it was either someone got connected between steps 2 and 3 or it was the backup job itself that held the connection? Should I insert another step between 2 and 3 to kill all users? Or is there a better and more elegant solution to this?Please post your log backup method/strategy. TIA. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-14 : 11:48:04
|
| You don't need to do that...just dump the log.....Do you have a contingency server?Brett8-) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 12:46:39
|
quote:
Another thing is I encountered a problem on step three. SQL server could not set the DB back to multiuser mode because there were connected users. I am assuming it was either someone got connected between steps 2 and 3 or it was the backup job itself that held the connection? Should I insert another step between 2 and 3 to kill all users? Or is there a better and more elegant solution to this?
I think more likely that the SINGLE USER mode at (1) failed because users were connected and you are not checking for error, and thus those users were still connected at step (3).However, as Brett says there is no need to put MSSQL into single user to do a backup, it has a very slinky way of allowing access to continue whilst backups are being made without effecting the viability of the backup.Tara's Blog has some descriptions of her backup procedures which are pretty comprehensive.http://weblogs.sqlteam.com/tarad/Kristen |
|
|
|
dingster
Starting Member
2 Posts |
Posted - 2004-07-14 : 13:06:58
|
quote:
Originally posted by Kristen
quote:
Another thing is I encountered a problem on step three. SQL server could not set the DB back to multiuser mode because there were connected users. I am assuming it was either someone got connected between steps 2 and 3 or it was the backup job itself that held the connection? Should I insert another step between 2 and 3 to kill all users? Or is there a better and more elegant solution to this?
I think more likely that the SINGLE USER mode at (1) failed because users were connected and you are not checking for error, and thus those users were still connected at step (3).However, as Brett says there is no need to put MSSQL into single user to do a backup, it has a very slinky way of allowing access to continue whilst backups are being made without effecting the viability of the backup.Tara's Blog has some descriptions of her backup procedures which are pretty comprehensive.http://weblogs.sqlteam.com/tarad/Kristen
The database was in single user mode after the job fails at step 3. You have to put the database in single user mode to be able to perform transaction log backups. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-14 : 13:29:49
|
quote:
Originally posted by dingsterYou have to put the database in single user mode to be able to perform transaction log backups.
News to me....Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 13:42:43
|
| Yup, me too. We are talking MS SQL Server I presume?Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-14 : 14:04:17
|
| if you put the database into single user mode every 15 minutes, you are certainly going to impact your users.You really only need to put the database into single user mode to perform database repairs with some DBCC commands. I've never had to use single user mode for anything else.-ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-14 : 23:30:52
|
| This is kind of kewl. You're actually kicking your users off the system every 15 minutes???? How many users do you have?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-15 : 09:11:18
|
| Hey, hadn't thought of that. Just need to make the backup take 15.1 minutes and that's all my problems solved!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-15 : 13:05:24
|
quote:
Originally posted by dingster
The database was in single user mode after the job fails at step 3. You have to put the database in single user mode to be able to perform transaction log backups.
Who told you that? It isn't true.Tara |
|
|
|
|
Questions re. Setting up intraday log backups