backup | SQLServer.info

SQL Server Kill All Connections

November 24, 2017 by robert

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.

-- Kill all connections and set to single user mode

USE MASTER

ALTER DATABASE [DatabaseName]

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

-- Do your operations

-- Set the database back in to multiple user mode

USE MASTER

ALTER DATABASE [DatabaseName]

SET MULTI_USER

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.

USE MASTER

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

Estimate Backup Size In SQL Server

September 1, 2017 by robert

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.

Estimate Backup Size In Sql Server

February 20, 2015 by robert

Although it won’t be 100% accurate, SQL Server has a built in stored procedure that will get you pretty close. You can use the sp_spaceused stored procedure to show you the amount of space used in your database. Now keep in mind that a full database backup only stores the actual data/objects in the database. The unused space is not stored in the backup.

USE zoobase

EXEC sp_spaceused @updateusage = N'TRUE'

Estimate Backup Size In SQL Server

In the example above we call the sp_spaceused stored procedure from the database that we want to get the backup size for. This stored procedure will return a couple datasets with multiple columns. The column that you will look at is named reserved. This will show you a good estimate of how large your full backup file will be.

More Info
The @updateusage parameter, in the example above, tells the stored procedure to update the space usage statistics before returning the database size information. Passing TRUE in to this parameter will give us the most accurate size estimate.