Policy Based Management - Enabling Transaction Logs

Question

Post reply

Policy Based Management - Enabling Transaction Logs

swoozie

SSCrazy

Points: 2770
More actions
July 25, 2014 at 12:10 pm

#291368

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

swoozie SSCrazy Points: 2770 More actions · Answer 2

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@PathNVARCHAR(255),

@FileNameNVARCHAR(128),

@NameNVARCHAR(128),

@SIZENVARCHAR(10),

@MAXSIZENVARCHAR(10),

@FILEGROWTHNVARCHAR(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.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass