question

Willsonyuan-msft avatar image
6 Votes"
Willsonyuan-msft asked ·

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

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.


sql-server-generalsql-server-transact-sqlsql-server-reporting-servicessql-server-integration-servicessql-server-analysis-services
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is point of this incomplete post. You have not taken into account many scenarios which can cause log file to get full. If you are writing it please make it thorough and please try to include all scenarios like that of AG, mirroring, Logshipping, replication etc.

0 Votes 0 ·

@Shashank-Singh Thanks for your comments, really appreciate it. And yes, we did not take into account too much scenarios which can cause log file to get full, the point is to share some basic troubleshooting methods so that the community members can find the general ways to solve their issue quickly.

0 Votes 0 ·
Willsonyuan-msft avatar image
0 Votes"
Willsonyuan-msft answered ·

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://docs.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.
44480-1.png


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”.
44554-2.png


  1. Then hit OK.

  2. You can access the SQL server configure manager.
    44555-3.png


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
44576-4.png

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


1.png (72.8 KiB)
2.png (99.1 KiB)
3.png (120.1 KiB)
4.png (203.0 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Willsonyuan-msft avatar image
0 Votes"
Willsonyuan-msft answered ·

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

60078-1.png

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

60147-2.png

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

60107-3.png

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

60153-4.png

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

60079-5.png

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

60154-6.png

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

60161-7.png

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

60156-8.png



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.


1.png (43.2 KiB)
2.png (16.6 KiB)
3.png (31.5 KiB)
4.png (49.2 KiB)
5.png (63.4 KiB)
6.png (64.3 KiB)
7.png (60.5 KiB)
8.png (56.5 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.