Skip to content

Commit 317c3e2

Browse files
author
Rolf Tesmer (Mr. Fox SQL)
authored
Add files via upload
1 parent ef8c93b commit 317c3e2

46 files changed

Lines changed: 3401 additions & 0 deletions

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎KillDBConnections.sql‎

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,83 @@
1+
/************************************************************************************************
2+
================================================================================
3+
DESCRIPTION:
4+
--------------------------------------------------------------------------------
5+
Kill datbaase connections for a specific or all databases, and then change the setting
6+
of the database to OFFLINE, SINGLE USER, etc
7+
8+
HISTORY:
9+
--------------------------------------------------------------------------------
10+
Date: Developer: Description:
11+
--------------------------------------------------------------------------------
12+
* * Created
13+
--------------------------------------------------------------------------------
14+
15+
NOTES:
16+
--------------------------------------------------------------------------------
17+
THIS SCRIPT/CODE ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED.
18+
LIMITED TESTING HAS BEEN PERFORMED ON THIS SCRIPT/CODE AND THEREFORE THE AUTHOR DOES NOT WARRANT
19+
THAT ANY SCRIPT/CODE IS BUG OR ERROR-FREE. IT IS EXPECTED THAT ANY SCRIPT/CODE UNDERGO YOUR OWN
20+
TESTING AND/OR VALIDATION BEFORE USAGE ON ANY CRITICAL SQL SERVER PLATFORM.
21+
THIS SCRIPT MAY BE A COLLECTION OF MY OWN CODE COLLATED OVER MANY YEARS, OR OTHER CODE I HAVE
22+
LOCATED ON THE WEB WITH AN UNKNOWN ORIGIN. WHERE CODE HAS BEEN IDENTIFIED IT WILL BE CITED.
23+
================================================================================
24+
************************************************************************************************/
25+
26+
USE master;
27+
GO
28+
BEGIN
29+
SET NOCOUNT ON;
30+
DECLARE
31+
@SQLString NVARCHAR(4000),
32+
@DBName SYSNAME,
33+
@Option TINYINT;
34+
SELECT @DBName = 'MyDatabaseNameHere', -- PUT DB NAME HERE, <NULL> MEANS DO ALL DATABASES
35+
@Option = 0; -- 0 = KILL / 1 = KILL & OFFLINE / 2 = KILL & SINGLE / 3 = KILL & DETACH
36+
37+
DECLARE dbcurs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
38+
FOR SELECT [name]
39+
FROM [master].[dbo].[sysdatabases](nolock)
40+
WHERE [name] NOT IN('tempdb', 'master', 'model', 'msdb')
41+
AND [name] = ISNULL(@DBName, [name]);
42+
OPEN dbcurs;
43+
FETCH FROM dbcurs INTO
44+
@DBName;
45+
WHILE(@@fetch_status = 0)
46+
BEGIN
47+
PRINT '';
48+
PRINT @DBName;
49+
-- KILL CONNECTIONS
50+
SELECT @SQLString = '';
51+
SELECT @SQLString = @SQLString+'kill '+CONVERT( VARCHAR(10), [spid])+' '
52+
FROM [master].[sys].[sysprocesses]
53+
WHERE [dbid] = DB_ID(@DBName);
54+
PRINT @SQLString;
55+
EXECUTE [dbo].[sp_executesql]
56+
@SQLString;
57+
58+
-- ALTER DATABASES
59+
SELECT @SQLString = '';
60+
IF(@Option = 1)
61+
SELECT @SQLString = 'alter database ['+@DBName+'] set OFFLINE';
62+
IF(@Option = 2)
63+
SELECT @SQLString = 'alter database ['+@DBName+'] set SINGLE_USER';
64+
IF(@Option = 3)
65+
SELECT @SQLString = 'execute dbo.sp_detach_db '''+@DBName+''', ''TRUE'', ''TRUE''';
66+
PRINT @SQLString;
67+
EXECUTE [dbo].[sp_executesql]
68+
@SQLString;
69+
70+
-- USE DATABASE
71+
SELECT @SQLString = '';
72+
IF(@Option = 2)
73+
SELECT @SQLString = 'use ['+@DBName+']';
74+
PRINT @SQLString;
75+
EXECUTE [dbo].[sp_executesql]
76+
@SQLString;
77+
FETCH NEXT FROM dbcurs INTO
78+
@DBName;
79+
END; -- while
80+
CLOSE dbcurs;
81+
DEALLOCATE dbcurs;
82+
END;
83+
GO

‎ListDBFileGrowthHistoryFromDefaultTrace.sql‎

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
/****************************************************************************************************************************************************************
2+
================================================================================
3+
DESCRIPTION:
4+
--------------------------------------------------------------------------------
5+
List the last time a log file was expanded for a databaswe
6+
this will will identify the times when log growth occurs to pinpoint when IO load occurs
7+
these are actual log file expansions on the disk (ie gorws from 10GB to 11GB)
8+
if the file expansion happes in file these are not detected (ie log is 78GB but 5GB used. if it expands to 6GB used but still a 78GB file these are not detected)
9+
10+
HISTORY:
11+
--------------------------------------------------------------------------------
12+
Date: Developer: Description:
13+
--------------------------------------------------------------------------------
14+
* * Created
15+
--------------------------------------------------------------------------------
16+
17+
NOTES:
18+
--------------------------------------------------------------------------------
19+
THIS SCRIPT/CODE ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED.
20+
LIMITED TESTING HAS BEEN PERFORMED ON THIS SCRIPT/CODE AND THEREFORE THE AUTHOR DOES NOT WARRANT
21+
THAT ANY SCRIPT/CODE IS BUG OR ERROR-FREE. IT IS EXPECTED THAT ANY SCRIPT/CODE UNDERGO YOUR OWN
22+
TESTING AND/OR VALIDATION BEFORE USAGE ON ANY CRITICAL SQL SERVER PLATFORM.
23+
THIS SCRIPT MAY BE A COLLECTION OF MY OWN CODE COLLATED OVER MANY YEARS, OR OTHER CODE I HAVE
24+
LOCATED ON THE WEB WITH AN UNKNOWN ORIGIN. WHERE CODE HAS BEEN IDENTIFIED IT WILL BE CITED.
25+
================================================================================
26+
****************************************************************************************************************************************************************/
27+
28+
BEGIN
29+
SET NOCOUNT ON;
30+
DECLARE
31+
@TracePath NVARCHAR(4000);
32+
SELECT @TracePath = CAST(value AS NVARCHAR(4000))
33+
FROM [sys].[fn_trace_getinfo](1)
34+
WHERE [property] = 2;
35+
SELECT @TracePath;
36+
SELECT [e].[name] AS [event_name],
37+
[t].[loginname],
38+
[t].[spid],
39+
[t].[databasename],
40+
[t].[filename],
41+
[t].[starttime],
42+
[t].[endtime]
43+
FROM [sys].[fn_trace_gettable](@TracePath, DEFAULT) AS [t]
44+
INNER JOIN [sys].[trace_events] AS [e] ON [t].[eventclass] = [e].[trace_event_id]
45+
WHERE [t].[eventclass] IN(92, 93)
46+
ORDER BY [endtime];
47+
END;
48+
GO

‎ListDBFileSettingsAndGrowth(2012+).sql‎

Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,171 @@
1+
/************************************************************************************************
2+
================================================================================
3+
DESCRIPTION:
4+
--------------------------------------------------------------------------------
5+
List all databases and all files in the databsae, along with sizing used and free
6+
and the growth values
7+
SQL Server 2012+
8+
9+
HISTORY:
10+
--------------------------------------------------------------------------------
11+
Date: Developer: Description:
12+
--------------------------------------------------------------------------------
13+
* * Created
14+
--------------------------------------------------------------------------------
15+
16+
NOTES:
17+
--------------------------------------------------------------------------------
18+
THIS SCRIPT/CODE ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED.
19+
LIMITED TESTING HAS BEEN PERFORMED ON THIS SCRIPT/CODE AND THEREFORE THE AUTHOR DOES NOT WARRANT
20+
THAT ANY SCRIPT/CODE IS BUG OR ERROR-FREE. IT IS EXPECTED THAT ANY SCRIPT/CODE UNDERGO YOUR OWN
21+
TESTING AND/OR VALIDATION BEFORE USAGE ON ANY CRITICAL SQL SERVER PLATFORM.
22+
THIS SCRIPT MAY BE A COLLECTION OF MY OWN CODE COLLATED OVER MANY YEARS, OR OTHER CODE I HAVE
23+
LOCATED ON THE WEB WITH AN UNKNOWN ORIGIN. WHERE CODE HAS BEEN IDENTIFIED IT WILL BE CITED.
24+
================================================================================
25+
************************************************************************************************/
26+
27+
BEGIN
28+
SET NOCOUNT ON;
29+
30+
-- CALCULATE DATABASE GROWTH OPTIONS CALCULATIONS
31+
DECLARE
32+
@DBName SYSNAME,
33+
@SQLString NVARCHAR(4000);
34+
CREATE TABLE [#tempforfilestats]
35+
(
36+
[database name] VARCHAR(35) NOT NULL,
37+
[file name] VARCHAR(128) NOT NULL,
38+
[usage type] VARCHAR(6) NOT NULL,
39+
[size (mb)] REAL NOT NULL,
40+
[space used (mb)] REAL NULL,
41+
[maxsize (mb)] REAL NOT NULL,
42+
[next allocation (mb)] REAL NOT NULL,
43+
[growth type] VARCHAR(12) NOT NULL,
44+
[file id] SMALLINT NOT NULL,
45+
[group id] SMALLINT NOT NULL,
46+
[physical file] NVARCHAR(260) NOT NULL,
47+
[date checked] DATETIME NOT NULL
48+
);
49+
CREATE TABLE [#tempfordatafile]
50+
(
51+
[file id] SMALLINT NOT NULL,
52+
[group id] SMALLINT NOT NULL,
53+
[total extents] INT NOT NULL,
54+
[used extents] INT NOT NULL,
55+
[file name] NVARCHAR(128) NOT NULL,
56+
[physical file] NVARCHAR(260) NOT NULL
57+
);
58+
CREATE TABLE [#tempforlogfile]
59+
(
60+
[recovery unit id] INT NOT NULL,
61+
[file id] INT NOT NULL,
62+
[size (bytes)] REAL NOT NULL,
63+
[start offset] VARCHAR(50) NOT NULL,
64+
[fseqno] INT NOT NULL,
65+
[status] INT NOT NULL,
66+
[parity] SMALLINT NOT NULL,
67+
[createtime] VARCHAR(50) NOT NULL
68+
);
69+
DECLARE cursdb CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
70+
FOR SELECT [name]
71+
FROM [master].[dbo].[sysdatabases](nolock)
72+
WHERE [status]&512 <> 512 -- NOT OFFLINE
73+
AND [status]&1073741824 <> 1073741824 -- NOT SUSPECT
74+
AND [status]&32 <> 32 -- NOT LOADING
75+
AND [status]&128 <> 128; -- NOT RECOVERING
76+
77+
OPEN cursdb;
78+
FETCH FROM cursdb INTO
79+
@DBName;
80+
WHILE(@@FETCH_STATUS = 0)
81+
BEGIN
82+
SELECT @SQLString = ''+'select '+''''+@DBName+''''+' as ''Database'', '+'convert(nvarchar(128), f.name), '+'case '+' when (64 & f.status) = 64 then ''Log'' '+' else ''Data'' '+'end as ''Usage Type'', '+'f.size * 8.00 / 1024.00 as ''Size (MB)'', '+'NULL as ''Space Used (MB)'', '+'case '+' when f.maxsize < 0 then -1 '+' when f.maxsize = 0 then f.size * 8.00 / 1024.00'+' when f.maxsize > 2147483647 then 2147483647 '+' else f.maxsize * 8.00 / 1024.00 '+'end as ''max Size (MB)'', '+'case '+' when (1048576 & f.status) = 1048576 then growth'+' when f.growth = 0 then 0 '+' else f.growth * 8.00 / 1024.00 '+'end as ''next Allocation (MB)'', '+'case '+' when (1048576 & f.status) = 1048576 then ''%'' '+' else ''Mb'' '+'end as ''Usage Type'', '+'f.fileid, '+'f.groupid, '+'filename, '+'getdate() '+' from ['+@DBName+'].dbo.sysfiles f (nolock)';
83+
84+
--print @SQLString
85+
INSERT INTO [#tempforfilestats]
86+
EXECUTE (@SQLString);
87+
88+
-- SHOW FILE STATS
89+
SELECT @SQLString = 'USE ['+@DBName+'] DBCC SHOWFILESTATS';
90+
91+
--print @SQLString
92+
INSERT INTO [#tempfordatafile]
93+
EXECUTE (@SQLString);
94+
UPDATE [#tempforfilestats]
95+
SET
96+
[space used (mb)] =
97+
CONVERT( REAL, [s].[used extents]) * 64 / 1024.00
98+
FROM [#tempforfilestats] [f](nolock)
99+
INNER JOIN [#tempfordatafile] [s](nolock) ON [f].[file id] = [s].[file id]
100+
AND [f].[group id] = [s].[group id]
101+
WHERE [f].[database name] = @DBName;
102+
TRUNCATE TABLE [#tempfordatafile];
103+
104+
-- LOG INFO
105+
SELECT @SQLString = 'USE ['+@DBName+'] DBCC LOGINFO';
106+
107+
--print @SQLString
108+
INSERT INTO [#tempforlogfile]
109+
EXECUTE (@SQLString);
110+
UPDATE [#tempforfilestats]
111+
SET
112+
[space used (mb)] =
113+
(
114+
SELECT
115+
(
116+
MIN([l].[start offset]) + SUM(CASE
117+
WHEN [l].[status] <> 0
118+
THEN [l].[size (bytes)]
119+
ELSE 0
120+
END)
121+
) / 1048576.00
122+
FROM [#tempforlogfile] AS [l](nolock)
123+
WHERE [l].[file id] = [f].[file id]
124+
)
125+
FROM [#tempforfilestats] [f](nolock)
126+
WHERE [f].[database name] = @DBName
127+
AND [f].[usage type] = 'Log';
128+
TRUNCATE TABLE [#tempforlogfile];
129+
FETCH NEXT FROM cursdb INTO
130+
@DBName;
131+
END;
132+
CLOSE cursdb;
133+
DEALLOCATE cursdb;
134+
END;
135+
GO
136+
PRINT '';
137+
SELECT [database name],
138+
[file name],
139+
[usage type],
140+
[size (mb)],
141+
[space used (mb)],
142+
CAST(
143+
(
144+
[space used (mb)] / [size (mb)] * 100.00
145+
) AS NUMERIC(5, 2)) AS [space used (%)],
146+
CASE CONVERT( VARCHAR(25), [maxsize (mb)])
147+
WHEN '-1'
148+
THEN 'No max Size'
149+
ELSE CONVERT(VARCHAR(25), [maxsize (mb)])
150+
END AS 'maxSize',
151+
CASE CONVERT( VARCHAR, [next allocation (mb)])
152+
WHEN '0'
153+
THEN 'No'
154+
ELSE 'Yes - '+CONVERT(VARCHAR(25), [next allocation (mb)])+' '+[growth type]
155+
END AS 'Auto Grow',
156+
[next allocation (mb)],
157+
[growth type],
158+
[file id],
159+
[group id],
160+
[physical file],
161+
[date checked]
162+
FROM [#tempforfilestats](nolock)
163+
ORDER BY 1,
164+
2;
165+
GO
166+
DROP TABLE [#tempforfilestats];
167+
GO
168+
DROP TABLE [#tempfordatafile];
169+
GO
170+
DROP TABLE [#tempforlogfile];
171+
GO

0 commit comments

Comments
 (0)