Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"?

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 5);
GO
DBCC SHRINKFILE (DataFile2, 5);
GO

Will it start shrinking DataFile2 only after DataFile1 has finished, or simultaneously?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

so far i am not aware of any SQL command that can initiate two query/command execution at the same time or in parallel. regard less of the GO stmt. if you are running set of commands using t-sql in SSMS they are executed in order one after another. you can create 2 SQL JOBS and invoke each using individual t-sql command or schedual them a the same time. in case of t-sql invoking, immediately after invoking first job DB engine will go for second invoke it will not wait for that JOB to complete. but still operation of invoking each job will get executed in order.

share|improve this answer
    
Thanks! Would it be possible to force the engine to wait for the first command to complete? –  Symon Loska Nov 2 '13 at 22:33
    
if you are running set of commands using t-sql in SSMS they are executed in order one after another. that means second starts after first completes. now meaning of completes is different for different commands. if the command is DDL/DML operation and/or dynamic sql, execution another system/user procedures the second command will wait until the first command actually completes execution. you do not have o do any thing for that. but if the first command is invoking SQL job then as soon invoking of job is done db engine ill go to next command. –  Anup Shah Nov 3 '13 at 0:03
    
so if you are asking about forcing DB engine to wait until the SQL job execution (invoked by first command) then you have to write something to check the status of that job in while loop or something as a second command. based on that status you can Return/Continue to next command. –  Anup Shah Nov 3 '13 at 0:05
GO

is a batch terminator in SSMS. Every time you have a GO (or a ;) you have told SQL Server that the batch just ended. So you are sending your commands in batches, and the batches are executed after another - not at the same time.

SO in this case they are all executed serially. Really anytime you run commands they are executed serially in one connection.

The bigger question in my mind is - Why are you doing Shrinkfile? I hope this isn't a production database? This is not a best practice operation to regularly be running at all, as this link describes.

Preview: Shrinking your data files shouldn't be needed - unless you just cleared a ton of space you'll never grow back into, are having serious disk capacity issues or are doing something in dev - and even in these situations, there may be alternatives that are better for you.

Shrinking means introducing index fragmentation. It means some expensive IO operations and there is a good chance you'll be growing back into the space eventually anyway if your database is still live and in use with inserts and updates.

share|improve this answer

Will it start shrinking DataFile2 only after DataFile1 has finished, or simultaneously?

If executing these statements out-of-order is in question, it would also be prudent to ask when the statement USE UserDB will be executed...

In any event,

  • Statements within a batch are executed serially (procedural logic), top-to-bottom
  • Batches within a SSMS window/selection are executed serially (GO is the default batch separator), top-to-bottom

The only way to get multiple logical operations running in parallel is to somehow separate them onto different server connections. There are several ways to do this; off the top of my head, here are a few:

  • Multiple SSMS windows (or a single window connected to multiple servers using Registered Servers or Central Management Servers)
  • Multiple instances of sqlcmd
  • SQL Agent job(s) (I don't say multiple for the case where you want to do the same thing multiple times in parallel)
  • Service Broker with MAX_QUEUE_READERS set to a value greater than 1 (I believe this doesn't guarantee parallelism, but I'm mentioning it just because I thought of it)

Whether or not what you're attempting to do with this information is a good idea is a separate discussion which has been covered many times, so I'm not going to cover it here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.