July 25, 2014 at 12:10 pm
I am not finding the answer that I am looking for. I am sory if there is another post out there some where I just could not find it.
I am trying to find or write a script that is run to enable the transaction log.
I have a PBM that checks the following:
SELECT COUNT(file_id)
FROM sys.database_Files
WHERE type = 1 AND state <> 0
1 = LOG
0 = ONLINE
Instead of following the instructions through the GUI to correct any fails I want to write a script. Seems simple enough but I am unable to determine the correct manner including all pieces I may need.
I figure it is somewhere in the ALTER DATABASE
code but I do not see option related to this specifically.
Thanks in advance.
July 25, 2014 at 12:27 pm
swoozie (7/25/2014)
I am trying to find or write a script that is run to enable the transaction log.
Please explain what you mean above. Transaction logs are not "enabled".
--Jeff Moden
July 25, 2014 at 1:56 pm
I had the same quandry with the wording of the company policy. I believe that enabled just means the same thing as the PBM script.
Basically I think that For Every fail, i.e., db that doesn not have a log file that I need to have a Log file created. I don't think there is an actual STATE_DESC = ONLINE or STATE_DESC = OFFLINE (STATE = 1/0) that pertains to anything but the database. I just started second guessing myself because of this being a Company wide written standard.
so in a nutshell, I want to be able to run a ALTER DATABASE script wth parameters and I think this will be the actual correct response to policy fails.
USE master;
DECLARE @Path NVARCHAR(255),
@FileName NVARCHAR(128),
@Name NVARCHAR(128),
@SIZE NVARCHAR(10),
@MAXSIZE NVARCHAR(10),
@FILEGROWTH NVARCHAR(10)
SET @Path = ''
SET @Name = ''
SET @SIZE = ''
SET @MAXSIZE = ''
SET @FILEGROWTH = ''
SET @FileName = @Path + @Name + '_Log.ldf'
GO
ALTER DATABASE dbTest1
ADD LOG FILE
(
NAME = @Name,
FILENAME = @FileName
SIZE = @SIZE,
MAXSIZE = @MAXSIZE,
FILEGROWTH = @FILEGROWTH
);
GO
I have not been able to locate an example of using parameters in this method however.
July 25, 2014 at 3:13 pm
Yeah, I'm completely lost as to what the code is trying to code. And if the db is offline, you won't be able to access the view "sys.database_files" within that db anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 25, 2014 at 3:21 pm
swoozie (7/25/2014)
Basically I think that For Every fail, i.e., db that doesn not have a log file that I need to have a Log file created.
But you can't have a database without a transaction log. It's impossible, the log file is a critical part of the database. Basically, you will never have an online database without at least one log file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply