Shrink Log File

Just a few days back I received an issue regarding disk size is out of space. I have tried to shrink the database using DBCC ShrinkFile but there isn’t any success.

I have follow below steps to resolve this issue and it really works.

I have check the below query for

Select name,log_reuse_wait_desc from sys.databases

1. There was another database which is waiting log backup to release the space
2. There was one database who is waiting for Replication to release the space

Database who is waiting for log backup to release the log space

I have taken the log backup two times and executed the database shrink file command to release the space and it has reclaimed 40GB space from transaction log file.

Database who is waiting for Replication to release the log space

This database is in Simple recovery mode and there isn’t any replication enable on this. I have executed the DBCC OPENTRAN command to see any active transaction. I have executed the DBCC OpenTran and it has provided me the below result.

Oldest active transaction:

SPID (server process ID): 101

UID (user ID) : -1

Name : INSERT

LSN : (999:138204:2)

Start time : OCT 13 2009 1:34:47:827PM

SID : 0x88d52e4051a71143adee5dc7b6619f8a

Replicated Transaction Information:

Oldest distributed LSN : (890:2091888:1)

Oldest non-distributed LSN : (896:2784855:1)

I don’t know the exact reason what happened internally. But from the output it seems that there is unmark distributed transaction. So I have executed Sp_Repldone command to unmark the LSN

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Than I have executed the DBCC ShrinkFile command and it has reclaimed the 400GB space

SQL Server Database Consistency Check Errors are Reported by DBCC CHECKDB

Microsoft SQL Server includes an inbuilt utility, called DBCC CHECKDB, to check the consistency and integrity of MDF (Master Database File). However, sometimes the DBCC CHECKDB process does not complete successfully and can not fix the associated issues. Database inconsistency can make the SQL Server database inaccessible and lead to severe data loss situations. In such cases, you need to recover SQL database, in order to extract data from it. When you execute DBCC CHECKDB on an SQL database, an error message similar to the following one, appears in ERRORLOG of Microsoft SQL Server- “2010-03-31 22:07:06.34 spid53 DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required.” The above message shows the total number of database consistency errors in SQL database and how many of them are repaired. The message information, start from 'internal database snapshot...', appears only if you run the DBCC CHECKDB online. The database consistency check process is halted with this error message and your database remains inaccessible. To gain access of your valuable data, SQL database recovery is required. Root of the problem: DBCC CHECKDB utility checks the logical and physical consistency of SQL Server database pages, allocation pages, rows, system table referential integrity, index relationships, and other database structure checks. In case any one of these checks does not complete successfully (depending upon the options that you have selected), errors are reported as part of this command. This behavior may occur due to numerous reasons of database corruption, such as hardware system problems, file system corruption, damaged pages in memory, or issues with MS SQL Server Engine. Resolution: The most excellent solution to fix DBCC CHECKDB consistency errors is to restore the database from the most recent backup. However, if backup is not available or updated, CHECKDB offers an option to repair the errors. When all else fails, SQL recovery software are required to repair and restore the damaged SQL Server database. They employs high-end scanning techniques to thoroughly scan entire database and extract all of its inaccessible objects. The MS SQL recovery tools are pretty safe and easy to use. Stellar Phoenix SQL Recovery software repairs damaged or corrupt SQL Server database and restores it to default or user-specified location. It is usable with Microsoft SQL Server 2008, 2005, and 2000. The software extracts all items from SQL Server database, such as tables, reports, macros, forms, stored procedures, triggers, and constraints.

Resolving “Internal error. Buffer provided to read..” MS SQL Server Error

Microsoft SQL Server enables you to create tables for short term uses. Such tables are known as Temporary Tables that are usable only for a particular session. MS SQL Server supports two types of temporary tables- Global temporary table and Local temporary table. However, under some situations, you may encounter database corruption errors while running queries on the temporary tables. The corrupt SQL Server database (MDF or Master Database File) can not be accessed by any means and leads to serious data loss situations. In order to overcome such cases, you need to repair MDF file using appropriate tools.

As a practical example of this problem with Microsoft SQL Server, you may encounter the below error message when you run any query on MDF file that references temporary table in MS SQL Server 2005:

“Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.”

The process fails with the above error message. In case you run DBCC CHECKDB SQL statement on your problem MDF file, the database integrity and consistency check process completes successfully without reporting any error.

The database is still inaccessible and you get the same error every time you try to access the database. In order to access your precious data in MDF file, you needto identify the root of this behavior and repair MDF database by fixing it.

Grounds of the problem:

You may come across this problem due to any of the below reasons:

• The temporary SQL Server database tables are cached in Microsoft SQL Server 2005. If table schema is not changed, the temporary tables can be reused. But, if cached temporary database table is reused incorrectly, this problem occurs.

• The MDF file is corrupt.

Resolution

Go through the following MDF repair steps to resolve this problem:

• Install updates for Microsoft SQL Server 2005.

• Download and install hotfixes from Microsoft's website to work around this behavior.

If both the above methods fail to sort out your problem, you are need to opt forMDF file repair software to repair and restore corrupt MDF file. The applications are incorporated with highly-effective and advanced scanning techniques to ensure absolute recovery of inaccessible MDF file objects.

USING OPENXML TO PROCESS CSV (COMMA-SEPARATED VALUE) STRINGS

This may be common knowledge to those among us that are handy with XML (I'm not), but since I just uncovered this nice little trick I thought I'd pass it along either way. It is a very easy way to process a CSV string and turn it into multiple rows:

declare @idoc int -- the handle for the XML document

declare @csv varchar(8000) -- the CSV we are being passed

set @csv = '1,2,3,7,4,8,10' -- our list of CSV values

-- With a simple edit of the CSV string:

set @csv = ''

-- We can now do this:

EXEC sp_xml_preparedocument @idoc OUTPUT, @csv

-- And then:

SELECT *

FROM OPENXML (@idoc, '/root/id',1)

WITH (value int)

This raises some interesting possibilities about ways we can process regular text strings (like CSV's) by using simple

replace techniques to turn that string into an XML value. It is certainly shorter that the usual looping technique to split

a CSV string into each value and then insert that value into a temp table or a table variable. I'm not sure if it's more or

less efficient, though.Anyone else have any other nice tricks like this?

Connect to SQL Server from command prompt – list tables and database

Q. How do I connect to Microsoft SQL Server from command prompt? I just wanted to see list of tables and database.

A. MS- SQL Server is relational database management system.

Its primary query language is Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase.

There is command line tool available and it is called as sqlcmd. you need to enter the word GO after every command.

On the Start menu click Run. In the Open box type cmd, and then click OK to open a Command Prompt window.

At the command prompt, type sqlcmd.

Press ENTER.

Let us say your username is vivek and password is foo, use:

C:> sqlcmd -U vivek -P foo

Once connected you should see 1> prompt. Type following command to use database called sales:

use sales

GO

To list tables type:

sp_help

OR

select * from SYSOBJECTS where TYPE = 'U' order by NAME

To List all the databases on the server:

sp_databases

To list fields in a table called foo:

sp_help tablename

sp_help foo