What can I do if my transaction log is full?--- Hot issues November

Question

Scenarios:

1. Transaction log is full
2. Transaction log cannot shrink

In these two scenarios, main concern is why transaction log cannot be reused. If log is waiting to do the log backup/checkpoint/active transaction, it cannot be shrunk. So firstly we need to find out why the log cannot be reused and then solve the problem.

   --Check log used space--
    dbcc sqlperf(logspace)

    --Check log reuse wait type--
    select log_reuse_wait_desc,* from sys.databases  

    --Check if there is active transaction--
    dbcc opentran

If transaction log is full, making log space can be reused doesn't decrease the size of physical log file size. We need to manually shrink the log file size.

 --Find the logical name of database’s log file--
    USE [Your database]
    GO
    SELECT Name AS LogicalName, filename AS PhysicalFile
    FROM sys.sysfiles
    GO

    --SHRINKFILE command--
    USE [Your database]
    GO
   -- Shrink the truncated log file to 8 MB--  
   DBCC SHRINKFILE (LogicalName_Log, 8);  
    GO  

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Answer 1

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

How to calculate the running total in TSQL

In this article, we will discuss how to write a SQL Query to Calculate Running Total in SQL Server with some examples.

We will use below sample table for testing purpose.
CREATE TABLE RUNTOTAL
(
OrderID int,
OrderName varchar(100),
PurchaseNum int,
SaleNum int
)

INSERT INTO RUNTOTAL VALUES  
(1,'Pen',10,20),  
(1,'Pencil',13,9),  
(1,'Clock',11,14),  
(1,'Paper',23,17),  
(2,'Calendar',30,29),  
(2,'Keyboard',15,34),  
(2,'Monitor',33,15),  
(2,'Cup',23,16)  
  
SELECT * FROM RUNTOTAL  

With GROUP BY, summing up the PurchaseNum and SaleNum for each OrderID will get rid of some details that may be useful in future statements:

SELECT OrderID  
,SUM(PurchaseNum) PurchaseNum  
,SUM(SaleNum) SaleNum  
FROM RUNTOTAL  
GROUP BY OrderID  

If we would like to show other columns like OrderNum which could not be included without making them part of the aggregation, which isn't ideal here.

If we insist on to add OrderName,,PurchaseNum and SaleNum like below, we would get one error as below.

SELECT OrderID,OrderName  
,PurchaseNum,SaleNum  
,SUM(PurchaseNum) PurchaseNum  
,SUM(SaleNum) SaleNum  
FROM RUNTOTAL  
GROUP BY OrderID  

However, OVER() could maintain details while still returning the summed values:

SELECT OrderID,OrderName  
,PurchaseNum,SUM(PurchaseNum) OVER (PARTITION BY OrderID) TOTALPurchaseNum  
,SaleNum,SUM(SaleNum) OVER (PARTITION BY OrderID) TOTALSaleNum  
FROM RUNTOTAL  

Each window (red border) contains the summed values for the requested partition only (By OrderID) at the base query detail level.

We could continue narrowing down the window, using ROWS or RANGE like below:

SELECT OrderID,OrderName  
,PurchaseNum,SUM(PurchaseNum) OVER (PARTITION BY OrderID) PurchaseNum  
,SaleNum,SUM(SaleNum) OVER (PARTITION BY OrderID ORDER BY OrderID ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) TOTALSaleNumTHISROWANDAFTER  
,SUM(SaleNum) OVER (PARTITION BY OrderID) SaleNum  
FROM RUNTOTAL  

The totalsalenumthisrowandafter shows the total of current row and the following row using ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING.

In the same way, we could have the total of current row and the proceeding row as below using ROWS BETWEEN 1 PRECEDING AND CURRENT ROW:

SELECT OrderID,OrderName  
,PurchaseNum,SUM(PurchaseNum) OVER (PARTITION BY OrderID) PurchaseNum  
,SaleNum,SUM(SaleNum) OVER (PARTITION BY OrderID ORDER BY OrderID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) TOTALSaleNumTHISROWANDBEFORE  
,SUM(SaleNum) OVER (PARTITION BY OrderID) SaleNum  
FROM RUNTOTAL  

In addition, if we would like to have a running total of all values within a group, we could use ROWS UNBOUNDED PRECEDING to achieve.

SELECT OrderID,OrderName  
,PurchaseNum,SUM(PurchaseNum) OVER (PARTITION BY OrderID) PurchaseNum  
,SaleNum,SUM(SaleNum) OVER (PARTITION BY OrderID ORDER BY OrderID ROWS UNBOUNDED PRECEDING) TOTALRUNNINGSaleNum  
,SUM(SaleNum) OVER (PARTITION BY OrderID) SaleNum  
FROM RUNTOTAL  

Finally, we could have a running total of all values without any group, we could remove the PARTITION BY OrderID part as below:

SELECT OrderID,OrderName  
,PurchaseNum,SUM(PurchaseNum) OVER (PARTITION BY OrderID) PurchaseNum  
,SaleNum,SUM(SaleNum) OVER (ORDER BY OrderID ROWS UNBOUNDED PRECEDING) WHOLETOTALRUNNINGSaleNum  
,SUM(SaleNum) OVER (PARTITION BY OrderID) SaleNum  
FROM RUNTOTAL  

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Answer 2

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Issue Title:
SQL Server Configuration Manager not showing in windows 10
Case link: https://learn.microsoft.com/en-us/answers/questions/166724/sql-server-configuration-manager-not-showing-in-wi.html

Question:
SQL Server Configuration Manager not showing in windows 10

Solution:
Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows (Windows 8 or Windows 10).
We can use below methods to access SQL Server Configuration Manager.

Method1:
To open SQL Server Configuration Manager, on the Start Page, type SQLServerManager15.msc (for SQL 2019). For other versions of SQL Server, replace 15 with the corresponding number. Clicking SQLServerManager15.msc opens the Configuration Manager. To pin the Configuration Manager to the Start Page or Task Bar, right-click SQLServerManager15.msc, and then click Open file location. In the Windows File Explorer, right-click SQLServerManager15.msc, and then click Pin to Start or Pin to taskbar.
Here is the folder and the file from my environment.

SQL Server 2008 SQLServerManager10.msc
SQL Server 2012 SQLServerManager11.msc
SQL Server 2014 SQLServerManager12.msc
SQL Server 2016 SQLServerManager13.msc
SQL Server 2017 SQLServerManager14.msc
SQL Server 2019 SQLServerManager15.msc

Method 2:
You can also create a shortcut using below steps.

1. Open MMC.exe by going to Start > Run > mmc.exe
2. In the menu bar, go to “File” and choose “Add/Remove Snap-in”
3. That would open a new window and we need to look for “SQL Server Configuration Manager”. Highlight that and hit “Add”.
   
4. Then hit OK.
5. You can access the SQL server configure manager.
   

Method 3.
A much simpler way is to run the Computer Management applet from Control Panel→Administrative Tools→Computer Management, where you should find the configuration manager installed

Reference
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver15