December 14, 2011 at 10:58 pm
Comments posted to this topic are about the item Concurrent Operations
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 14, 2011 at 11:17 pm
It is possible to run both these operations in SQL Server concurrently, but not on the same database.
December 15, 2011 at 12:33 am
Great question Steve, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 15, 2011 at 12:55 am
This was removed by the editor as SPAM
December 15, 2011 at 1:24 am
Hi,
This QOTD may not be as clear as it seems.
While you (as asked in the question) can certainly run 2 of these backups at the same time without getting an error, the system will block the second backup until the first one is completed.
So yes, the backup operations are queued, but there is no problem issuing two of these commands concurrently.
Best Regards,
Chris Büttner
December 15, 2011 at 1:58 am
Yes, it's possible, but the second operation will wait for the first to finish.
December 15, 2011 at 2:50 am
Even I, a confirmed pedant, understood 'run at the same time' and that it was implied on the same database. I mean you could definitely do it on separate servers, or in a parallel universe, too.
I hadn't seen the matrix of concurrency - I've learnt something new. And I'm running at 80% correct - hoorah!
December 15, 2011 at 3:00 am
good question i learned new thing in 2008r2
December 15, 2011 at 3:55 am
Nice Question about 2008R2..!
December 15, 2011 at 4:29 am
Good one Steve. Thanks.
M&M
December 15, 2011 at 6:50 am
Yay! For a change one I didn't have to think about, because I actually knew the answer.
Such a warm and fuzzy feeling... 🙂
December 15, 2011 at 7:03 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 15, 2011 at 7:23 am
Got to go with those yes or no, 50/50 questions every once in a while. Nice, thanks.
December 15, 2011 at 7:33 am
We learn something new every day... questions like these humble the most arrogant DBAs...
Having said that, I read in this discussion that the operations are indeed permitted in T-SQL because the engine will queue them so I tried the following script:
BACKUP DATABASE AdventureWorks
TO DISK = N'D:\Program Files\AdventureWorks.bak'
WITH
NOFORMAT,
COMPRESSION,
NOINIT,
NAME = N'AdventureWorks-Full Database Backup',
SKIP,
STATS = 10;
GO
BACKUP DATABASE AdventureWorks
FILE = N'AdventureWorks_Data',
FILEGROUP = N'PRIMARY'
TO DISK = N'D:\Program Files\AdventureWorks_File.bak'
GO
Not suprisingly... operations completed successfully.
In other words, knowing this is good for you so you don't expect these 2 backup operations (that take 1 and 2 hours respectively) to complete in 2 hours (if they were to run in parallel). If you are familiar with this information then you will know this script will take 3 hours (as it is serialized). Other than that, there is no risk in being a total ignorant about this one, because you will never get run-time exceptions as a result of this (as far as I can see though...)
December 15, 2011 at 7:42 am
msurasky-905715 (12/15/2011)
We learn something new every day... questions like these humble the most arrogant DBAs...Having said that, I read in this discussion that the operations are indeed permitted in T-SQL because the engine will queue them so I tried the following script:
BACKUP DATABASE AdventureWorks
TO DISK = N'D:\Program Files\AdventureWorks.bak'
WITH
NOFORMAT,
COMPRESSION,
NOINIT,
NAME = N'AdventureWorks-Full Database Backup',
SKIP,
STATS = 10;
GO
BACKUP DATABASE AdventureWorks
FILE = N'AdventureWorks_Data',
FILEGROUP = N'PRIMARY'
TO DISK = N'D:\Program Files\AdventureWorks_File.bak'
GO
Not suprisingly... operations completed successfully.
In other words, knowing this is good for you so you don't expect these 2 backup operations (that take 1 and 2 hours respectively) to complete in 2 hours (if they were to run in parallel). If you are familiar with this information then you will know this script will take 3 hours (as it is serialized). Other than that, there is no risk in being a total ignorant about this one, because you will never get run-time exceptions as a result of this (as far as I can see though...)
If you really want to test the statement made in today's question, you have to run the two backup commands from two seperate tabs in SSMS, making sure to start them in very quick succession (hit F5, switch to other tab, hit F5 again). Then, from a third tab, you can run sp_who2 to check that the two transactions are blocking each other.
Your test really doesn't say that much - statements in a single SSMS window will ALWAYS run consecutively, even if they are able to run concurrently.
PS: If your AdventureWorks backups really take 1 and 2 hours to finish, I'd suggest upgrading your hardware - they run in 3 seconds each on my laptop!
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply