Anmelden

Navigationslinks überspringen
Unternehmen
Leistungen
Blog
Deutsche Seiten English Sites
Startet die SucheErweiterte Suche
Kategorien
SQL Server
Analyses Services
Coding
Reporting Services
Integration Services
Microsoft Access
Sonstiges
Sharepoint
Andere Blogs
Diese Liste enthält keine Elemente.
Hyperlinks
Fotos
Archiv
Archiv (Kalender)
Deutsch > Blog > Kategorien

11.01.2010

Trace beenden - stoppen (Kill)

DECLARE @TraceID int

SET @TraceID = ?

EXEC sp_trace_setstatus @TraceID, 0

EXEC sp_trace_setstatus @TraceID, 2

Bereitgestellt um 09:22 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
Trace ermitteln

select * from ::fn_trace_getinfo(default)

Bereitgestellt um 09:21 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)

06.01.2010

Tabellen und Indexes je Data_Space inkl. Indexusage

SELECT p1.object_id

, object_name(p1.Object_id) Objectname

, p1.Partition_number

, ds.name as data_space_name

, p1.rows as rows_in_data_space

, p1.index_id

, idx.name index_name

, idx.type_desc index_type

, idx.is_unique index_is_unique

, iu.user_seeks

, iu.user_scans

, iu.user_updates

, iu.system_seeks

, iu.system_scans

, iu.system_updates

FROM sys.partitions p1

inner join sys.allocation_units au

      on p1.hobt_id = au.container_id

inner join sys.data_spaces ds

      on au.data_space_id = ds.data_space_id

inner join sys.indexes idx

      on idx.index_id = p1.index_id

      and idx.object_id = p1.object_id

inner join sys.dm_db_index_usage_stats iu

      on ((iu.index_id = idx.index_id) and (iu.object_id = idx.object_id))

where ds.name = 'PRIMARY'

order by p1.rows desc

Bereitgestellt um 07:33 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
Indexstatistiken - Details zur Verwendung

begin try 

      Select (dense_rank() over (order by s.name,t.name))%2 as l1

      ,       (dense_rank() over (order by s.name,t.name,i.name))%2 as l2

      ,           s.name as [schema_name]

      ,       t.name as [table_name]

      ,       i.name as [index_name]

      ,       i.type_desc

      ,       case when iop.object_id is NULL then '' else convert(varchar(20),iop.object_id) end as object_id

      ,       case when iop.index_id is NULL then '' else convert(varchar(20),iop.index_id) end as index_id

      ,       case when iop.partition_number is NULL then '' else convert(varchar(20),iop.partition_number) end as partition_number

      ,       case when iop.leaf_insert_count is NULL then '' else convert(varchar(20),iop.leaf_insert_count) end as leaf_inserts

      ,       case when iop.leaf_delete_count is NULL then '' else convert(varchar(20),iop.leaf_delete_count) end as leaf_deletes

      ,       case when iop.leaf_update_count is NULL then '' else convert(varchar(20),iop.leaf_update_count) end as leaf_updates

      ,       case when iop.leaf_ghost_count is NULL then '' else convert(varchar(20),iop.leaf_ghost_count) end as leaf_ghosts

      ,       case when iop.nonleaf_insert_count is NULL then '' else convert(varchar(20),iop.nonleaf_insert_count) end as nonleaf_inserts

      ,       case when iop.nonleaf_delete_count is NULL then '' else convert(varchar(20),iop.nonleaf_delete_count) end as nonleaf_deletes

      ,       case when iop.nonleaf_update_count is NULL then '' else convert(varchar(20),iop.nonleaf_update_count) end as nonleaf_updates

      ,       case when iop.leaf_allocation_count is NULL then '' else convert(varchar(20),iop.leaf_allocation_count) end leaf_allocations

      ,       case when iop.nonleaf_allocation_count is NULL then '' else convert(varchar(20),iop.nonleaf_allocation_count) end nonleaf_allocations

      ,       case when iop.leaf_page_merge_count is NULL then '' else convert(varchar(20),iop.leaf_page_merge_count) end leaf_page_merges

      ,       case when iop.nonleaf_page_merge_count is NULL then '' else convert(varchar(20),iop.nonleaf_page_merge_count) end nonleaf_page_merges

      ,       case when iop.range_scan_count is NULL then '' else convert(varchar(20),iop.range_scan_count) end as range_scan

      ,       case when iop.singleton_lookup_count is NULL then '' else convert(varchar(20),iop.singleton_lookup_count) end as singleton_lookups

      ,       case when iop.forwarded_fetch_count is NULL then '' else convert(varchar(20),iop.forwarded_fetch_count) end as forwarded_fetches

      ,       case when iop.lob_fetch_in_pages is NULL then '' else convert(varchar(20),iop.lob_fetch_in_pages) end as lob_fetches

      ,       case when iop.lob_fetch_in_bytes is NULL then '' else convert(varchar(20),iop.lob_fetch_in_bytes ) end as lob_bytes_fetched

      ,       case when iop.row_lock_count is NULL then '' else convert(varchar(20),iop.row_lock_count) end as row_locks

      ,       case when iop.row_lock_wait_count is NULL then '' else convert(varchar(20),iop.row_lock_wait_count) end as row_lock_waits

      ,       case when iop.row_lock_wait_in_ms is NULL then '' else convert(varchar(20),iop.row_lock_wait_in_ms) end as row_lock_wait_ms

      ,       case when iop.page_lock_count is NULL then '' else convert(varchar(20),iop.page_lock_count) end as page_locks

      ,       case when iop.page_lock_wait_count is NULL then '' else convert(varchar(20),iop.page_lock_wait_count) end as page_lock_waits

      ,       case when iop.page_lock_wait_in_ms is NULL then '' else convert(varchar(20),iop.page_lock_wait_in_ms) end as page_lock_wait_ms

      ,       case when iop.index_lock_promotion_attempt_count is NULL then '' else convert(varchar(20),iop.index_lock_promotion_attempt_count ) end as index_lock_promotion_attempts

      ,       case when iop.index_lock_promotion_count is NULL then '' else convert(varchar(20),iop.index_lock_promotion_count) end as index_lock_promotions

      ,       case when iop.page_latch_wait_count is NULL then '' else convert(varchar(20),iop.page_latch_wait_count) end as page_latch_waits

      ,       case when iop.page_latch_wait_in_ms is NULL then '' else convert(varchar(20),iop.page_latch_wait_in_ms) end as page_latch_wait_ms

      from sys.dm_db_index_operational_stats(db_id(),null,null,null) iop

      inner join sys.indexes i on ((iop.index_id = i.index_id) and (iop.object_id = i.object_id))

      inner join sys.tables t  on ( i.object_id = t.object_id )

      inner join sys.schemas s on ( t.schema_id = s.schema_id )

      where i.type <> 0

      order by s.name, t.name, i.name, iop.partition_number

end try

begin catch

      select

                  -100 as l1

      ,           0 as l2

      ,           N'' as [schema_name]

      ,       ERROR_SEVERITY() as table_name

      ,       ERROR_STATE() as index_name

      ,       ERROR_MESSAGE() as type_desc

      ,           ERROR_NUMBER()  as object_id

      ,           0 as index_id

      ,           0 as partition_number

      ,           0 as leaf_inserts

      ,           0 as leaf_deletes

      ,           0 as leaf_updates

      ,           0 as leaf_ghosts

      ,           0 as nonleaf_inserts

      ,           0 as nonleaf_deletes

      ,           0 as nonleaf_updates

      ,           0 as leaf_allocations

      ,           0 as leaf_page_merges

      ,           0 as nonleaf_page_merges

      ,           0 as range_scans

      ,           0 as singleton_lookups

      ,           0 as forwarded_fetches

      ,           0 as lob_fatches

      ,           0 as lob_bytes_fetches

      ,           0 as slob_fatches

      ,           0 as slob_bytes_fetched

      ,           0 as row_locks

      ,           0 as row_lock_waits

      ,           0 as row_lock_wait_ms

      ,           0 as page_locks

      ,           0 as page_lock_waits

      ,           0 as page_lock_wait_ms

      ,           0 as index_lock_promotion_attempts

      ,           0 as index_lock_promotions

      ,           0 as page_latch_waits

      ,           0 as page_latch_wait_ms

end catch

Bereitgestellt um 07:09 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
Indexstatistiken

begin try 

      Select

                  (dense_rank() over (order by s.name,t.name))%2 as l1

      ,       (dense_rank() over (order by s.name,t.name,i.name))%2 as l2

      ,           s.name as [schema_name]

      ,       t.name as [table_name]

      ,       i.name as [index_name]

      ,       i.type_desc

      ,       case when iu.object_id is NULL then '' else convert(varchar(20),iu.object_id) end as object_id

      ,       case when iu.index_id is NULL then '' else convert(varchar(20),iu.index_id) end as index_id

      ,       case when iu.user_seeks is NULL then '' else convert(varchar(20),iu.user_seeks) end as seek_user

      ,       case when iu.user_scans is NULL then '' else convert(varchar(20),iu.user_scans) end as scan_user

      ,       case when iu.user_updates is NULL then '' else convert(varchar(20),iu.user_updates) end as update_user

      ,       case when iu.last_user_seek is NULL then '' else iu.last_user_seek end as time_seek_user

      ,       case when iu.last_user_scan is NULL then '' else iu.last_user_scan end as time_scan_user

      ,       case when iu.last_user_lookup is NULL then '' else iu.last_user_lookup end as time_lookup_user

      ,       case when iu.last_user_update is NULL then '' else iu.last_user_update end as time_update_user

      ,       case when iu.system_seeks is NULL then '' else convert(varchar(20),iu.system_seeks) end as seek_system

      ,       case when iu.system_scans is NULL then '' else convert(varchar(20),iu.system_scans) end scan_system

      ,       case when iu.system_updates is NULL then '' else convert(varchar(20),iu.system_updates) end as update_system

      ,       case when iu.last_system_seek is NULL then '' else iu.last_system_seek end as time_seek_system

      ,       case when iu.last_system_scan is NULL then '' else iu.last_system_scan end as time_scan_system

      ,       case when iu.last_system_lookup is NULL then '' else iu.last_system_lookup end as time_lookup_system

      ,       case when iu.last_system_update is NULL then '' else iu.last_system_update end as time_update_system

      from sys.dm_db_index_usage_stats iu

      inner join sys.indexes i on  ((iu.index_id = i.index_id) and (iu.object_id = i.object_id))

      inner join sys.tables t on ( i.object_id = t.object_id )

      inner join sys.schemas s on (s.schema_id = t.schema_id)

      where iu.database_id = db_id() and i.type <> 0

      order by s.name,t.name,i.name

end try

begin catch

      select

                  -100 as l1

      ,           0 as l2

      ,       ERROR_SEVERITY() as table_name

      ,       ERROR_STATE() as index_name

      ,       ERROR_MESSAGE() as type_desc

      ,           ERROR_NUMBER() as object_id

      ,           0 as index_id

      ,           0 as seek_user

      ,           0 as scan_user

      ,           0 as update_user

      ,           0 as time_seek_user

      ,           0 as time_scan_user

      ,           0 as time_lookup_user

      ,           0 as time_update_user

      ,           0 as seek_system

      ,           0 as scan_system

      ,           0 as update_system

      ,           0 as time_seek_system

      ,           0 as time_scan_system

      ,           0 as time_lookup_system

      ,           0 as time_update_system

end catch

Bereitgestellt um 07:06 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)

14.12.2009

Tabellen und Indexes je Data_Space

SELECT p1.object_id

, object_name(p1.Object_id) Objectname

, p1.Partition_number

, ds.name as data_space_name

, p1.rows as rows_in_data_space

, p1.index_id

, idx.name index_name

, idx.type_desc index_type

, idx.is_unique index_is_unique

FROM sys.partitions p1

inner join sys.allocation_units au

      on p1.hobt_id = au.container_id

inner join sys.data_spaces ds

      on au.data_space_id = ds.data_space_id

inner join sys.indexes idx

      on idx.index_id = p1.index_id

      and idx.object_id = p1.object_id

order by ds.name, object_name(p1.Object_id), idx.name

 

Bereitgestellt um 14:22 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)

26.07.2009

Datenbank Verschlüsselung & Entschlüsselung mit Zertifikat und symetrischen Schlüssel

USE [MyDatabase]

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My Secret Password';

GO

BACKUP MASTER KEY TO FILE = 'C:\MyDatabase_Master.Key' ENCRYPTION BY PASSWORD = 'My Secret Password' ;

GO

--RESTORE MASTER KEY FROM FILE = 'C:\MyDatabase_Master.Key' DECRYPTION BY PASSWORD = 'My Secret Password' ENCRYPTION BY PASSWORD = 'My Secret Password';

--GO

BACKUP SERVICE MASTER KEY TO FILE = 'C:\SQL_Service_Master.Key' ENCRYPTION BY PASSWORD = 'My Secret Password';

GO

--RESTORE SERVICE MASTER KEY FROM FILE = 'C:\SQL_Service_Master.Key' DECRYPTION BY PASSWORD = 'My Secret Password' ;

--GO

 

IF EXISTS (select * from sys.certificates where name = 'MyDatabase-Cert')

DROP CERTIFICATE [MyDatabase-Cert] ;

GO

CREATE CERTIFICATE [MyDatabase-Cert]

ENCRYPTION BY PASSWORD = 'My Secret Password'

WITH SUBJECT = 'MyDatabase Certificate'

, START_DATE = '20060101'

, EXPIRY_DATE = '99991231'

;

GO

BACKUP CERTIFICATE [MyDatabase-Cert]

TO FILE = 'C:\MyDatabase-Cert.CER'

WITH PRIVATE KEY ( FILE = 'C:\MyDatabase-Cert.PVK',

ENCRYPTION BY PASSWORD = 'My Secret Password',

DECRYPTION BY PASSWORD = 'My Secret Password'

)

IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'MyDatabase-Key')

DROP SYMMETRIC KEY [MyDatabase-Key];

GO

CREATE SYMMETRIC KEY [MyDatabase-Key] WITH

KEY_SOURCE = 'My Secret Shared Key!',

ALGORITHM = TRIPLE_DES, --TRIPLE_DES --AES_256 --AES_128

IDENTITY_VALUE = 'My Secret Shared Key!'

ENCRYPTION BY CERTIFICATE [MyDatabase-Cert];

GO

 

 

 

/*

--#####################################################

--############ Beispiel 1 mit Variablen ###############

--#####################################################

DECLARE @MeinText as varchar(8000)

DECLARE @Blob_enc as varbinary(8000)

DECLARE @Blob_dec varchar(8000);

Set @MeinText = 'Mein geheimer Text'

--Symetrischen Schlüssel öffnen

OPEN SYMMETRIC KEY [MyDatabase-Key]

DECRYPTION BY CERTIFICATE MyDatabase-Cert

WITH PASSWORD = 'My Secret Password';

--Meine geheimen Text verschlüsseln und in @Blob_enc speichern

SELECT @Blob_enc = encryptbykey(key_guid('MyDatabase-Key'), @MeinText )

--Ausgabe zur Kontrolle ob die verschlüsselung in @Blob_enc funktioniert hat

select @MeinText as Geheimer_Text, @Blob_enc as Blob_encypted, @Blob_dec as Blob_decypted

--Meinen geheimen Text wieder entschlüsseln und in @Blob_dec speichern

SELECT @Blob_dec = CONVERT(varchar(8000), decryptbykey(@Blob_enc));

--Ausgabe zur Kontrolle ob die entschlüsselung in @Blob_dev funktioniert hat

select @MeinText as Geheimer_Text, @Blob_enc as Blob_encypted, @Blob_dec as Blob_decypted

--Symetrischen Schlüssel schliesen

CLOSE SYMMETRIC KEY [MyDatabase-Key];

GO

 

--###########################################################

--############ Beispiel 2 mit Tabellenspalten ###############

--###########################################################

--Neue Spalte zum verschlüsselten Speichern von Daten an Tabelle anfügen

IF NOT EXISTS (select * from sys.columns where name = 'Encrypted_dbname' and object_name(object_id) = 'databases')

ALTER TABLE DMVconfig.databases

ADD Encrypted_dbname varbinary(128);

GO

--Symetrischen Schlüssel öffnen

OPEN SYMMETRIC KEY [MyDatabase-Key]

DECRYPTION BY CERTIFICATE MyDatabase-Cert

WITH PASSWORD = 'My Secret Password';

--Ausgabe zur Kontrolle

select * from DMVconfig.databases

--Daten aus dem unverschlüsselten Feld 'dbname' in 'Encrypted_dbname' speichern

UPDATE DMVconfig.databases

SET Encrypted_dbname = EncryptByKey(Key_GUID('MyDatabase-Key'), dbname);

--Ausgabe zur Kontrolle

select * from DMVconfig.databases

--Ausgabe zur Kontrolle mit entschlüsselten Feld

SELECT dbname, Encrypted_dbname,

CONVERT(nvarchar, DecryptByKey(Encrypted_dbname))

AS 'Decrypted DBName'

FROM DMVconfig.databases;

--Symetrischen Schlüssel schliesen

CLOSE SYMMETRIC KEY [MyDatabase-Key];

GO

*/

Bereitgestellt um 10:33 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
Super easy SQL Server 2005 Database Schema change auditing
Source: Super easy SQL Server 2005 Database Schema change auditing

CREATE TABLE [Audit].[Objects](

[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventData] [xml] NULL,

PRIMARY KEY CLUSTERED
(
   [EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Then, the trigger:

CREATE TRIGGER [Trig_AuditObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO Audit.Objects(EventData)
SELECT EVENTDATA()
GO
ENABLE TRIGGER [Trig_AuditObjects] ON DATABASE

That's it.. now I get a nice neat little xml entry in my table every time a DDL database level event happens, like so:

<EVENT_INSTANCE>
    <EventType>ALTER_TABLE</EventType>
    <PostTime>2007-06-03T20:12:05.813</PostTime>
    <SPID>55</SPID>
    <ServerName>MYSERVER</ServerName>
    <LoginName>MYDOMAIN\myusername</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>Sales</DatabaseName>
    <SchemaName>dbo</SchemaName>
    <ObjectName>Products</ObjectName>
    <ObjectType>TABLE</ObjectType>
    <TSQLCommand>
        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"      QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
        <CommandText>ALTER TABLE dbo.Products
    DROP COLUMN testremove
       </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

The EVENTDATA() function is provided by SQL Server inside a DDL trigger and provides all the data you see above as an xml document.
Bereitgestellt um 10:33 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
logischen Dateinamen umbenennen
ALTER DATABASE <Datenbanknam>  MODIFY FILE  ( NAME = <current_logical_name>, NEWNAME = <new_logical_name>)
Bereitgestellt um 10:32 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
Registry lesen aus T-SQL
 

declare @SomeValue nvarchar(max)

EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances',
@value = @SomeValue output

select @SomeValue

Bereitgestellt um 10:28 von Andy Löwen | Kategorie: SQL Server | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
1 - 10 Weiter
certlogo
Small Business Specialist
Impressum  | Kontaktieren Sie uns  |