The transaction log for database 'mydatabase' is full

Issue : The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Resolution :

BACKUP LOG WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (, 1)
GO

Basic Understanding for Sql server

* Difference between Clustered Index and Non Clustered Index

- A Clustered Index consists of index as well as data pages. Clustered Index is not just an index but also contains the table data. A clustered index is organized as a B-tree where the non-leaf nodes are index pages and the leaf nodes are data pages.

- A Non-clustered index is organized as a B-tree but it consists of only index pages. The leaf nodes in a non-clustered index are not data pages, but contains pointer for individual rows in a data pages.

* Master Database contains login Information.
* MSDB Database contains Job Information.
* Model system database is default to FULL recovery model.


* Difference between Primary Key and Unique Key is as follows

- Primary key prevents the duplication of key values and does not allow NULL values. It allows each row in a table to be identified uniquely.
- Unique Key does not same what Primary Key does except it allows NULL record.

* Only One NULL values can be inserted for column that has Unique Key defined.

* Difference between Clustered Index and Non Clustered Index

- A Clustered Index consists of index as well as data pages. Clustered Index is not just an index but also contains the table data. A clustered index is organized as a B-tree where the non-leaf nodes are index pages and the leaf nodes are data pages.

- A Non-clustered index is organized as a B-tree but it consists of only index pages. The leaf nodes in a non-clustered index are not data pages, but contains pointer for individual rows in a data pages.

Stuff Keyword in Sqlserver

STUFF - The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Syntex - STUFF (character_expression , start , length ,character_expression )

Example -

SELECT STUFF('VABCDERBHATT', 2, 6, 'iral');
GO

Get Row number from query

use Row_Number() function to get row numbers in rows

ex.

select Row_Number() over (order by fieldname) as rowid, * from [Tablename]

Export EXCEL File To SQL Server 2000 /2005 /2008

CREATE procedure [dbo].[InsertEXCELToSQLSERVER]
as

declare @Field1 nvarchar(50)
declare @Field2 nvarchar(50)

declare @CursorName cursor

set @CursorName = cursor
for
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\path\datafile.xls','select @Field1,@Field2 from [Sheet1$]')
open @CursorName
fetch next from @CursorName into @Field1, @Field2

WHILE @@FETCH_STATUS = 0
BEGIN
select @Field1, @Field2 ;

INSERT INTO Tablename (Field1,Field2) VALUES (@Field1, @Field2 )

fetch next from @UserCursor into @Field1,@Field2

END

CLOSE @CursorName
DEALLOCATE @CursorName

OutPut : following will execute and insert data in to table
exec InsertEXCELToSQLSERVER

Functions In Sqlserver

Table valued Functions

create function [dbo].[GetInformation](@ParameterId int)
returns table as
return (
select count(*) as total from tablename where condition=@ParameterId)

to view results
select * from GetInformation(10) where 10 is parent table's primary key Id

Scalar valued Functions

ALTER FUNCTION [dbo].[GenerateReceiptNumber](@FirstName varchar(50),@Year varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Result varchar(50)
Declare @FN varchar(50)
Declare @YR varchar(50)

set @FN = @FirstName
set @YR = @Year


SELECT @Result = upper(@FN + @YR)

RETURN @Result

END

to view results write

select dbo.GenerateReferenceNumber('MyName','2009')
OutOut -- > MyName2009

Use Of Pivot Table in Sqlserver 2005

Display Columns into Rows Using Pivot Table

here is the example that from Attendence table Want to get Yearly Attendence report By Student Wise

select StudentID,
StudentName,
isnull(January,0) January,
isnull(February,0) February,
isnull(March,0) March,
isnull(April,0) April,
isnull(May,0) May,
isnull(June,0) June,
isnull(July,0) July,
isnull(August,0) August,
isnull(September,0) September,
isnull(October,0) October,
isnull(November,0) November,
isnull(December,0) December
from
(
select StudentID, StudentName, DateName(m,AttendenceDate) [Month], Count(*) Total from AttendenceTable
Group by StudentID, DateName(m,AttendenceDate),StudentName
) as source
Pivot
(
sum(Total)
for [month] in (January,February,March,April,May,June,July,August,September, October, November, December)

) as result