Shrink SQL Server transaction log files

Once I'm using database mirroring, all my databases has the recovery model set to Full.

When using this recovery model, you must have a good backup policy to regularly backup your transaction log, between full backups of your database. If not, the database transaction log file size may increase a lot.

To shrink the file size I use the following script:

exec sp_dboption DBName, 'trunc. log on chkpt.', true

checkpoint

DBCC SHRINKFILE (DBNameFileName, 500);

exec sp_dboption DBName, 'trunc. log on chkpt.', false

Sql Server Database Mirror and Recovery Model

When i've configured the Database Mirroring on my production environment, i've changed the database recovery model to full. Then, i've faced the issue with transaction log file size, that is increasing a lot, on all mirrored databases.

After some research, I found some information to solve this that i would like to share with you.

To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:

• Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.

• Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
For additional information about the issues to consider when you configure the autogrow option, click the following article number to view the article in the Microsoft Knowledge Base:

315512 (http://support.microsoft.com/kb/315512/) Considerations for autogrow and autoshrink configuration

• Change the recovery model. If a disaster or data corruption occurs, you must recover your database so that the data consistency and the transactional integrity of the database are maintained. Based on how critical the data in your database is, you can use one of the following recovery models to determine how your data is backed up and what your exposure to the data loss is:

•Simple recovery model

•Full recovery model

•Bulk-logged recovery model

By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups.
By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database.
You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.

• Back up the transaction log files regularly to delete the inactive transactions in your transaction log.

• Design the transactions to be small.

• Make sure that no uncommitted transactions continue to run for an indefinite time.

• Schedule the Update Statistics option to occur daily.

• To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.
For additional information about defragmenting the indexes in SQL Server 2000, see the following Microsoft Web site:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx)

If you must run the DBCC DBREINDEX statement as a job that is a part of the database maintenance plan, you must break up the job into multiple jobs. Additionally, you must take the frequent backups for the transaction logs between the execution of the jobs.

You can see the full document on the following address. http://support.microsoft.com/kb/873235

Kill all SQL active connections

For the last couple of weeks i'm using SSIS a lot. In some tasks i need to make sure that any client is connected to the database where i need to perform a specific task.

To solve this issue i found this script that basically, kills all the active connections on the database.

DECLARE 
@p_SPID int, 
@p_SQL nvarchar(2000),
@dbName nvarchar(100)

SET @dbName = 'MyDBName'

DECLARE #cur_Processes CURSOR FOR 

SELECT 
p.SPID 
FROM 
master.dbo.sysprocesses AS p 
JOIN master.dbo.sysdatabases AS d ON( d.dbid = p.dbid ) 
WHERE 
d.Name = @dbName AND p.SPID > 50 -- AND spid >= 51 (because spids of 50 or less are reserved for internal use.) 
OPEN #cur_Processes 
FETCH NEXT FROM #cur_Processes INTO @p_SPID 
WHILE @@FETCH_STATUS = 0 

BEGIN 
SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID ) 
PRINT @p_SQL 
EXECUTE( @p_SQL ) 
FETCH NEXT FROM #cur_Processes INTO @p_SPID 
END 
CLOSE #cur_Processes 
DEALLOCATE #cur_Processes 

VSTS 2008 VPC Image - Expires on December 31, 2008

As you probably notice, the VSTS 2008 VPC Image expired on April 1st. Microsoft has published a newer version with many enhancements including an updated Hands On Lab.

You can download it here.

http://www.microsoft.com/downloads/details.aspx?FamilyID=c7a809d8-8c9f-439f-8147-948bc6957812&displaylang=en

SQL Server Training Videos

The series features 13 professional videos that start at the very beginning and then explore designing tables/schemas, using data types, creating SPROCs, using reporting services and the business intelligence engine, enabling full text search across your data, and more (I'm guessing even most devs with a lot of data experience might learn some new things from the reporting services and full text search videos):

http://weblogs.asp.net/scottgu/archive/2007/03/01/free-sql-server-training-videos-and-other-good-data-tutorial-pointers.aspx

How to get the current row in Gridview RowCommand event

Blogger Tags: asp.net gridview

I've been working in a project where we use the Gridview control very often.

In this particularly case I need to get a reference to the current row of a gridview, in order to get some values that exists in several columns of the same gridview.

If we take a look at the signature of the method:

protected void GridView\_RowCommand(object sender, GridViewCommandEventArgs e)

you will notice that, at a first approach, in none of the parameters, we can get a reference to the current row.

Using the following single line of code, we can get a GridViewRow object referencing the current row.

GridViewRow row = (GridViewRow)((Control)e.CommandSource).Parent.Parent;

The code above is not so elegant as I wish, but the world is not perfect as well. :)

We can now use the following code to get a reference to existing controls on the current GridView row.

DropDownList ddlPriority = row.FindControl("ddlPriority") as DropDownList;

int priorityID = ddlPriority != null ? Int32.Parse(ddlPriority.SelectedValue) : 0;

 

Problem fixed !!!

Automated cleanup tool to remove the .NET Framework 1.0, 1.1, 2.0, 3.0 and 3.5

Aaron Stebner wrote an utility to remove all the .Net  frameworks from a computer.

http://blogs.msdn.com/astebner/archive/2006/05/30/611355.aspx