SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Note: Please read the complete post before taking any actions.

This blog post would discuss SHRINKFILE and TRUNCATE Log File. The script mentioned in the email received from reader contains the following questionable code:

“Hi Pinal,

If you could remember, I and my manager met you at TechEd in Bangalore.

We just upgraded to SQL Server 2008. One of our jobs failed as it was using the following code.

The error was:

Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

The code was:

DBCC SHRINKFILE(TestDBLog, 1)BACKUP LOG TestDB WITH TRUNCATE_ONLYDBCC SHRINKFILE(TestDBLog, 1)GO

I have modified that code to subsequent code and it works fine. But, are there other suggestions you have at the moment?

USE [master]
GOALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAITDBCC SHRINKFILE(TestDbLog, 1)ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

Configuration of our server and system is as follows:

[Removed not relevant data]“

An email like this that suddenly pops out in early morning is alarming email. Because I am a dead, busy mind, so I had only one min to reply. I wrote down quickly the following note. (As I said, it was a single-minute email so it is not completely accurate). Here is that quick email shared with all of you.

“Hi Mr. DBA [removed the name]

Thanks for your email. I suggest you stop this practice. There are many issues included here, but I would list two major issues:

1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.

2) Shrinking database file or database adds fragmentation.

There are a lot of things you can do. First, start taking proper log backup using following command instead of truncating them and losing them frequently.

BACKUP LOG [TestDb] TO  DISK = N'C:\Backup\TestDb.bak'GO

Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.

There are so many things to add here, but you can call me on my [phone number]. Before you call me, I suggest for accuracy you read Paul Randel‘s two posts here and here and Brent Ozar‘s Post here.

Kind Regards,
Pinal Dave”

I guess this post is very much clear to you. Please leave your comments here. As mentioned, this is a very huge subject; I have just touched a tip of the ice-berg and have tried to point to authentic knowledge.

Update: Small typo correction and small detail corrected based on feedback.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Could not load file or assembly ‘AjaxControlToolkit’ or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0×80070057 (E_INVALIDARG))

If you are working with the Ajax Control Toolkit, sometimes the AjaxControlToolkit assembly will become corrupted in your Temporary ASP.NET Files folder. Here are a set of steps that might help you get back up and running. This is only relevant to developing with the built in web server, Cassini.

But first, here is the scenario.

Last Friday, my computer crashed while I was working on a project which references the AJAX Control Toolkit assembly.

After my computer restarted, I opened the project in Visual Studio 2008. I launched the default web page and I received the following error:

Could not load file or assembly ‘AjaxControlToolkit’ or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0×80070057 (E_INVALIDARG))

I tried closing and reopening the project; I even restarted my computer, but none of these efforts were successful.

I deleted and re-added the AjaxControlToolkit assembly reference, but still no dice.

Here are the steps that I found which worked for me:

1. Close Visual Studio
2. Open the Temporary ASP.NET Files folder and delete its contents.
• If the OS is Windows Vista, then the folder is
  %LocalAppData%\Temp\Temporary ASP.NET Files
• If the OS is Windows XP, then the folder is%WINDIR%\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files
3. Restart Visual Studio
4. Open the project

That's it. Works great now.

Hope this helps.

Reference From : http://weblogs.asp.net/joshuajohnson/archive/2008/12/22/could-not-load-file-or-assembly-ajaxcontroltoolkit-or-one-of-its-dependencies-the-parameter-is-incorrect-exception-from-hresult-0-215-80070057-e-invalidarg.aspx

How to return the ID from the database on insert

Here is the Example of a Insert statement which returns ID of the row got inserted.

DECLARE ID int
INSERT INTO <tablename> (<colums name>) VALUES (<values>)
SELECT ID = SCOPE_IDENTITY()


Hope it will help you.

Thanks
Solomon S.


If information is helpful then don't forget to post comments

Selecting Random Rows in SQL Server

Here is the quick and easy way to select random rows in sql server.

SELECT <columns Name> FROM <table name> ORDER BY NewID()

Reference from : Brian Jeremy
......

Hope it will help you.

Thanks
Solomon S.


If information is helpfull then don't forget to post comments

How to stop bulk update in sql server

I was searching how to prevent SQL Injection and stop updating all rows of the table and I got a solution in given link http://www.mssqltips.com/sqlservertip/1851/prevent-accidental-update-or-delete-commands-of-all-rows-in-a-sql-server-table/

Post is really good and helpfull, But when I was Creating Trigger to stop bulk update by following the link I found index =1 is not working for few tables then i used index=0 still not for all tables finally I make some changes in SQL and got the result.

I removed Index from where clause and finally its work for all tables.

My Modified Query is given below.

USE AdventureWorks
GO

CREATE TRIGGER [Purchasing].[uPreventWholeUpdate] 
ON [Purchasing].[VendorContact] 
FOR UPDATE AS 
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;
         
     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats 
         WHERE OBJECT_ID = OBJECT_ID('Purchasing.VendorContact' ) )
     BEGIN
         RAISERROR('Cannot update all rows',16,1) 
         ROLLBACK TRANSACTION
         RETURN;
     END
END
GO

If this link is help full please do post comments.

Start writting blog from today

Hi friends,

I have planned to write blog when I'll get time. and I thought if I'll will start writing on Programming and new Technology that I learned and want to share, That will help me later and the user also. And new developers can also get ideas and solutions from my post. isn't it ?

I planned to write on ASP.Net, PHP, SQL Server, AJAX, JQuery.