Überwachung von Updates auf Tabellenebene

[Skimausi2908]

Hallo an alle,
ich habe einen SQL Server 2014, SE und bin am "aufräumen". Dafür habe ich folgendes Script gefunden was mir hilft, von allen Tabellen die Info zu erhalten, wann sie das letztes Mal geändert wurden.
WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

Gibt es jetzt noch die Möglichkeit heraus zu filtern, welcher User geändert hat? Hat jemand eine Idee?

[defo]

Ich seh da ganz viele Index Views. Bist Du sicher, dass das vollständige(!) Informationen über Tabellenänderungen sind?
Deine eigentlich Frage kann ich nicht beantworten.
Ich empfehle eine Sichtung der Dictionary Views und MSDN Doku.

[Skimausi2908]

Diese Infos reichen mir. Ich will herausfinden, welche Tabellen überhaupt nicht mehr benutzt werden und gelöscht werden können. Da es mehrere Datenbanken betrifft und mehrere Bereiche in der Firma damit arbeiten, bräuchte ich halt den Usernamen noch, damit ich mit den Leuten nochmal Rücksprache nehmen könnte. Es handelt sich hier um Projekte, die "historisch" gewachsen sind und wo es Not tut, mal Ordnung zu schaffen.

[defo]

Diese Infos reichen mir. Ich will herausfinden, welche Tabellen überhaupt nicht mehr benutzt werden und gelöscht werden können. Da es mehrere Datenbanken betrifft und mehrere Bereiche in der Firma damit arbeiten, bräuchte ich halt den Usernamen noch, damit ich mit den Leuten nochmal Rücksprache nehmen könnte. Es handelt sich hier um Projekte, die "historisch" gewachsen sind und wo es Not tut, mal Ordnung zu schaffen.

Ok, da wäre mein Ansatz ein anderer. (Dabei gehe ich davon aus, dass gewisse Grundprinzipien in den DB verschiedener Hersteller gleich sind, ich arbeite aber nicht mit MS SQL)
1. Referenzierte Objekte
Das dictionary muss über einige Dinge Buch führen. Z.B. referentielle Constraints. Aber auch Trigger, Views, SP, .. die eine Tabelle nutzen, müssen irgendwo verzeichnet sein. Hilft natürlich nur bedingt, wenn irgendwo eine Solitärtabelle rumfliegt (oder viele)
2. Query Cache
Verzeichnet alle aktuellen Aufrufe, zwecks Analyse, Optimierung.
Hier kann man ständig alles abgreifen, was "eintrudelt".
3. Aktive "Forschung"
Log Trigger oder Trace Anweisungen auf fragwürdige Tabellen legen
Resultate dann entsprechend untersuchen
4. Geduld
Ich vermute, dass bestimmte Tabellen Anlass orientiert genutzt werden. Vielleicht nur 1x im Jahr ((Geschäfts-)Jahresabschluss) oder seltener (Stammdaten, Synchronisationsabgleich)
5. Weitsicht
Je mehr ein Datenbankserver nicht als Server, sondern als "dummer" Datencontainer eingesetzt wird, desto weniger kann man über die Schritte 1, 2, 4 rausfinden. Alles was an "Anwendungslogik" außerhalb des Servers definiert ist, kann man nicht abfragen, wissen. Selbst wenn halbwegs ordentlich mit Constraint und SP gearbeitet wird. Ein einzelnes, dynamisches "Execute" in einer SP reicht, fachlich starke und komplexe Zusammenhänge zu verbergen (vor dem Dictionary und Quellcoderecherche. Denn es liegt in der Natur dynamischer Aufrufe, dass eben keine "compiletime" Abhängigkeiten definiert werden und tatsächlich aufgerufene Funktionen dynamisch, situationsabhängig, z.B. interaktiv oder über Mappingtabellen zusammengesetzt werden.

Da man de facto also nichts über Tabellen weiß, wäre der Punkt also gar nicht "Aufräumen", sondern auflisten, ggF. "Wegsperren"/Ent- oder Umrechten, umbennenen und abwarten, wer heulend ankommt.
Dann archivieren, dann löschen.

P.S. Indizes verwaister Tabellen zu löschen, wäre eine Maßnahme, die sehr viel Platz sparen kann, einfach zu restaurieren ist, wenn man sich die Createanweisung archiviert und nicht zu einem absoluten Funktionsausfall führt. Sie können mehr Platz belegen, als die Tabelle selbst. Beschwerden würden wahrscheinlich trotzdem eintreffen, wenn es tatsächlich benutzte Indizes waren (was keineswegs selbstverständlich ist).

[Ralf Jansen]

Dafür habe ich folgendes Script gefunden was mir hilft, von allen Tabellen die Info zu erhalten, wann sie das letztes Mal geändert wurden.

Da werden nicht Tabellen überwacht sondern Indizes. Über die Änderungen eines Index auf die Veränderung einer Tabelle zu schließen ist eher ungenau. Ein simpler Rebuild des Indexes (den man schon mal macht) macht die Daten in dem View irrelevant soweit sie da überhaupt überleben. Es gibt vermutlich noch andere Wege indirekt Änderungen von Tabellen zu erkennen aber bei keinem davon wird Userbezug Sinn machen (bei dem Feature wofür sie eigentlich gedacht waren) insofern glaube ich nicht das der irgendwo künstlich herzustellen ist. Über ein lesenden Zugriff auf Tabellen ohne Index Verwendung oder Tabellen die gar keinen Index auf den abgefragten Daten haben weißt du dann sowieso nichts da du ja die Indizes befragst und nicht die Tabellen. Klingt im allgemeinen Fall nach Sackgasse.

Userbezug bekommst du nur über Transaktionslogs (die du vermutlich nicht vollständig hast zumindest nicht für den Zeitraum der dich interessieren würde) oder aber du hast explizit das Change Data Capture Feature des Sql Server eingeschaltet (was man üblicherweise nicht hat). Wenn beides nicht da ist dann würde ich vorschlagen Daten/Tabellen von dem nicht feststellbar ist das sie noch verwendet werden zu archivieren bzw. den Zugriff darauf zu sperren und sehen was passiert.