SQL Server Logs is the first place we search whenever there is an issue on SQL Server. Due to default settings, these logs also contain generic information which makes it very difficult for us to find important information. Since there is too much of data, we tend to miss the necessary information. In this article, we will learn how to keep SQL Server Logs clean and simple
SQL Server Backup filling up SQL Server log
It is a common scenario to see many databases hosted within a single SQL Server instance. It is considered as a best practice to take backup of the databases frequently. However, if all the databases are backed up often, these successful entries are written to the SQL Server Log and the Log will grow big exponentially.
Fix this using Trace
To fix this manually, you can turn on the Trace 3226 on that single SQL Server Instance. However, if you have to do this on multiple SQL Server instances, you can quickly complete it using TSQL script. We are going to create a table ‘TBL1’ and save all our SQL Server names inside this table. Please ensure that this script is run from a Central Server and all SQL Servers listed in the TBL1 have a corresponding linked server, since this script uses linked server to turn on the Trace. Using a cursor, each SQL Server name from TBL1 is read. Then using RPC and Linked Server, a dynamic command is executed on each SQL Server. You can also add all SQL Servers as Registered Servers and then from a single Query window you can enable the Trace 3226 at a global level.
Script:
CREATE TABLE TBL1(cservername VARCHAR(200));
INSERT INTO TBL1
VALUES('SERVERNAME');
DECLARE @cmd NVARCHAR(4000);
DECLARE @cmd2 NVARCHAR(4000);
DECLARE @vsvrname VARCHAR(200);
DECLARE cur1 CURSOR
FOR SELECT cservername
FROM TBL1;
OPEN cur1;
FETCH NEXT FROM cur1 INTO @vsvrname;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @vsvrname;
SET @cmd2 = 'DBCC TRACEON (3226,-1)';
SET @cmd = 'EXEC ('''+@cmd2+''') at ['+@vsvrname+']';
PRINT @cmd;
EXEC (@cmd);
FETCH NEXT FROM cur1 INTO @vsvrname;
END;
CLOSE cur1;
DEALLOCATE cur1;
GO
SQL Server Log will capture this
You don’t separate table to capture when the Trace was turned on for a particular SQL Server. SQL Server Log will capture this information automatically. Though this Trace will stop successful backup messages from being written to the SQL Server log, success messages would still get written to the MSDB database and if you can’t read your MSDB database, there are higher chances that it might be corrupted and MDF repair is the only option you are left with.
Restore default settings
To enable successful backup messages to get written to the SQL Server Log, you can reuse the same script by changing the keyword “TRACEON” to “TRACEOFF”. Please do note that these settings will disappear whenever the SQL Server Service is restarted. To make these as permanent settings, you have to add these trace flags as startup parameters to SQL Server Service.
Author Introduction:
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com
Leave a Reply