SQL Log File Too Big (2022-02-16)

SQLServerCentral Newsletter for February 16, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Log File Too Big]( - ["I started lobbying to just toss the tool aside."]( - [DAX table functions for paginated reports: Part 1]( - [From the SQL Server Central Blogs - SQL Server – Backing up the Tail of the Log]( - [From the SQL Server Central Blogs - Data Thoughts – Renewing Microsoft Certifications]( Question of the Day - [Log File Architecture]( The Voice of the DBA  Daily Coping Tip Focus on being kind rather than right I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. The NoSQL Rise and Fall I'm not sure [this blog that seems to talk about the problems of NoSQL databases]( in general makes a lot of sense. If you read the comments, you certainly see lots of complaints, but I also think the post isn't well written. It lists the problems of RDBMSes as possibly deleting all tables while changing a key or being unable to add a column easily. While I don't know about all RDBMSes, I don't know any that could lose all data with anything less than DROP DATABASE or TRUNCATE TABLE. At the same time, the NoSQL complaints and problems seem to be presented as very general thoughts, which isn't good. The various NoSQL flavors of databases vary widely, and the way you look at a columnar or graph database is much different than a document database. Really, the piece ought to be separated to look at a certain class of NoSQL database compared with RDBMSes. That being said, I do see less hype around NoSQL in general. The class of problems handled by various types of databases vary, and I am sure that lots of people using NoSQL databases are quite pleased with them. At the same time, I'm not sure a lot of the RDBMSes did get replaced or continue to get replaced. RDBMSes have a lot of value and we have a lot of experience, both as developers and administrators. There might be a shortage of NoSQL (pick a type) administrators for operating the databases. Even in some of the famous companies that use them, I find developers often explaining a RCA (root cause analysis) or the troubleshooting of a particular process and fixing an issue. While that might be a choice that an organization makes, a lot of developers that I know don't want to be supporting systems very often, especially at night and on weekends. A couple of bad incidents usually has developers wondering why there isn't an expert on the Operations staff. I do think that many of the benefits of different types of NoSQL databases are a bit over-hyped. Not that a graph database can't handle your e-commerce system, but it likely takes a lot of code, and likely a lot of mistakes in getting it to work well. I also feel that some of the downsides of RDBMSes are minimized. They are a pain for application developers to work with, and the SQL language isn't very sensible. That being said, we have a lot of experience with these databases, and that should not be discounted. Productivity is a big advantage in trying to build new software in a flexible manner. I am all for experimenting with different platforms to understand where they work well, but also learn to use your existing RDBMS platform well. Write better code and you might be surprised just how scalable your system proves itself to be. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQL Log File Too Big]( Ginger Keys Daniel from SQLServerCentral Is your SQL database log file too big? Maybe it is. But maybe it’s not. When log files keep growing and appear to be too big some might suggest switching to Simple recovery, shrinking the log file, and switching back to Full recovery. While this will work to shrink the file and free up disk […] [External Article]( ["I started lobbying to just toss the tool aside."]( Additional Articles from Redgate In a total 180° turn, find out which Redgate product Grant Fritchey wanted to get rid of a few years ago, why he changed his mind and why it's now one of the reasons he is so proud to work at Redgate! [External Article]( [DAX table functions for paginated reports: Part 1]( Additional Articles from SimpleTalk Several DAX table functions can be used for paginated reports when you want all the data, not a summary. Adam Aspin explains how to use DAX table functions for paginated reports. [Blog Post]( From the SQL Server Central Blogs - [SQL Server – Backing up the Tail of the Log]( nelsonaloor from PracticalSQLDba When a database gets corrupted or failure occurs, before restoring the database from the backups you've created, it is recommended to create a tail-log backup of the records that... [Blog Post]( From the SQL Server Central Blogs - [Data Thoughts – Renewing Microsoft Certifications]( Martin Catherall from measure twice, cut once. G’Day, I recently renewed one of my Azure Certifications, extending it by another year from the original expiry data. You can extend any certification that is about to expire...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Log File Architecture Pages and Extents are used to store data in data files, with rows being stored on the pages. For a log file, how many log records in SQL Server fit on a page? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Yearly Uptime of Four Nines If I want to ensure 99.99% uptime for one of my databases, how much downtime is allowed each year? Answer: 52 minutes Explanation: Four nines of uptime is 99.99% of the time. In a year, this allows you 52.6 minutes of downtime, or just under an hour. Ref: High Availability - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [How to find out the time when command failed in sql server?]( - I was performing dile shrinking using GUI. After more than 10 hours I got an error, saying that the shrink command failed. Maybe a silly question but I am not able to see exact time when Error occurred in sql server? I started shrink file job in SSMS using GUI. After more than 10 hours […] SQL Server 2016 - Administration [Restore Verify only failing.]( - We are having issue for backup restore from lower version 2012 to 2016. We copied the backup file over the network from other VM and did check the backup file using restore verify and this verify getting fail. Please help with your valuable suggestion.   SQL Server 2016 - Development and T-SQL [Attempted foreign key constraint creation involving multiple fields fails]( - Hello Experts I have defined a table with an incrementing primary key (pretty usual), and also with a unique index on two other fields, thus: Create Table dbo.CTRs ( CTRseqNo Integer identity(1,1) not null, -- Incrementing number, generated on import. LocalPatientID varchar(20) not null, -- Assigned by provider, unique to them ReviewDate Date not null, […] Development - SQL Server 2014 [Stop job after specified run time]( - I'm testing a long running job and want to stop it after it has been running for, say, 2 hours. How can I do this automatically, i.e. without being around to manually stop it? SQL Server 2012 - T-SQL [Show only once repeated value in result a set]( - Hello Everyone, Here again I came with a question. Below is my query for a report that I want. It's working fine with no issues. SELECT TP.Location, TE.Name, TEI.imp_amount_approved_by_finance AS Amount, CONVERT(DATE, TEI.imp_fin_appr_date, 103) AS PaidDate FROM tbl_emp_imprest TEI JOIN tbl_Projects TP ON TP.Sno = TEI.imp_emp_location JOIN tbl_Employee TE ON TE.Sno = TEI.imp_id WHERE imp_amount_approved_by_finance […] SQL Server 2019 - Administration [Need to extract all queries used across all packages]( - A developer is asking me to provide a list of all the "queries" (aka sqlcommand) as it is in the tables there.  He is asking for ALL of the queries from ALL of the packages... Is there an extract I can run against the SSISDB to render this list of queries?  (the developer was pulling […] SQL Server 2019 - Development [Compare 2 rows in the same table and output result when conditions are met]( - I am newbie to SQL, tried searching for answers for similar question but couldn't find any. Sorry, if this type of question was asked before. I have a temp table as below: Drop TABLE if EXISTS #temp Create table #temp(ID int, Country VARCHAR(40), ItemCount int, DeliveryDate Date, Itemtype VARCHAR(40) ) insert #temp(id,Country,itemCount,DeliveryDate,Itemtype) Select 3012111,'Dublin', 100, […] [Compare 2 rows in the same table and output result when conditions are met]( - I am newbie to SQL, tried searching for answers for similar question but couldn't find any. Sorry, if this type of question was asked before. I have a temp table as below: Drop TABLE if EXISTS #temp Create table #temp(ID int, Country VARCHAR(40), ItemCount int, DeliveryDate Date, Itemtype VARCHAR(40) ) insert #temp(id,Country,itemCount,DeliveryDate,Itemtype) Select 3012111,'Dublin', 100, […] [Bulk Insert C# Code Error]( - I figured out the issue. Please ignore post. [How to do recursion in join clause or in exits clause or in "in" clause.]( - hi, I have used "with cte" to do some recursion, which i have used on top, is there any why that i can do recursion with "with cte" or with other techniques in side the exists clause or in outer apply or cross apply . note : "with out using function" as some one said […] [T-SQL : Slicing time periods]( - Consider the following table that gives the validity dates for which an Item is associated to something called a DU, and a certain Market : CREATE TABLE Items (Item_ID VARCHAR(2) ,Start_Date DATE ,End_Date DATE ,DU VARCHAR(2) ,Market VARCHAR(10) ); INSERT INTO Items VALUES ('X', '2020-06-01', '2020-09-30' , 'A', 'GREECE'); INSERT INTO Items VALUES ('X', '2020-11-01', '2021-01-01' , 'A', 'BELGIUM'); INSERT […] T-SQL (SS2K8) [running OPENROWSET from sql function with parameter]( - Following function works if the parameter to harcoded as shown below. But i would like to replace the value with parameter. PO12345 - should be replaced by @IpRef. I am not able to figure out how to do that. --select * from dbo.ki_GetSupplyUsage ('PO12345') alter FUNCTION ki_GetSupplyUsage(@IpRef VARCHAR(100)) RETURNS TABLE AS return SELECT * FROM […] SQL Azure - Administration [Tempdb Behavior in Managed Instances]( - Dear colleagues, I understood that the tempdb size will depend on multiple factors, such as the service tier, the number of vcores and much of it is directly controlled by Microsoft. However, something that is very strange to me, is that when the instance is restarted not always come back with the same configuration for […] General [Python Pattern best practice]( - Ahoi, anyone can tell me if there is a pattern/best practice to handle this type of request handling? I am trying to handle the execution of functions by name. I found a pattern for classes using a dicitionary but i couldnt make it work like this. What i have i have made work is […] Integration Services [Issue with Execute SQL Task in Sequence container returning 0 results]( - I'm working on a package to update some local tables from a datalake we access. I've been able to write individual incremental update packages for each table. The general flow of the package is "Get Now" (UTC) - Get Max Modified Utc from local table, get list of changed ids from source table where modified […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -