When did this DB property Change?

Question

When did this DB property Change?

Kim Killian-SiteDataView

Ten Centuries

Points: 1309
More actions
June 5, 2012 at 9:21 pm

#392442

Comments posted to this topic are about the item When did this DB property Change?
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

Viewing 15 posts - 1 through 14 (of 14 total)

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

OldFashionGang SSC Enthusiast Points: 121 More actions · Answer 1

Hi, Kimberly. I believe your decision is not to exact. The idea to have a servers list for centralized monitoring is pretty reasonable. But tracking DB-settings changes by results of querying system table/view from time to time is not too good. It is very ease to have "missing states" scenario after some one changes setting forth and back again between requests. Database settings changes logging pretty accurately to ERRORLOG, so there is no reason to investigate such events sideway. If there is need to centralize such data and fire alerts on it, you can parse errorlog files with appropriate sp_s on servers under control.

okbangas SSChampion Points: 11773 More actions · Answer 2

I would definitely not enable xp_cmdshell on all my servers, due to the security risk of it.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Upsert SSC Veteran Points: 296 More actions · Answer 3

I would suggest setting up a database trigger instead that tracks ddl events:

create trigger [LOG_EVENTS]

on database

for create_procedure, alter_procedure, drop_procedure,

create_table, alter_table, drop_table,

create_function, alter_function, drop_function

as

set nocount on

declare @data xml

set @data = EVENTDATA()

insert into dbo.CHANGE_LOG(DATABASE_NAME, EVENT_TYPE,

[OBJECT_NAME], OBJECT_TYPE, SQL_COMMAND, LOGIN_NAME)

values(

@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),

@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),

@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),

@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')

)

Geoff A SSChampion Points: 11511 More actions · Answer 4

Hi Kim,

thanks for sharing. i would have to agree that this info you are after is easily gathered from the SQL Server Logs.

Also, you can get what you are after PLUS a lot more by reading the logs. below is a script you can run on your central server since you already have all your linked servers setup. i too am a huge fan of a centralized servers where i run things from via linked servers. the script will get all info out of the current SQL Server Logs. you could modify the final select statement with a WHERE clause to see what you want to see. as it stands, it will show you everything. hope all is well with you.

might take awhile to run if your DBA_Reports.dbo.ServerList is huge and some of your Servers are off-line....

declare @table table (ServerName varchar(128),LogDate datetime, ProcessInfo varchar(50), [Text] varchar(max))

declare @server_name nvarchar(250)

declare db_crsr_DBS cursor for

select [Server] from [DBA_Reports].[dbo].[ServerList]

open db_crsr_DBS

fetch next from db_crsr_DBS into @server_name

while @@fetch_status = 0

begin

declare @retval int;

begin try

exec @retval = sys.sp_testlinkedserver @server_name;

end try

begin catch

set @retval = sign(@@error);

end catch;

if @retval = 0

begin

declare @cmd varchar(500)

set @cmd = 'exec ['+@server_name+'].master.dbo.xp_readerrorlog 0'

insert into @table (LogDate, ProcessInfo, Text)

exec (@cmd)

update @table

set ServerName = @server_name

where ServerName is NULL

end

else

print 'Server not online';

fetch next from db_crsr_DBS into @server_name

end

close db_crsr_DBS

deallocate db_crsr_DBS

select * from @table

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 5

Thanks for the feed back everyone! Yes the logs are also a useful way to collect this information. The intention of this article is for an alternative collection as there are 10 different ways to complete something. I use this method because I have already setup complex reporting and this solution fit nicely into my current solution. 😉

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 6

Good article. As you pointed out there are many ways to pull information and your method is just as valid as any other.

David

TravisDBA SSCoach Points: 15780 More actions · Answer 7

Good article, and I agree that there are many different ways to arrive at the same result. I am reminded of something my father once said about this concept. "Son, you can cut down a tree with a power saw or a Swiss Army knife. They both will achieve the same result, the difference is the time and energy expended to get there." 😀

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

Rudy Panigas SSChampion Points: 10702 More actions · Answer 8

Hello,

Nice article!

You should really say "no" to xp_cmdshell. It is very bad for security.

Instead of using xp_cmdshell, why not just create a local database on each server (very small one) to collect information using stored procs and then use your centralized server to create a link to each server and then copy the data back to the centralized server. This link can be created and dropped once the data copy is completed and the data on each server can be overwritten.

From your centralized server you can then use SSRS to create your reports and database mail to send you information/alerts.

I've create this process for over 120+ servers and it works well.

Just my 2 bytes worth,

Rudy

TryingToLearn SSCrazy Points: 2646 More actions · Answer 9

thanks for posting this, in my environment it would be helpful, Unfortunately i am new to development and SSIS. You have provided in detail all the steps and content except the content of the last two steps for the 'collect database info' loop container, and i'm not sure what to do with the actual DBProperties.dtx you have provided at the end of the post.

thanks again.

Jim

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 10

Hi Jim,

Here is a word copy of my article and I added the instruction you asked for.

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

smthembu Hall of Fame Points: 3464 More actions · Answer 11

Nyc article but have few things to understand

U have other connections that refers to "FROM [].[VES_MS].[dbo].[Logs]" with no script to create a table referenced

Also the following step but no script for the table creation

SELECT RTRIM(Server) AS servername

FROM SSIS_ServerList

WHERE (Skip_SQL_Overview is null or Skip_SQL_Overview = 'FALSE')

ORDER BY 1

Thanks

It's better to fail while trying, rather than fail without trying!!!

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 12

Jim,

I'm sorry I just now saw this post. Did you figure it out?

Kim

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 13

@smthembu sorry here is the create script

CREATE TABLE [dbo].[SSIS_Errors](

[Server] [varchar](128) NOT NULL,

[TaskName] [varchar](128) NULL,

[ErrorCode] [int] NULL,

[ErrorDescription] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Also, I have updated the ssis package as it was not the correct one that was uploaded.

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 14

@jim - I have updated the copy of the ssis package as the previous one was uploaded in error. You will need to create a new integration services project then add this xdts package. Change the variables such as server name and database names then move the package to your integration services server and finally call it from a job to run.

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook