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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
| /* sp_shrinklog
Created by Dan Wunder 06/01/2k1
Configurable transaction log shrinking program
*/
if exists (select name from sysobjects where name like 'sp_shrinklog') drop proc sp_shrinklog
go
CREATE PROCEDURE sp_shrinklog
@dbname varchar(75)=null,
@NewSize int=50,
@maxtime int=5,
@killusers int=0,
@fileid int=0,
@replcommit int=0
AS
BEGIN
SET nocount on
DECLARE
@cmd1 varchar(512),
@connect varchar(255),
@statvar int,
@lfn varchar(128),
@Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255),
@loopvar int,
@numtrans int,
@strtime varchar(10),
@maxloop int,
@floatvar float,
@spid smallint,
@OriginalSize int
SET @loopvar=1
SET @numtrans=50000 --Equates to 50000 transactions per shrink loop. Modify value if needed.
SET @maxloop=2
SET @connect='master..xp_cmdshell ''OSQL -d'+@dbname+' -E -Q ' --Trusted connection OSQL header
IF @dbname is null GOTO syntax --If dbname not supplied display command syntax
--Convert @maxtime to string
IF @maxtime between 0 and 9 SET @strtime='00:0'+cast(@maxtime AS char(1))+':00'
IF @maxtime between 10 and 59 SET @strtime='00:'+cast(@maxtime AS char(2))+':00'
IF @maxtime < 1 or @maxtime > 59
BEGIN
SELECT 'Invalid value for maxtime. Please use a number between 1 and 59'
RETURN(-1)
END
--Determine which log to shrink based on data passed to procedure
CREATE TABLE #t5 (col1 int not null)
SET @cmd1='INSERT #t5 SELECT count(*) AS col1 FROM '+@dbname+'..sysfiles'
EXEC (@cmd1)
SELECT @statvar=col1 FROM #t5
DROP TABLE #t5
IF @statvar=2 and @fileid=0 set @fileid=2 --Set default value of 2 if applicable
IF @statvar > 2 --If the database consists of more than 2 physical files
BEGIN
IF @fileid = 0 --No value supplied
BEGIN
SET @cmd1='raiserror (''Fileid not specified for log shrink process on database '+@dbname+'. Shrink did not occur'',15,1) with log'
EXEC (@cmd1)
RETURN(-1)
END
IF (@fileid > @statvar) or (@fileid < 2) --Value out of range
BEGIN
SET @cmd1='raiserror (''Invalid fileid for log shrink process on database '+@dbname+'. Shrink did not occur'',15,1) with log'
EXEC (@cmd1)
RETURN(-1)
END
END
CREATE TABLE #t4 (col1 varchar (255) not null)
SET @cmd1='INSERT #t4 SELECT name FROM '+@dbname+'..sysfiles WHERE fileid='+convert(char(2),@fileid)
EXEC(@cmd1)
SELECT @lfn=rtrim(col1) FROM #t4 --Trim trailing blanks from sysfiles value
DROP TABLE #t4
IF @replcommit=1 --Commit replicated transactions option is set
BEGIN
CREATE TABLE #t6 (x varchar(500))
INSERT #t6 EXEC ('select name from '+@dbname+'..sysobjects where name like ''syspublications''')
IF not exists (SELECT * FROM #t6) --Check to see if syspublications table exists
BEGIN
SET @cmd1='master..xp_logevent 99999,''Replcommit option ignored- database '+@dbname+' is not replicated'',INFORMATIONAL'
EXEC(@cmd1)
GOTO transcheck
END
DELETE from #t6
CREATE TABLE #t7 (x varchar(500))
INSERT #t7 EXEC ('select name from '+@dbname+'..sysobjects where name like ''sysmergepublications''')
IF exists (SELECT * FROM #t7) --Check to see if sysmergepublications table exists
BEGIN
SET @cmd1='SELECT name FROM '+@dbname+'..sysmergepublications WHERE publisher_db=''%'+@dbname+'%'''
insert #t6 exec(@cmd1)
END
set @cmd1='SELECT description FROM '+@dbname+'..syspublications WHERE description like ''%'+@dbname+'%'''
insert #t6 exec(@cmd1)
DROP TABLE #t7
IF exists (SELECT * FROM #t6) --Execute sp_repldone unless the database is not the replication source db
BEGIN
SET @cmd1=' "sp_replflush"'''
EXEC (@connect+@cmd1)
SET @cmd1=' "sp_repldone @xactid=null,@xact_seqno=null,@reset=1"'''
EXEC(@connect+@cmd1)
SET @cmd1='master..xp_logevent 77777,''You must reinitialize the snapshot for database '+@dbname+' after the logshrink process completes'',WARNING'
EXEC(@cmd1)
END
ELSE
BEGIN
SET @cmd1='master..xp_logevent 99999,''Replcommit option ignored- database '+@dbname+' is not the replication source database'',INFORMATIONAL'
EXEC(@cmd1)
GOTO transcheck
END
DROP TABLE #t6
END
transcheck: --Determine if there are transactions pending in the database's log
CREATE TABLE #t1 (col1 varchar(255) null)
SET @cmd1='master..xp_cmdshell ''OSQL /Q"DBCC OPENTRAN("'+@dbname+'")"'''
INSERT #t1 EXEC(@cmd1)
IF (SELECT count(*) FROM #t1 WHERE col1 like '%No active open transactions%') = 0 --If open transactions are present
BEGIN
IF @killusers=0 --Killusers option not set
BEGIN
SET @cmd1='master..xp_logevent 99999,''There are active users in '+@dbname+' and the killusers option has not been set. The log file may not shrink.'',INFORMATIONAL'
EXEC (@cmd1)
DROP TABLE #t1
GOTO shrinklog
END
ELSE
BEGIN --Killusers option set
DROP TABLE #t1
SELECT ('Attempting to kill active user connections')
CREATE TABLE #t2 (
spid int,
status varchar(32),
login varchar(32),
host varchar(32),
block varchar(8),
dbname varchar(32),
lastcmd varchar(255))
INSERT into #t2 EXEC sp_who
DECLARE getlogins cursor
FOR
SELECT spid FROM #t2 WHERE dbname = @dbName
OPEN getlogins
FETCH getlogins into @spid
WHILE @@FETCH_status = 0
BEGIN
SELECT @cmd1 = ' "kill ' + CONVERT(varchar, @spid)+'"'''
EXEC(@connect+@cmd1)
FETCH getlogins into @spid
END
CLOSE getlogins
DEALLOCATE getlogins
DROP TABLE #t2
END
END
shrinklog:
--Modified MS Log shrink code
-- Setup / initialize
CREATE TABLE #t3 (col1 int)
SET @cmd1='INSERT #t3 SELECT size FROM '+@dbname+'..sysfiles WHERE name='''+@LFN+'''' -- in 8K pages
EXEC (@cmd1)
SELECT @OriginalSize = col1 FROM #t3
DROP TABLE #t3
SET @cmd1=' "CREATE TABLE DummyTrans (DummyColumn char (8000) not null)"'''
EXEC(@connect+@cmd1+',no_output')
SELECT @TruncLog = 'BACKUP LOG ' + @dbname + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
SET @cmd1=' "DBCC SHRINKFILE ('+@lfn+','+convert(varchar(4),@newsize)+')"'''
EXEC (@connect+@cmd1+',no_output')
EXEC (@TruncLog)
-- Wrap the log if necessary.
SELECT @StartTime = GETDATE()
WHILE
(@maxtime > datediff (mi, @StartTime, GETDATE())) -- time has not expired
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < @numtrans))
BEGIN
SET @cmd1='INSERT '+@dbname+'..dummytrans VALUES (''Fill Log'')' -- Because it is a char field it inserts 8000 bytes.
EXEC(@cmd1)
SET @cmd1='DELETE '+@dbname+'..dummytrans'
EXEC(@cmd1)
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END
SET @cmd1='''Final Size of ' + @dbname + ' LOG is ''' +
'+CONVERT(VARCHAR(30),size)+' + '''8K pages or ''' +
'+CONVERT(VARCHAR(30),(size*8/1024))+' + '''MB'''
SET @cmd1='SELECT '+@cmd1+' FROM '+@dbname+'..sysfiles WHERE name = '+''''+@lfn+''''
EXEC (@cmd1)
SET @cmd1='DROP TABLE '+@dbname+'..DummyTrans'
EXEC(@cmd1)
SET NOCOUNT OFF
RETURN(1)
syntax: --Commands listed if no database name provided
SELECT 'sp_shrinklog [dbname],[newsize],[maxtime],[killusers],[fileid],[replcommit]
[dbname] The name of the database containing the log to be shrunk. Default=null
[newsize] New size of the log. Default=2MB or minimum log size whichever is greater.
[maxtime] The maximum number of minutes this process will run [1-59]. Default=5.
[killusers] Specify 1 if you want the sp to kill connections to the database. Default=0.
[fileid] The id of the log (for databases with more than one log). Default=0. [more]'
SELECT ' [replcommit] Setting this option to 1 forces all pending replication transactions
to be marked as distributed. Default=0
Warning: You will need to re-run the snapshot agent on any database whose logs
have been shrunk with this option enabled to ensure the integrity of
the subscriber database.'
SELECT ' In order to maintain the integrity of transaction log backups, a full database backup
must be performed after running this stored procedure.'
SET nocount off
RETURN(0)
END
GO |
/u/9258/killbill.png?f=community)
:strip_icc():strip_exif()/u/15793/zwelgje100.jpg?f=community)
oeps
