SQL Server Blog

New, faster disks arrived? Maybe you''ve done a reshuffle and ended up with a clean, unused physical drive? Either way your tempDB needs to be relocated. Doing so is actually incredibly simple and requires just a few short steps.



Let’s presume for a moment that my tempDB is in completely the wrong location and I need it moved…

This is just a little tip in case you ever have to make a lot of updates or inserts using a while loop and don’t mind a short amount of blocking while you do it.



Basically within SQL Server the default is for the database engine to use AUTOCOMMIT. So, in the following example, there are 3 inserts into the temp table @temp… after each insert SQL Server will autocommit the transaction. Therefore, due to autocommit, this yields 3 commits…

One of the most crucial and most complex stages in selecting the Article Properties of a table with an Identity column is how to utilise replication's Identity Range Management.

This came to light when I was working with a company who were struggling with their MSDB database. They had a requirement to maintain huge volumes of historical job data and backup information and didn't want to archive this to another database. Therefore this meant a large MSDB which had filled their drive and they needed to relocate it.

This is a crucial addition to SQL Server which is invaluable at times. It basically takes a snapshot of the database rather than an actual backup. Therefore it can be used to restore a database and to provide a copy, but crucially it does not break the backup chain.

This is actually the cleanest and quickest way to produce a backup as it is a one line command in the form of:

Making backups using the GUI is a little longer than when using T-SQL (here), but they do provide a much easier way to see and customise your backup options.

It is possible and perfectly acceptable to put T-SQL commands into a scheduled task in order to create a full backup plan, but a Maintenance Plan is Microsoft’s way of allowing you the power of the GUI backup with the convenience of a scheduled task so that you don’t have to code your own.

These are incredibly useful when writing complex T-SQL or stored procedures etc. as they are very versatile and simple to use. They are also only active and present during the execution of your code. They are automatically dropped and tidied up once execution finishes.



Effectively they are storage in which you can place a value to be used and manipulated throughout your code and refer to it by the variable name.