So I am taking databases offline and I need to find out which ones have active connections to them. People usually do a
sp_who
However that brings a lot of info that I don’t need. I prefer to use this query so I see the DBs with number of connections!
SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
;
Brilliant query!
We had a spaghetti of database owners so I decided to get this straightened out. How to do it? Simple!
– Open New Query in SQL Server Management Studio in the database that you want to change the owner
– Run EXEC sp_changedbowner ‘domain\owner’
– Wait for Command(s) completed successfully.
As simple as that!
My last post was about taking databases offline, which if in use can be very problematic.
So before doing anything that can be interrupted if there is an open connection just run this:
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT *
FROM #sp_who2
-- Add any filtering of the results here :
WHERE DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY DBName ASC
DROP TABLE #sp_who2
Please note that sp_who2 is undocumented by Microsoft so it might change
So I saw this today in my control panel… I am over my sql quota in a small project…
Crap….
However, I found this query to find all space used in a sql database. It leverages sp_spaceused but builds a table that you can quickly review. BTW: I didn’t create it, I just borrowed it from here http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx
Big thank you to Mr Alexander, whom I’ve never met in my life but such a nice query is appreciated on a Saturday night.
SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT *
FROM #t
-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t
DROP TABLE #t
Oh, and forgot to say. I deleted logs and unnecessary stuff. Shrink db and now I am well under my quota. Hoorray! Saved some money!
