茫茫網海中的冷日
         
茫茫網海中的冷日
發生過的事，不可能遺忘，只是想不起來而已！
 恭喜您是本站第 1668529 位訪客!  登入  | 註冊
主選單

Google 自訂搜尋

Goole 廣告

隨機相片
IMG_00018.jpg

授權條款

使用者登入
使用者名稱:

密碼:


忘了密碼?

現在就註冊!

DB研討會 : [教學]MSSQL 2016 的 Transaction Log 檔（ldf）爆炸（或爛掉了）怎麼辦！？

討論區主頁 - 主題一覽 > DB研討會 > [教學]MSSQL 2016 的 Transaction Log 檔（ldf）爆炸（或爛掉了）怎麼辦！？
討論串 | 最新的先
前一個主題 | 下一個主題 | 頁尾
主題一覽的上方
發表者 討論內容
冷日
(冷日)		 發表時間：2016/11/12 7:15
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[教學]MSSQL 2016 的 Transaction Log 檔（ldf）爆炸（或爛掉了）怎麼辦！？
MSSQL 2016 的 Transaction Log 檔（ldf）爆炸（或爛掉了）怎麼辦！？

1.請先確認你的 mdf 還完整！（冷日作法是馬上 Shutdown SQL Server 把 mdf 檔複製一份出來

2.然後回去對你的 SQL Server 進行修復！步驟如下：
I.進入 Single Mode
Using SQL Server Management Studio
To set a database to single-user mode
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Right-click the database to change, and then click Properties.
In the Database Properties dialog box, click the Options page.
From the Restrict Access option, select Single.
If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.
Using Transact-SQL
To set a database to single-user mode
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. This example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the AdventureWorks2012 database to READ_ONLY and returns access to the database to all users.The termination option WITH ROLLBACK IMMEDIATE is specified in the first ALTER DATABASE statement. This will cause all incomplete transactions to be rolled back and any other connections to the AdventureWorks2012 database to be immediately disconnected.
USE master;
GO
ALTER DATABASE {yourDataBaseName}
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

II.修復你的 ldf 檔
EXEC(‘ALTER DATABASE [{yourDataBaseName}] SET EMERGENCY’);
DBCC checkdb ({yourDataBaseName}, repair_allow_data_loss);

III.恢復正常（Multi）模式
GO
ALTER DATABASE {yourDataBaseName}
SET MULTI_USER;
GO


3.上述步驟如果都順利執行的話，恭喜你，你應該已經救回你的資料庫了！

4.但有時就是沒那麼簡單，依照冷日本次碰上的狀況，是資料庫無法卸載也無法啟動，最多只能『離線』，一整個卡住！！！
所以冷日採取了比較極端的作法：
A.先把 SQL Server 停掉，然後把 mdf 檔搬走
B.重新啟動 SQL Server，因為原本的資料庫檔案本體不存在，所以資料庫會直接顯示錯誤後消失！
C.此時在把 SQL Server 停掉，去到原本資料夾確認所有相關檔案都清空（就是以資料庫名稱命名的檔案都刪掉
D.再回去啟動 SQL Server，此時因為原資料庫已經沒有掛載在上面，應該沒有錯誤訊息順利啟動了！
E.重建一個和原本資料庫同名的資料庫！（這很重要，一定要名稱一模一樣喔
F.把 SQL Server 再次停掉，然後把我們步驟 A 所搬出去的 mdf 檔再複製回來新建立的資料庫檔案位置（Windows 應該會問你是否覆蓋，當然是覆蓋！
G.啟動 SQL Server 並執行下列語法：
USE master;
GO
ALTER DATABASE {yourDataBaseName}
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
EXEC('ALTER DATABASE [{yourDataBaseName}] SET EMERGENCY');
GO
DBCC checkdb ('{yourDataBaseName}', repair_allow_data_loss);
GO
ALTER DATABASE {yourDataBaseName}
SET MULTI_USER;
GO

H.靜待 SQL Server 幫你把 mdf 檔檢查並修復，同時也會把新的 ldf 給搞定，都完成之後，應該就回到慘案之前的狀態了

祝大家順利！

參考資料：
MSDN : Set a Database to Single-user Mode
MSDN : Start SQL Server in Single-User Mode
Manvendra's blog: Error Msg 1813, Level 16, State 2, Line 1, Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.
回覆
冷日
(冷日)		 發表時間：2016/11/12 7:23
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]MSSQL 2016 Set a Database to Single-user Mode

Set a Database to Single-user Mode

SQL Server 2016

Applies To: SQL Server 2016

This topic describes how to set a user-defined database to single-user mode in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

Limitations and Restrictions

  • If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

  • The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.


Prerequisites

  • Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. For more information, see ALTER DATABASE SET Options (Transact-SQL).

Security

Permissions

Requires ALTER permission on the database.

To set a database to single-user mode


  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Right-click the database to change, and then click Properties.

  3. In the Database Properties dialog box, click the Options page.

  4. From the Restrict Access option, select Single.

  5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

You can also set the database to Multiple or Restricted access by using this procedure. For more information about the Restrict Access options, see Database Properties (Options Page).

To set a database to single-user mode

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the AdventureWorks2012 database to READ_ONLY and returns access to the database to all users.The termination option WITH ROLLBACK IMMEDIATE is specified in the first ALTER DATABASE statement. This will cause all incomplete transactions to be rolled back and any other connections to the AdventureWorks2012 database to be immediately disconnected.




USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

ALTER DATABASE (Transact-SQL)

補充 （Community Additions）：
Switch to single-user mode and back, respecting AUTO_UPDATE_STATISTICS_ASYNC
Code that takes into account the AUTO_UPDATE_STATISTICS_ASYNC flag and if enabled, temporarily disables it. Sets the database into single-usermode, does the work, re-enablesmulti-usermode and re-enablesasynchronous statisticupdates (if needed).


        -- Drop temporary table "##OriginalState" table
        IF OBJECT_ID('tempdb.dbo.##OriginalState', 'U') IS NOT NULL BEGIN
            DROP TABLE ##OriginalState;
        END
        GO
        -- Create temporary table "##OriginalState"
        CREATE TABLE ##OriginalState([AsyncAutoStats] [BIT] NOT NULL);
        GO
        -- Fill in "AutoUpdateStatsAsync" in temp table
        DECLARE @AutoUpdateStatsAsync AS BIT;
        SELECT @AutoUpdateStatsAsync = CASE WHEN (is_auto_update_stats_on = 0) THEN 0 ELSE CASE WHEN (is_auto_update_stats_async_on = 0) THEN 0 ELSE 1 END END
        FROM sys.databases WHERE name = 'AdventureWorks2012'
        INSERT INTO ##OriginalState ([AsyncAutoStats]) SELECT @AutoUpdateStatsAsync;
        -- Disable "AutoUpdateStatsAsync"
        IF (@AutoUpdateStatsAsync = 1) BEGIN
            ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
        END
        GO
        -- Change database to "single-user mode"
        USE master;
        GO
        ALTER DATABASE AdventureWorks2012
        SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
        GO
        -- Do your work in sinle-user mode....
        -- ....
        -- Change database back to "multi-user mode"
        ALTER DATABASE AdventureWorks2012
        SET MULTI_USER;
        GO
        -- Re-enable asynchronous auto statistics
        DECLARE @AutoUpdateStatsAsync AS BIT;
        SELECT @AutoUpdateStatsAsync = [AsyncAutoStats] from ##OriginalState;
        IF (@AutoUpdateStatsAsync = 1) BEGIN
            ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC ON;
        END
        GO
        -- Drop temporary table "##OriginalState" table
        IF OBJECT_ID('tempdb.dbo.##OriginalState', 'U') IS NOT NULL BEGIN
            DROP TABLE ##OriginalState;
        END
        GO

原文出處： Set a Database to Single-user Mode
回覆
冷日
(冷日)		 發表時間：2016/11/12 7:27
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]MSSQL 2016 Error Msg 1813

Error Msg 1813, Level 16, State 2, Line 1, Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.


Fix/Solution/Workaround:

Follow below steps:-
1. Move mdf and ndf files to another directory (Data_old)
2. Create a database with the same name and same file names and locations as the original databases. (this only applies to the mdf and ndf files the log file can go anywhere)
3. Stop the SQL Server service.
4. Overwrite new mdf and ndf files with the original ones.
5. Start SQL Server.
6. Run this script (Set the @DB variable to the name of your database before running):
Declare @DB sysname;
set @DB = ‘DBName’;
– Put the database in emergency mode
EXEC(‘ALTER DATABASE [' + @DB + '] SET EMERGENCY’);
– Set single user mode
exec sp_dboption @DB, ‘single user’, ‘TRUE’;
or
– Repair database
DBCC checkdb (@DB, repair_allow_data_loss);
– Turn off single user mode
exec sp_dboption @DB, ‘single user’, ‘FALSE’;

If you are not able to connect after single user then run below cmd in one go.

Alter database dbname set single_user with roll back immediate
go
Run your DBCC checkdb command
go
Alter database dbname set multi_user

I got an error stating that the log file did not match the data file. You can ignore this as we are rebuilding the log file.
Posted by
原文出處：Manvendra's blog: Error Msg 1813, Level 16, State 2, Line 1, Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.
回覆
主題一覽的下方
討論串
前一個主題 | 下一個主題 | 頁首 | | |

| Powered by XOOPS 2.0 © 2001-2008 The XOOPS Project|