SQL Server and SQL Server Agent Service Account(Startup Account) and Permissions

SQL Server and SQL Server Agent service account(Startup Account) and Permissions SQL Server與SQL Server Agent 服務帳戶(啟動帳戶)與權限 可設定的種類 1.Domain User Account (使用一個網域使用者帳戶) 2.Local User Account (使用一個本機使用者帳戶) 3.Local Service Account (NT AUTHORITY\LOCAL SERVICE) 本機服務帳戶 4.Network Service Account (NT AUTHORITY\NETWORK SERVICE) 本機網路帳戶 5.Local System Account (NT AUTHORITY\SYSTEM) 本機系統帳戶 6.Virtual Accounts (start from Windows 7 and Windows Server 2008…

0

Query SQL Server backup history and restore history records

SQL Server備份還原紀錄 1.使用以下TSQL語法查詢備份檔紀錄 SELECT bs.backup_set_id, bs.database_name, bs.backup_start_date, bs.backup_finish_date, CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ‘ ‘ + ‘MB’ AS [Size], CAST(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘Seconds’ [TimeTaken], CASE bs.[type] WHEN ‘D’ THEN ‘Full Backup’ WHEN ‘I’ THEN ‘Differential Backup’ WHEN ‘L’ THEN ‘TLog Backup’ WHEN ‘F’ THEN ‘File or filegroup’ WHEN…

4

指定本地快照目錄來改善初始化時間 Apply snapshot from Alternate folder to improve performance when initialize a Subscription in Transactional Replication

Apply snapshot from Alternate folder to improve performance when initialize a Subscription in Transactional Replication 訂閱的初始化有兩個過程: 1.產生snapshot file,這個是snapshot agent所負責的工作。 Type of Replication Common Snapshot Files Snapshot Replication or Transactional Replication schema (.sch); data (.bcp); constraints and indexes (.dri); constraints (.idx); triggers (.trg):for updating Subscribers only; compressed snapshot files (.cab). Merge Replication schema (.sch); data (.bcp);…

0

Quick to build a testing web site to access SQL Server

Classic ASPHow to access SQL Server in Active Server Pageshttp://support.microsoft.com/kb/169377/en-us ASP.NET 1.1逐步解說:在 Web Form 網頁中顯示資料Visual Studio .NET 2003 http://msdn.microsoft.com/zh-tw/library/cc438239(v=vs.71).aspx Walkthrough: Basic Data Access in Web Pages  Visual Studio 2005http://msdn.microsoft.com/en-us/library/tw738475(v=vs.80).aspx Walkthrough: Basic Data Access in Web PagesVisual Studio 2008http://msdn.microsoft.com/en-us/library/tw738475(v=vs.90).aspx Walkthrough: Basic Data Access in Web Pages.NET Framework 4http://msdn.microsoft.com/en-us/library/tw738475(v=vs.100).aspx

0

How to Rename SQL Server Cluster Virtual Server Name(Network name)

How to Rename SQL Server Cluster Virtual Server Name(Network name) Rename a SQL Server Failover Cluster Instance http://msdn.microsoft.com/en-us/library/ms178083.aspx The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). Although you can change the name of the virtual server, you cannot change…

0

SQL Server 2008 與SQL Server 2005 Sample Databases範例資料庫下載與安裝

SQL Server 2008 與SQL Server 2005 Sample Databases範例資料庫下載與安裝   SQL Server 2008安裝光碟已不再含有範例資料庫Sample Database,需自行到CodePlex網站的SQL Server Database Product Samples網頁下載http://msftdbprodsamples.codeplex.com/     [SQL Server 2008]         [SQL Server 2005]     SQL Server 2008 Sample Database 安裝 Next       勾選同意       Next       選擇一個要安裝在哪一個本機SQL Server 2008 Instance   Read…

0

立即中斷所有SQL Server連線

立即中斷所有SQL Server連線   1.針對單一資料庫的連線 範例:將Northwind資料庫設定為只能有一個連線,並中斷其他連線–中斷Northwind資料庫的所有連線USE masterGOALTER DATABASE [Northwind]SET SINGLE_USERWITH ROLLBACK IMMEDIATE;GO –復原為一般多人連線USE masterGOALTER DATABASE [Northwind]SET MULTI_USERWITH ROLLBACK IMMEDIATE;GO 2.針對所有SQL Server連線(1)查出現有連線,一一刪除EXEC sp_who KILL spid號碼 (2)使用T-SQL 的SURSOR取出所有非系統spid,一次全部刪除 USE masterGODECLARE @spid_number intDECLARE @sql_text varchar(100) DECLARE CUR CURSOR FORSELECT [spid] FROM master..sysprocessesWHERE [spid] > 50 and [spid] <> @@spid OPEN CURFETCH CUR INTO @spid_number WHILE (@@FETCH_STATUS=0)BEGIN SET @sql_text =…

0

SQLDiag工具程式的使用

SQLDiag工具程式的使用   前言:SQLDiag是SQL Server內建的診斷收集共用程式, [SQL Server 2005]預設位置:C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLDiag.exe你可以在任何路徑下執行SQLDiag因為此路徑已被加到path系統變數,執行sqldiag -?或sqldiag /?即可顯示參數說明 SQL 2005的sqldiag搭配參數請使用正斜線”/” SQLdiag 可以收集下列類型的診斷資訊:Windows 效能記錄Windows 事件記錄檔SQL Server Profiler 追蹤SQL Server 封鎖資訊SQL Server 組態資訊 http://technet.microsoft.com/zh-tw/library/ms162833.aspx 一般使用:例如: sqldiag /O C:\temp\sqldiag(/O 指定輸出檔案的目錄)最下方會出現2008/07/01 00:34:42.82 SQLDIAG Collection started. Press Ctrl+C to stop.表示正在收集中在此時按下Ctrl+C即可停止收集 收集的資訊會儲存在指定的C:\temp\sqldiag\目錄下,其中數個log_xx.trc檔是從C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\路徑下所複製的 進階收集: 修改並指定xml檔,來收集Performance Counter的資訊 [SQL Server 2000]預設位置:C:\Program Files\Microsoft SQL Server\MSSQL\Binn\SQLDiag.exe切換目錄到此位置後執行sqldiag -?或sqldiag /?即可顯示參數說明SQL 2000的sqldiag搭配參數執行要使用”-“例如:…

0

Listing space used information in each database files

Listing space used information in each database files — SQL Server 2000請將 sys.databases改成sysdatabases– 第欄是資料使用大小單位是KB CREATE TABLE #db_space([DBname] NVARCHAR(50),[Fileid] NVARCHAR(10),[Filegroup] NVARCHAR(10),[TotalExtents] int,[UsedExtents] int,[Name] NVARCHAR(50),[FileName] NVARCHAR(300),);GO DECLARE @name sysnameDECLARE cur cursor for SELECT [name] FROM sys.databases WHERE [name] not in (‘master’,’msdb’,’tempdb’,’model’)OPEN curFETCH cur INTO @nameWHILE @@fetch_status = 0BEGIN–Print @nameBEGIN TRANINSERT INTO #db_space([Fileid],[Filegroup],[TotalExtents],[UsedExtents],[Name],[FileName])EXEC(‘USE ‘+@name+’ ;DBCC SHOWFILESTATS;’);COMMIT TRAN BEGIN…

0