sql server tables unused space / unbenutzer Platz in Sql Server Tabellen, filesize / filegröße

--sql server tables unused space / unbenutzer Platz in Sql Server Tabellen: SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY UnusedSpaceKB desc --filegröße / filesize SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files where right(physical_name,3)='mdf' order by size desc

creating Table on secondary filegroup on sql server / eine Tabelle in der zweiten Filegroup anlegen am Sql Server

angenommen die Datenbank
asuming the database has a filegroup named "secondary":

create table tSecondary
(
    Id integer
)
on 'Secondary'

sql server datenbank offline nehmen / take database offline / verbindungen lsöchen / kill connections

use master
go
ALTER DATABASE GBOv2_Reports_Test
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO


alle Verbindungen zu einer db killen / delete all Connections to a db
declare @kill varchar(8000) = '';
select @kill=@kill+'kill '+convert(varchar(5),spid)+';'
    from master..sysprocesses
where dbid=db_id('GBOv2_Reports_Test');
exec (@kill);


-- Show all processes of SQL Server / alle processe (Verbindungen) des SQL Servers anzeigen
select * from master..sysprocesses

In welcher Tabelle ist die Spalte namens ? Where is column (in which table)

oracle:
select * from user_tab_columns where table_name like 'T_PDB%' and  Column_NAME like '%PRO%';

sql-server:
select c.name,t.name from sys.columns c join sys.tables t on c.object_id=t.object_id where c.name like 'Bem%'

sql server health (gesundheits) check

1) Arbeitsspeicher: maximal Grenze (physikalischer gesamtspeicher-2GB fürs betreibssystem) sonst wird der sqlserver prozess eventuell ausgepaged (ab 2005 meldung im log wenn ausgepaged)

2) Autogrow: keine zu kleinen Einheiten, 32 oder 64MB , besser: eine große Datei, die nicht fragmentiert ist, also vor dem Anlegen checken ob ein so großer zusammenhängender Platz auf der HD verfügbar, ebenso bei vergrößerung mit großen Zusammenhängenden Dateien vergrößern; mit dbcc loginfo logfile  chekcen: sollte 50-100 Virtuelle Logfiles enthalten, bei autogrow 64MB werden 4 virtuelle Logfiles erzeugt, bei mehr 8

3) Shrink: niemals Datenfiles Shrinken - fragmentiert Indizies, und rebuild braucht dann wieder den Speicher; beim Logfile Shrinken auf die LogBackupChain achten

---------------------

1) set sql server max. ram to max physical ram-2gb for OS

2) Autogrow: grow by mind 64MB, better one big file not fragmented, every Autogrow creates a new fragment; look at logfile with dbcc loginfo to check count of virtual logfiles should be 50-100

3) Shrink: never shrink datafiles because index are fragmented, watch log file backup chain when shrinking logfile

sql server: connect users to logins / User mit Logins verbinden

sql server hat ein 2 stufiges SIcherheitsmodell: Der SqlServer selbst verwaltet Rechte für logins, jede Datenbank am SqlServer hat dann db users, die mit den logins verbunden sind, oder auch nicht. z.b. nach einem Restore. UNverbundene (verwaiste) Users können wie folgt gelistet werden:
--This will lists the orphaned users:
EXEC sp_change_users_login 'Report'

Im ssms können Logins Datenbanken zugeorndet werden, dann wird in der Datenbank ein entsprechender User mit dem selben Namen des logins erstellt. Wenn Login und Datenbankbenutzer bereits vorhanden sind können sie wie folgt verbunden werden:
--If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Wenn noch kein login existiert, kann dieses über SSMS oder wie folgt erstellt werden:
--If you want to create a new login id and password for this user, fix it by doing:
--creates login EXEC sp_change_users_login 'Auto_Fix', 'dbUser',NULL,'pwd'



for searching:
EXEC SP CHANGE USER LOGIN
EXEC SP CHANGE USERS LOGIN

Berechtigungen für SQL Server Agent / rights for Sql Server Agent

Um den Sql Server Agent im SSMS eingeblendet zu bekommen, muß der User in der MSDb Datenbank zur Rolle SqlServerAgentOperater hinzugefügt werden.
For seing SqlServerAgent in SSMS a user has to be in role SqlServerAgentUser of msdb database.

SqlServer2008 - SqlCompact MergeReplikation

Um eine Db eines SQL Servers 2008/2005 zu replizieren ist es wichtig sich mit dem Rechnernamen, nicht mit der IP anzumelden, sonst glaub ssms dass die Replikation nicht installeirt ist.
Anschließend:
1) neue Publikation unter Server
2) neue Subscription

wichtig: bei den Properties der Publikation das Verfallsdatum (expire) von Subscription richtig konfigurieren, sonst verfallen diese bei seltener  Merge Replikation (Standard 14 Tage)

sql server dynamic managment objects (views) categories / Kategorien

dm_exec_
dm_os_
dm_tran_
dm_io_
dm_db_

sql profiler trace of table adapter (typed dataset) vs2008, sql server 2008r2

used test table definition on SqlServer2008r2 / verwendete Test Tabellen Definition auf sql2008r2:

USE [test]
GO

/****** Object:  Table [dbo].[tTest]    Script Date: 10/04/2013 09:38:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tTest](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL
) ON [PRIMARY]

GO

created a table adpater by drag and drop the table from server explorer into an empty dataset / erzeugete mittels drag and Drop vom Server Explorer in ein leeres Dataset einen TabellenAdapter

created a winform and drag and drop the table adapter into it => creating a datagridview
ein windows form erzeugt und den Table Adapter hineingezogen => ein Datagridview erzeugt

 1) insert first row by entering name "test" and click save=>
exec sp_executesql N'INSERT INTO [dbo].[tTest] ([name]) VALUES (@name)',N'@name nvarchar(4)',@name=N'test'

2)insert second row "Test2"  and click save=>
 exec sp_executesql N'INSERT INTO [dbo].[tTest] ([name]) VALUES (@name)',N'@name nvarchar(5)',@name=N'test2'

--added primary key to table to get update command & delete command into Table Adapter
Primärschlüssel hinzugefügt um Update und Delete Command beim Table Adapter hinzuzufügen

3) update first row from "Test" to "Test1":
exec sp_executesql N'UPDATE [dbo].[tTest] SET [name] = @name WHERE (([id] = @Original_id) AND ((@IsNull_name = 1 AND [name] IS NULL) OR ([name] = @Original_name)));
SELECT id, name FROM tTest WHERE (id = @id)',N'@name nvarchar(5),@Original_id int,@IsNull_name int,@Original_name nvarchar(4),@id int',@name=N'test1',@Original_id=1,@IsNull_name=0,@Original_name=N'test',@id=1


4) insert row 3:
exec sp_executesql N'INSERT INTO [dbo].[tTest] ([name]) VALUES (@name);
SELECT id, name FROM tTest WHERE (id = SCOPE_IDENTITY())',N'@name nvarchar(5)',@name=N'test3'

script for copy a sql server database / Skript zum Kopieren einer Sql Server Datenbank

quell Datenbank Name=x1, Ziel Datenbank Name=x2
source database name =x1, dest database name=x2

select 'backup'  --echo wath I'm doing
go backup database x1 to disk ='D:\BAK\x1.bak' --full backup
go
USE master
go
select 'DropCon'
go
ALTER DATABASE x2 SET OFFLINE WITH ROLLBACK IMMEDIATE --cut all existing connections
go
ALTER DATABASE mydb SET ONLINE WITH ROLLBACK IMMEDIATE --connect again
go
DROP DATABASE mydb --drop to remove mdf and ldf file to create same named files with 
go
select 'restore'  --echo wath I'm doing
go

--get filelist of logical files in restore file, maybe they differ fropm x1 and x1_log
RESTORE FILELISTONLY from disk = 'd:\temp\x.bak'



restore database x2  from disk ='D:\BAK\x1.bak' with file=1,
move 'x1' to 'D:\sqldata\x2_Data.MDF',
move 'x1_Log'  to 'D:\sqldata\x2_Log.LDF'
go


use osql on sql server 2000 or sqlcmd 2005 and later to execute from commandline:
osql:


d:
cd D:\sqldata

osql -U username -P password -i script.sql

stored procedure to create command line for merge pull subbscription - zum erzeugen der Command Line für Merge Pull Subscription

create PROCEDURE mergeCom
(
@SubscriptionDB as varchar(max)='subsdb'
,@Publication as varchar(max) ='pub'
)
AS
BEGIN
--exec mergecom
-- Declare the variables.
declare @Publisher as varchar(max);
declare @Subscriber as varchar(max);
declare @PublicationDB as varchar(max);

select @Publisher=s.publisher,@subscriber=s.subscriber,@PublicationDB=s.publisher_db
from distribution.dbo.MSmerge_subscriptions s
join distribution.dbo.mspublications  p on s.publication_id=p.publication_id
where s.subscriber_db=@SubscriptionDB and p.publication=@Publication

--Start the Merge Agent with concurrent upload and download processes.
-- The following command must be supplied without line breaks.
--2008 C:\Program Files (x86)\Microsoft SQL Server\100\COM>
--'C:\Program Files\Microsoft SQL Server\110\COM\
declare @com as varchar(max)='REPLMERG.EXE -Publication '+@Publication
set @com=@com +' -Publisher '+@Publisher+' -Subscriber '+@Subscriber +' -Distributor '+@Publisher
set @com=@com +' -PublisherDB '+@PublicationDB+' -SubscriberDB '+@SubscriptionDB
---DistributorSecurityMode 1  -PublisherSecurityMode 1 -- use for windows authentication, default=0=sql server auth
set @com=@com +' -OutputVerboseLevel 2  -SubscriberSecurityModc5e 1  -SubscriptionType 1  -Validate 3  -ParallelUploadDownload 1'
set @com=@com +' -DistributorLogin sa -DistributorPassword Pa$$w0rd -PublisherLogin sa -PublisherPassword Pa$$w0rd'
Print @com
END

simple transaction rollback example - einfaches transaktions rollback beispiel

select * from tMergeUni
begin transaction
select * from tMergeUni
update tMergeUni set Name='free2' where Uni='5DADE228-7EAC-4C4D-A6FF-A078733DA68B'
select * from tMergeUni
rollback
select * from tMergeUni

Sql Server Through /durch Windows Friewall

If you have default port 1433 for SQL Server, you have to create an inbound and outbound Rule to enable remote connections to it. Times ago it was enough to create an inbound role.

Um den default Port 1433 des Sql Servers nach außen zu öffnen, muß man einen inbound und eine outbound Regel in der WIndows Firewall anlegen - früher genügte eine Inbound Rule.

Sql Server Agent Jobs

Query to view Sql Server Agent Jobs - Abfrage um Sql Server Agent Jobs anzuzeigen:

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY JobCreatedOn desc

Ms Sql Server: Merge Replikation / Replication

1) Publikation
Beim Hinzufügen von Artikeln muß nacher ein neuer Snapshot gemacht werden, damit diese Repliziert werden können

2) Subscription
der Subscriber darf keine höhere sql server Version als der Verleger haben ! - subscriber version has to be lower than publicator
erzeugt einen Sql Agent job Namens
PublikationsServer - Publikationsdatenbank - SubscriberServer - Nr
wobei Nr hochgezählt wird.
creates a Sql Server Agent Job with this name:
publicationServer - PublicationDatabase - SubscriberServer - Nr
Nr is counted

2a) Push Subscription

2b) Pull Subscription
Merge Agent:
Bei Sql Server 2008: C:\Program Files (x86)\Microsoft SQL Server\100\COM\replmerger.exe

Allgemeines zur Sql Server Replikation

Die Replikations Assistenten prüfen (zumindest bei Sql 2008) nciht, ob die eingegeben Account Informationen passen, es kommt erst während der Ausführung des Scripts oder der Replikation selbst zu einem Fehler.

Transactionale Replikation:

Zunächst wird mit einem SQLAgentJob ein Snapshot angelegt, und zwar macht das der Raplikationsagent, also das Windows Konto das beim Einrichten des Publishers angegeben wurde. wichtig: dieser Agent braucht schreibrechte auf der Freigabe, die zum Speichern des Snapshots verwendet wird - dort wird ein unc Directory angelegt und darin wieder eines mit dem Publikationsnamen, darin werden die Files gepeichert. INsgesamt werden 3 SqlAgent Jobs verwendet:
pubserver-db-Publication-Nr snapshot.exe zum erstellen des SNapshots
pubserver-db-Nr logreader.exe (liest das Transactionlog der Publikations Datenbank)
pubserver-db-db-subsrv-db-uniq distrib.exe für die subscribtion

Der Replikationsmonitor zeigt den Status aller Publikationen auf einen Blick und auch gleich die History der betroffenen SqlAgentJobs.

stnadard befehl des transaction log reader jobs:
logread -Publisher [KKMCON01] -PublisherDB [TASE] -Distributor [SQLSRV] -DistributorSecurityMode 1 -Continuous

logread.exe ist z.b. in c:\Program Files\Microsoft SQL Server\100\COM

Berechtigungen: siehe http://technet.microsoft.com/de-de/library/ms151868(v=sql.105).aspx

Agent	Berechtigungen
Momentaufnahme-Agent	Das Windows-Konto, unter dem der Agent ausgeführt wird, wird verwendet, wenn er Verbindungen mit dem Verteiler herstellt. Für dieses Konto ist Folgendes erforderlich: Es muss mindestens Mitglied der festen Datenbankrolle db_owner in der Verteilungsdatenbank sein. Es muss über Lese-, Schreib und Änderungsberechtigungen für die Momentaufnahmefreigabe verfügen. Das zum Herstellen der Verbindung mit dem Verleger verwendete Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Veröffentlichungsdatenbank sein.
Protokolllese-Agent	Das Windows-Konto, unter dem der Agent ausgeführt wird, wird verwendet, wenn er Verbindungen mit dem Verteiler herstellt. Dieses Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Verteilungsdatenbank sein. Das zum Herstellen der Verbindung mit dem Verleger verwendete Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Veröffentlichungsdatenbank sein.
Verteilungs-Agent für Pushabonnements	Das Windows-Konto, unter dem der Agent ausgeführt wird, wird verwendet, wenn er Verbindungen mit dem Verteiler herstellt. Für dieses Konto ist Folgendes erforderlich: Es muss mindestens Mitglied der festen Datenbankrolle db_owner in der Verteilungsdatenbank sein. Es muss Mitglied der Veröffentlichungszugriffsliste sein. Es muss über Leseberechtigungen für die Momentaufnahmefreigabe verfügen. Es muss über die Leseberechtigung für das Installationsverzeichnis des OLE DB-Anbieters für den Abonnenten verfügen, wenn das Abonnement für einen Nicht-SQL Server-Abonnenten vorgesehen ist. Das zum Herstellen der Verbindung mit dem Abonnenten verwendete Konto muss zumindest ein Mitglied der festen Datenbankrolle db_owner in der Abonnementdatenbank sein oder über vergleichbare Berechtigungen verfügen, wenn das Abonnement für einen Nicht-SQL Server-Abonnenten vorgesehen ist.
Verteilungs-Agent für Pullabonnements	Das Windows-Konto, unter dem der Agent ausgeführt wird, wird verwendet, wenn er Verbindungen mit dem Abonnenten herstellt. Dieses Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Abonnementdatenbank sein. Für das zum Herstellen der Verbindung mit dem Verteiler verwendete Konto ist Folgendes erforderlich: Es muss Mitglied der Veröffentlichungszugriffsliste sein. Es muss über Leseberechtigungen für die Momentaufnahmefreigabe verfügen.
Merge-Agent für Pushabonnements	Das Windows-Konto, unter dem der Agent ausgeführt wird, wird verwendet, wenn er Verbindungen mit dem Verleger und dem Verteiler herstellt. Für dieses Konto ist Folgendes erforderlich: Es muss mindestens Mitglied der festen Datenbankrolle db_owner in der Verteilungsdatenbank sein. Es muss Mitglied der Veröffentlichungszugriffsliste sein. Die Anmeldung muss mit einem Benutzer in der Veröffentlichungsdatenbank verknüpft sein. Es muss über Leseberechtigungen für die Momentaufnahmefreigabe verfügen. Das zum Herstellen der Verbindung mit dem Abonnenten verwendete Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Abonnementdatenbank sein.
Merge-Agent für Pullabonnements	Das Windows-Konto, unter dem der Agent ausgeführt wird, wird verwendet, wenn er Verbindungen mit dem Abonnenten herstellt. Dieses Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Abonnementdatenbank sein. Für das zum Herstellen der Verbindung mit dem Verleger und Verteiler verwendete Konto ist Folgendes erforderlich: Es muss Mitglied der Veröffentlichungszugriffsliste sein. Die Anmeldung muss mit einem Benutzer in der Veröffentlichungsdatenbank verknüpft sein. Die Anmeldung muss mit einem Benutzer in der Verteilungsdatenbank verknüpft sein. Der Benutzer kann der Guest-Benutzer sein. Es muss über Leseberechtigungen für die Momentaufnahmefreigabe verfügen.
Warteschlangenlese-Agent	Das Windows-Konto, unter dem der Agent ausgeführt wird, wird verwendet, wenn er Verbindungen mit dem Verteiler herstellt. Dieses Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Verteilungsdatenbank sein. Das zum Herstellen der Verbindung mit dem Verleger verwendete Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Veröffentlichungsdatenbank sein. Das zum Herstellen der Verbindung mit dem Abonnenten verwendete Konto muss zumindest Mitglied der festen Datenbankrolle db_owner in der Abonnementdatenbank sein.

function to get date from parts / Funktion um Datum aus Jahr/Monat/Tag zu erhalten in ms sql server 2008 and 2005

-- Description: like DateFromParts in sql server 2012
-- =============================================
ALTER FUNCTION [dbo].[MyDateFromParts]
(
@year int,
@month int,
@day int
)
RETURNS Datetime --nvarchar(20)--
AS
BEGIN
-- Declare the return variable here
declare @dateString as nvarchar(20) = convert(nvarchar(4),@year)+ dbo.AddLeading0(@month) +dbo.AddLeading0(@day)--dbo.AddLeading0(@year)--+
DECLARE @ret as DateTime=Convert(datetime, @dateString)

RETURN @ret

END

--depends on

-- Description: adds leading 0 if x in 0-9
-- =============================================
ALTER FUNCTION [dbo].[AddLeading0]
(
@x int
)
RETURNS Nvarchar(2)
AS
BEGIN
-- Declare the return variable here
DECLARE @ret as nvarchar(2) = convert(nvarchar(2),@x)
if (2>len(@ret)) set @ret='0'+@ret
RETURN @ret

END

Sql Profiler: Tracefilesize

Die tracefilegröße hängt von den getracten spalten ab - je mehr desto größer, z.b. bei sql batch completed beträgt die tracefilegröße in byte:

Zeilen	2cols	allcols
1	1148	1618
10	1715	5071
bytes/zeile	56,7	345,3

tracefilesize depends on count of traced columns - more columns, bigger file, filezize in bytes see above

sql reporting services slow startup

Der erste Aufruf von Reporting Services ist meist sehr langsam, weil die Seite inwzischen recyceld wurde. Man kann das Recycle Intervall verlängern, sodass bei wenig frequentierten Berichtsservern trotzdem ein schneller Respond erfolgt: In C:\Programme\Microsoft SQL Server\MSRS10.CRM\Reporting Services\ReportServer\rsreportserver.config 72000 von 720 erhöhen