SQL Server's Fascinating Stories

Its often to have issues on transaction log getting full with an error: The Transaction Log for the database ABC is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in the SYS.DATABASES System catalog view. If we execute the following query in the query editor, select log_reuse_wait_desc from sys.databases where name = 'ABC'; we get one of the follwing possibilities: Reuse of transaction log space is currently waiting on one of the following: 0 = Nothing Currently there are one or more reusable virtual log files. 1 = Checkpoint No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). 2 = Log backup A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). Note: Log Backup do not prevent truncation 3 = Active backup or restore data backup or a restore is in progress (all recovery

Methods of Data Pull from Excel to Database Tables: There are several methods that can be used to pull the data from an excel sheet into a database table. Using SQL Server's Import and Export wizard that lets us choose the excel file from which the data to be pulled. Each sheet of the excel file can be distinguished from the sheet name during the mapping step in the wizard. A sheet, for example sheet 1 is referred as Sheet1$ and so is Sheet2$ and so on. Creating an SSIS package, that lets us choose the data source to be an excel file and the data destination that would be a database table through a OLE DB Connection properly set up. Another way would be using a linked server. Here, select the OLE DB provider to be Microsoft Jet 4.0 OLE DB Provider. Select the Product Name as Excel, Data source is the full path of the Excel file and the provider string is Excel 8.0. This configuration is as mentioned in the below picture.  And later, click OK, which will complete the configur

The following query is used to get the SQL Server Uptime. Implicitly defines the SQL Server Services uptime. USE [master] GO /****** Object:  StoredProcedure [dbo].[GetSQLUptime]******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GetSQLUptime] AS BEGIN Declare @theMinutes int Set @theMinutes = (SELECT TOP 1 DATEDIFF(mi,login_time, GETDATE()) AS TotalUpTimeInMinutes FROM sys.sysprocesses ORDER BY login_time) Select @@SERVERNAME AS ServerName, convert(varchar(15), @theMinutes / 1440 ) + ' Days, ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 1440) / 60 ))) + convert(varchar(2), (@theMinutes % 1440) / 60 ) + ' Hours, ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 60)))) + convert(varchar(2), (@theMinutes % 60)) + ' Minutes' AS SQLUptime_Days_Hours_Minutes END GO