SQL Server Help And Tips
I just wanted to share the easiest way to get the modified date from a stored procedure in SQL Server. You do it just by accessing the Procedures table.
|
1 2 3 4 5 |
SELECT Name, Create_Date, Modify_Date FROM sys.Procedures WHERE Name = 'Animal_Update' |
Killing all connections in SQL Server is something that you may need to do from time to time. The two times that I’ve needed to do this most is when I’m restoring a database in my test environment or when I need to rename a database. Both of these operations require that there are not any open connections to the SQL Server database. There are a couple good scripts that I will show here. My favored method is to set it in single user mode.
Kill All Connections Using Single User Mode
This is the easiest way kill all the connections to the database. It basically sets the database to only allow 1 user (you) and it will kill all the other connections.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Kill all connections and set to single user mode USE MASTER GO ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO -- Do your operations -- Set the database back in to multiple user mode USE MASTER GO ALTER DATABASE [DatabaseName] SET MULTI_USER GO |
Kill All Connections Using The Kill Command
The following query will loop through all the open processes on the database and kill each one. Note that this may take a little bit of time to execute if there are long running transactions, it may take some time to roll them back.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
USE MASTER GO DECLARE @Spid INT DECLARE @ExecSQL VARCHAR(255) DECLARE KillCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT SPID FROM MASTER..SysProcesses WHERE DBID = DB_ID('DatabaseName') OPEN KillCursor -- Grab the first SPID FETCH NEXT FROM KillCursor INTO @Spid WHILE @@FETCH_STATUS = 0 BEGIN SET @ExecSQL = 'KILL ' + CAST(@Spid AS VARCHAR(50)) EXEC (@ExecSQL) -- Pull the next SPID FETCH NEXT FROM KillCursor INTO @Spid END CLOSE KillCursor DEALLOCATE KillCursor |
Reference: https://msdn.microsoft.com/en-us/library/bb522682(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms173730.aspx
Starting with SQL Server 2014, they added a feature called delayed transaction durability. This is a very interesting concept that could give you some speed improvements in your database. In this post, I will try to explain what this feature is and what some advantages/drawbacks are.
Transaction Log Durability
In 2014, SQL Server gives us the ability to control how the transaction log files are written to the disk. It gives us 2 options. Full transaction durability and Delayed transaction durability.
Advantages
The delayed transaction durability has a few distinct advantage over full transaction durability.
The first advantage is that it doesn’t need to write to disk before giving control back to the client. In many databases, writing to the disk provides a lot of contention. Log file writes to the disk can sometimes slow queries down quite a bit. Writing to memory is much faster and should speed up your queries in general if your IO to the disk is slow.
Another advantage is that the delayed transaction durability buffers the writes to disk. SQL Server can get optimizations by buffering the write operations. Instead of writing each transaction to the disk, it will fill a buffer and then flush that buffer all at once.
Disadvantages
Everything up to this point sounds great, right? Delayed transaction durability comes with one major pitfall. Delayed transaction durability could cause data loss if SQL Server crashes. The data that isn’t written to disk can be lost if SQL crashes.
When Should I Use Delayed Transaction Durability?
Only use delayed transaction durability if you can afford some data loss if SQL Server crashes. If you cannot afford any data loss, do not use delayed transaction durability.
How To Turn On/Off Delayed Transaction Durability
Okay, now that you’re an expert on delayed transaction durability, let’s quickly cover the 3 levels of transaction durability and how to activate them.
There are 3 kinds of transaction durability. Disabled, Allowed, and Forced.
DISABLED – This means delayed transaction durability is turned off. All transaction writes will go to disk and there will be no potential for loss.
FORCED – This means that delayed transaction durability is turned on for every query. All transaction writes will go to memory and there is a potential for data loss.
ALLOWED – This is kinda in the middle. It basically means that you can turn on the delayed transaction durability in the query or stored procedure.
You will configure the durability with one of these statements:
|
1 2 3 4 5 6 7 8 9 |
ALTER DATABASE zoobase SET DELAYED_DURABILITY = DISABLED GO ALTER DATABASE zoobase SET DELAYED_DURABILITY = FORCED GO ALTER DATABASE zoobase SET DELAYED_DURABILITY = ALLOWED |
If you use ALLOWED, you will need to add this hint to your commit transaction in in your sql query or stored procedure to activate the delayed transaction durability for it:
|
1 2 3 4 5 6 7 |
BEGIN TRAN UPDATE Animal SET AnimalName = 'Cow' WHERE AnimalName = 'Cattle' COMMIT TRAN WITH (DELAYED_DURABILITY = ON) |
Reference: https://msdn.microsoft.com/en-us/library/dn449490.aspx
NOTE: SQL Server shutdown/reboots are handled the same way as a crash. Make sure you flush your transaction buffer before rebooting!
Filestreams are a great way to store files in SQL Server. SQL Server makes this super easy by managing the storage of the physical files on the file system. But what if you want to get access to these files or if you want to know where they are stored. SQL Server wants you to access these files through a network share managed by SQL Server. They provide a function called PathName that you can call to get this path. You will call this function like you would call an extension method in .Net. In the example below, we will assume that the column InvoiceFile is a Filestream datatype. We call the PathName() function to get the share name to where the files are stored.
|
1 2 3 |
SELECT TOP 1 InvoiceFile.PathName() AS DirectoryOfFile FROM Invoice |
When you’re creating your database backup maintenance plans, you need to choose a drive that has enough space for your backup. So how much space will you actually need to backup your database? Figuring this out is actually a lot easier than you would expect.
Although nothing will be 100% accurate, SQL Server has a built in stored procedure for calculating the used space in a database. A full database backup only stores the actual data/objects in the database. The unused space is not stored.
|
1 |
EXEC sp_spaceused @updateusage = N'TRUE' |
In the example above we call the sp_spaceused stored procedure from the database that we would like the size from. This is important… you need to execute this from the database that you want to get the size for. This will return a dataset with multiple columns. The column that you will look at is the reserved column. This will show the amount of space without the unused space.
Note
The @updateusage parameter tells the stored procedure to update the space usage info before returning the used size. Passing TRUE in to this parameter will give us the most accurate size estimate.