Transaction log full Error - Solution...

DECLARE @DBName varchar(255)

DECLARE @DBLogicalFileName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR

select DATABASE_NAME = db_name(MaTableMasterFiles.database_id), MaTableMasterFiles.name

from sys.master_files MaTableMasterFiles

where

-- ONLINE

MaTableMasterFiles.state = 0

-- Only look at databases to which we have access

and has_dbaccess(db_name(MaTableMasterFiles.database_id)) = 1

-- Not master, tempdb or model

and db_name(MaTableMasterFiles.database_id) not in ('Master','tempdb','model','msdb')

AND type_desc LIKE 'log'

group by MaTableMasterFiles.database_id, MaTableMasterFiles.name

order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName

WHILE @@FETCH_STATUS = 0

BEGIN

Exec ('BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY')

Exec ('Use [' + @DBName + '] DBCC SHRINKFILE ("' + @DBLogicalFileName + '")')

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName

END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

Using INSERT / OUTPUT in a SQL Server Transaction

One of the Transact/SQL enhancements in Microsoft SQL Server is the OUTPUT sub-clause of the INSERT statement. You can now capture the records inserted via an INSERT statement (think also being able to capture IDENTITY column values for the new rows) for subsequent use in an additional INSERT statement for a child table to persist referential integrity without the need for an INSERT trigger */

Why not just use a trigger? It's a viable and proven construct of SQL Server, right?

The short answer is "Yes, it is." However, triggers are one of those nasty little secrets that the database keeps. They don't just jump right out at you and say "HERE I AM!"

Also the trigger is sitting in the background performing as it's been asked to may be causing your issues, but you're going to go through many iterations of searching stored procedures, and ad-hoc T/SQL code before you probably even stop to consider there is a trigger firing off data modification language commands (DML) - INSERTS, UPDATES, or DELETES that are adjunct to what you're trying to diagnose.

I associate the use of triggers with the use of ad-hoc T/SQL code used in an application's code stack and passed to a SQL Server instance for processing - practices to shy away from.

Syntax

INSERT INTO <SOME_TABLE>
(
<column_list>
)
OUTPUT INSERTED.<identity_column> --and other columns from SOME_TABLE if need be
INTO <SOME_OTHER_TABLE>
(
<column_list>
)
SELECT
(
<column_list>
)
FROM <source_table_OR_JOIN_of_multiple_tables>
WHERE <filtering_criteria>

/* The example for this would be A right-jolly old elf is being hired for some in-store promotions and in keeping with corporate policy you always perform a 90 day review for any new hires. We want to have the notfication recorded when the new hire is entered without any additional work on the part of Human Resources. The code below demonstates how we can use INSERT-OUTPUT to do this
*/


Sample:

USE AdventureWorks;
GO

---Create Example Tables
/*
Note, this is not fully-normalized. I would have included another table
for Notification Types if this was an actual solution.
I would also use an int NotificationTypeID column in Notifications table
instead of a varchar(xx) NotificationType column.
*/

CREATE SCHEMA [HR] AUTHORIZATION dbo;
GO

CREATE TABLE [HR].[Staff]
(
[StaffID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] VARCHAR(30) NOT NULL,
[LastName] VARCHAR(30) NOT NULL,

CONSTRAINT [PK_StaffID] PRIMARY KEY CLUSTERED
(
[StaffID] ASC
)ON [PRIMARY]
) ON [PRIMARY];

CREATE TABLE [HR].[Notification]
(
[NotificationID] [int] IDENTITY(1,1) NOT NULL,
[StaffID] [int] NOT NULL,
[NotificationDate] DATETIME NOT NULL,
[NotificationType] VARCHAR(30) NOT NULL,

CONSTRAINT [PK_NotificationID] PRIMARY KEY CLUSTERED
(
[NotificationID] ASC
)ON [PRIMARY]
) ON [PRIMARY];



/*
Demonstrate how you can insert the key values added to Staff.StaffID
into Notifications.StaffID in single transaction
*/

INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Santa','Claus');


Now there is a very important - and quite limiting caveat to using INSERT-OUTPUT. The output target or the child table cannot be a part of any foreign key relationship.




--Add Foreign Key for StaffID column to Notifications table
ALTER TABLE HR.Notification ADD CONSTRAINT [FK_Notification_Staff]
FOREIGN KEY
(
StaffID
)
REFERENCES HR.Staff
(
StaffID
);

/*
Demonstrate how you can insert the key values added to Staff.StaffID
into Notifications.StaffID in single transaction
*/

INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Frosty','Snowman');

SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;

The above will throw an error....

Msg 332, LEVEL 16, State 1, Line 17
The target TABLE 'HR.Notification' OF the OUTPUT INTO clause cannot be ON either side OF a (PRIMARY KEY, FOREIGN KEY) relationship. Found reference CONSTRAINT 'FK_Notification_Staff'.

ASP.NET Tips

//Adding a namespace in Designer view

<%@ Import Namespace="Sluice.GlobalClass" %>

//Adding a C# code in Design View

<%#SessionHelper.BusinessObjectsInfo.Permission.CanBasecampIntegration%>

Custom Validator With ClientValidation Property

//Check whether the either one of text box or drop down list is selected using JQuery */

//Step 1 - Javascript Code

function CheckProjectEntered(source, arguments) {

try {

var ddlProject = $("select[id*='ddlProject']");

var txtProjectTitle = $("input[id*='txtProjectTitle']");

if ($(ddlProject).get(0).selectedIndex == 0 && $(txtProjectTitle).val().length == 0)

arguments.IsValid = false;

else

arguments.IsValid = true;

} catch (e) {

}

}

//Step 2:

< id="vsProjects" runat="server" cssclass="error">

//Step 3:

//DropDownList Control

< id="ddlProject" runat="server" width="200px" tabindex="1">

< / asp:DropDownList>

< id="cvProjectTypeId" runat="server" display="None" clientvalidationfunction="CheckProjectEntered" errormessage="Select or Enter a Project name!" text=" " validateemptytext="True">< / asp:CustomValidator >

//Step 4:

//TextBox

< id="txtProjectTitle" runat="server" width="200px" tabindex="2" maxlength="20">< / asp : TextBox >

< id="cvCreateProject" runat="server" display="None" clientvalidationfunction="CheckProjectEntered" errormessage="Select or Enter a Project name!" text=" " validationgroup="ProjectExport" validateemptytext="True"> < / asp:CustomValidator >

//Step 5:

< id="btnExport" runat="server" text="Export My Project" cssclass="button" causesvalidation="true" tabindex="3">

< id="btnCancel" runat="server" text="Cancel" cssclass="button" causesvalidation="false" onclientclick="$find('mpeEnterProject').hide();" tabindex="4">

//Key points to note

1. The controltovalidate property should not be used.

2. CausesValidation should be true for the control which is going to be validation and should be false for the control which has the cancel option.