T-Log Monitoring/Reporting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

T-Log Monitoring/Reporting

Author

Topic

misken
Starting Member

8 Posts

Posted - 2014-08-01 : 07:24:47

Hello all,

Im a junior developer consultant that got an assignment as an accidental DBA a couple of weeks ago, and since i got interested in Transaction logs i studied(googling forums) and realized there was a need to monitor it.

I wrote a script, then kept going making it run from one server by linking servers. Now i dont quite have the guts to implement it before getting some constructive criticism, pointers or warnings from more experienced DBAs since the job runs as a domain user through a proxy as sysadmin on all these server(for DBCC Loginfo). Heres my script, tried it and works like charm for what i wanted :)

Any heads ups for it?

USE master

-- Add linked servers - Separated from the rest of the batch job since adding linked servers need an individual batch

DECLARE @iterator int = 1
DECLARE @numberOfRows int = (SELECT COUNT(*) FROM DBA_Maintenance.dbo.Server_List)
DECLARE @lsrvName nvarchar(12)

WHILE (@iterator <= @numberOfRows)
BEGIN
-- protection against the possibility of servers being removed from the infrastructure(and hence from the server_list-table) since the PK wont replace removed PK nr
tryagain:
IF (SELECT ServerName FROM DBA_Maintenance.dbo.Server_List WHERE RowNumber = @iterator) IS NULL
BEGIN
SET @iterator = @iterator +1
GOTO tryagain
END

SET @lsrvName = (SELECT ServerName FROM DBA_Maintenance.dbo.Server_List WHERE RowNumber = @iterator)

if exists(select * from sysservers WHERE srvname = @lsrvName)
exec sp_dropserver @lsrvName
EXEC sp_addlinkedserver
@lsrvName,
N'SQL Server'

SET @iterator = @iterator +1
END
GO

IF OBJECT_ID('tempdb..#Loginfo') IS NOT NULL
DROP TABLE #Loginfo
CREATE TABLE #Loginfo
(FileID varchar(3), FileSize numeric(20,0),
StartOffset bigint, FSeqNo bigint, Status char(1),
Parity varchar(4), CreateLSN numeric(25,0))

IF OBJECT_ID('tempdb..#VLF_temp') IS NOT NULL
DROP TABLE #VLF_temp
-- OBS!!! If the target server is SQL 2012, then Column "RecoveryUnitId numeric(10,0)" must be added as first column in table definition
CREATE TABLE #VLF_temp
(FileID varchar(3), FileSize numeric(20,0),
StartOffset bigint, FSeqNo bigint, Status char(1),
Parity varchar(4), CreateLSN numeric(25,0))

IF OBJECT_ID('tempdb..#VLF_db_total_temp') IS NOT NULL
DROP TABLE #VLF_db_total_temp
CREATE TABLE #VLF_db_total_temp
(name sysname, vlf_count int)

DECLARE all_servers CURSOR READ_ONLY FOR
SELECT RowNumber, ServerName FROM DBA_Maintenance.dbo.Server_List

DECLARE @databasename sysname
DECLARE @RowNumber int
DECLARE @srvName nvarchar(12)
DECLARE @ExecLinkedSrv nvarchar(400)
DECLARE @tempSrvName nvarchar(12)

OPEN all_servers
FETCH NEXT FROM all_servers INTO @RowNumber, @srvName

-- Declare all_databases Cursor (cant Declare to linked server with a straight DECLARE due to variables)
DECLARE @getCurrentServersDBsCursor nvarchar(255)
DECLARE @mailBody varchar(Max) = N'Indications for possible fragmentation:' + CHAR(13) + CHAR(13)
DECLARE @give_sa nvarchar(max)

WHILE (@@FETCH_STATUS = 0)
BEGIN

EXEC master.dbo.sp_serveroption @server=@srvName, @optname=N'remote proc transaction promotion', @optvalue=N'FALSE'

SET @getCurrentServersDBsCursor = N'DECLARE all_databases CURSOR READ_ONLY FOR ' +
N'SELECT name FROM [' + @srvName + N'].[master].dbo.sysdatabases'

EXEC sp_executesql @getCurrentServersDBsCursor

OPEN all_databases
FETCH NEXT FROM all_databases INTO @databasename

WHILE (@@fetch_status = 0)
BEGIN
-- dont know how to fix this. Its for the notorious syntax error next to '-' thing.
IF @databasename LIKE '%-%'
BREAK

DECLARE @remoteStatement nvarchar(max)= N'DBCC LOGINFO WITH NO_INFOMSGS'
DECLARE @localStatement nvarchar(max) = @srvName + N'.' + @databaseName + '.dbo.sp_executesql @remoteStatement;'
INSERT INTO #VLF_temp
exec sp_executesql @localStatement, N'@remoteStatement nvarchar(max)', @remoteStatement;

INSERT INTO #VLF_db_total_temp
SELECT @databasename, COUNT(*) FROM #VLF_temp
TRUNCATE TABLE #VLF_temp
FETCH NEXT FROM all_databases INTO @databasename
END
DEALLOCATE all_databases

DECLARE temptable CURSOR FOR
SELECT TOP 3
@srvName as [Servername], name as [DBName], vlf_count as [VLFCount]
FROM #VLF_db_total_temp
WHERE vlf_count > 100
ORDER BY vlf_count DESC

DECLARE @Servername varchar(12)
DECLARE @DBName sysname
DECLARE @VLFCount int
DECLARE @firstVLFSize varchar(50)
DECLARE @lastVLFSize varchar(50)

OPEN temptable
FETCH NEXT FROM temptable INTO @Servername, @DBName, @VLFCount
BEGIN

SET @mailBody += N'Server:' + @Servername +CHAR(13) +CHAR(13)

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @remoteStatement = N'DBCC LOGINFO WITH NO_INFOMSGS'
SET @localStatement = @srvName + N'.' + @DBName + '.dbo.sp_executesql @remoteStatement;'
INSERT INTO #Loginfo
exec sp_executesql @localStatement, N'@remoteStatement nvarchar(max)', @remoteStatement;

SET @firstVLFSize =
(SELECT TOP 1 (FileSize /1024.0/1024.0) AS lastFiles_Size FROM #Loginfo)
SET @lastVLFSize =
(SELECT TOP 1 (FileSize /1024.0/1024.0) AS lastFiles_Size FROM #Loginfo
ORDER BY FSeqNo DESC)

SET @mailBody +=
char(9) +N'Database: '+ @DBName + CHAR(13)+
char(9) +N'Number of VLFs: '+CAST(@VLFCount AS varchar(50)) + CHAR(13) +
char(9) +N'Size of first virtual log file: ' + SUBSTRING(@firstVLFSize, 1, 7) + ' MB'+ CHAR(13) +
char(9) +N'Size of last virtual log file: ' + SUBSTRING(@lastVLFSize, 1, 7) + ' MB' + CHAR(13)+ CHAR(13)
FETCH NEXT FROM temptable INTO @Servername, @DBName, @VLFCount
END

CLOSE temptable
DEALLOCATE temptable
TRUNCATE TABLE #VLF_temp
TRUNCATE TABLE [#VLF_db_total_temp]
TRUNCATE TABLE [#Loginfo]
FETCH NEXT FROM all_servers INTO @RowNumber, @srvName
END
END
DEALLOCATE all_servers

DROP TABLE [#VLF_temp]
DROP TABLE [#VLF_db_total_temp]
DROP TABLE [#Loginfo]

DECLARE LinkedServersToRemove CURSOR FOR SELECT * FROM DBA_Maintenance.dbo.Server_List
OPEN LinkedServersToRemove
FETCH NEXT FROM LinkedServersToRemove into @RowNumber, @tempSrvName

WHILE (@@fetch_status = 0)
BEGIN
if exists(select TOP 1 srvname from sysservers WHERE srvname = @tempSrvName)
exec sp_dropserver @tempSrvName
FETCH NEXT FROM LinkedServersToRemove into @RowNumber, @tempSrvName
END

CLOSE LinkedServersToRemove
DEALLOCATE LinkedServersToRemove

exec msdb.dbo.sp_send_dbmail
@profile_name = 'profile',
@recipients = 'mail_address',
@body = @mailBody,
@subject = 'T-Log fragmentation report'
GO

Sys.developer/accidental DBA

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-01 : 08:16:40

You might look into configuring an alert rather than linked servers.

And if you really want to implement some management power -- look into Master Target management and Data collection

http://technet.microsoft.com/en-us/library/bb677248(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms180992.aspx

misken
Starting Member

8 Posts

Posted - 2014-08-01 : 09:45:08

Thnx Michael for the input. The alerts doesnt seem to have what i was looking for, which is automaticly keeping track of number of VLF's.

The master target management looks pretty interesting though, does it have the option of monitoring the number of VLFs, and maybe also the min/max sizes, initial VLF size etc.

What im trying to achieve, is to escape the need to log in to all of these servers to run the LOGINFO and PERFLOG to get the information i need for planning optimization only for the servers that could need some attention.

I hope i made sense :)

Sys.developer/accidental DBA

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-01 : 10:19:06

Yes,

With alerts you would need to setup on each server. I don't remember if you can deploy via master target (would need to check - but think you can), but you can deploy all jobs via master target.

So if you create a master server and add your targets - you could deploy a job with your script to email you based on a threshold. once you define all your targets, you can manage them from one central server.

The data collection piece I believe has custom sql collection and will be able to collect this data and automatically send it back to a central DWH

misken
Starting Member

8 Posts

Posted - 2014-08-04 : 03:47:17

Oh I will definitely look into it more, thanks!

Is there anything in the script that could issue any security problem or any "shouldnt do"?

I know cursors isnt the best thing because of the overhead, but its a small job and runs quite fast

Sys.developer/accidental DBA

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-04 : 14:43:36

At quick glance it looks like you are looking up some servers, creating linked servers, running DBCC LOGINFO and sending out the results - It should be good - there are plenty of scripts on like this out on the web. it should be fine.

misken
Starting Member

8 Posts

Posted - 2014-08-05 : 05:13:12

Gr8 thnx for the tips and help m8 :)

Sys.developer/accidental DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources