March 5, 2020 at 6:38 pm
Hello Everyone,
I am new to develop queries and planning to automate Log Full issues fix using stored procedure and trying to develop the SP. Could you please help me to complete it. Below query is not working if log_reuse_wait_desc is due to LOG_BACKUP and getting the error : Error converting data type nvarchar to nvarchar.
DECLARE @subject nvarchar(max)
DECLARE @emailReportFlag1 nvarchar(Max)
DECLARE @emailReportFlag2 nvarchar(Max)
SET @emailReportFlag1 =0
SET @emailReportFlag2 =0
SET @subject = 'Log File Status ' + ' on ' + CONVERT(varchar(35), getdate(), 113)
Print @subject
SET @emailReportFlag1 =( select top 1 (name) from sys.databases where log_reuse_wait_desc = 'NOTHING' )
SET @emailReportFlag2 =( select top 1 (name) from sys.databases where log_reuse_wait_desc = 'LOG_BACKUP' )
print @emailReportFlag1
print @emailReportFlag2
IF (@emailReportFlag1 = 'master')
BEGIN
select name, log_reuse_wait_desc from sys.databases
END
--ELSE
IF (@emailReportFlag2 is not null)
BEGIN
BACKUP LOG @emailReportFlag2 TO DISK = 'C:\dbname.trn'
END
Thanks in Advance
March 5, 2020 at 8:57 pm
I'm not sure where you script is creating the error. You should have a line number that helps you determine this.
The backup log command should work find with nvarchar(max). It has for me.
March 5, 2020 at 10:06 pm
given that all you are storing in the @emailReportFlag variables is just the name of a database, those could just be defined as type SYSNAME or NVARCHAR(256). I believe that will work better as a parameter to the BACKUP LOG command.
You may want to come up with a better nomenclature for the log backup file though so you don't overwrite it constantly.
One other thing to consider, when checking for 'LOG_BACKUP' log reuse wait desc, you may want to not include the MODEL database. There shouldn't be any reason to try to backup its transaction log since it won't have any user tables in it.
March 6, 2020 at 11:54 am
chris is correct - if you keep over writing the last t-log backup then you don't have an effective DR plan
if the issue is that your t-logs are getting big and you are just backing them up to allow them to shrink …. then you need to set the database to simple mode
if you require Point in time restores then you have lots of options.. but are you trying to fix a checkpoint/VLF issue
there is a really good article here
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply