Ms Sql Server Technology Discussions

[deals2buy]

[b] Shrinking Truncate Log File – Log Full[/b]


Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.
[color=blue]USE [/color][color=black]DatabaseName
GO[/color]
[color=blue]DBCC [/color][color=black]SHRINKFILE[/color][color=gray](<[/color][color=black]TransactionLogName[/color][color=gray]>, [/color][color=black]1[/color][color=gray])[/color]
[color=blue]BACKUP [/color][color=magenta]LOG [/color][color=gray]<[/color][color=black]DatabaseName[/color][color=gray]> [/color][color=blue]WITH [/color][color=black]TRUNCATE_ONLY[/color]
[color=blue]DBCC [/color][color=black]SHRINKFILE[/color][color=gray](<[/color][color=black]TransactionLogName[/color][color=gray]>, [/color][color=black]1[/color][color=gray])[/color]
[color=black]GO [/color]


[b]How to truncate log file in SQL Server 2005[/b]
SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime I cannot shrink the log file at all.
Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.
[i][b]I. Shrink the log file size at the right time[/b][/i]
I found out this trick:
Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files
set the file size, say, 1MB.
Then, click OK and you are done.
[i][b]II. Eliminate the log file completely[/b][/i]
Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is
a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file
Let’s say, the database name is testDev. In the SQL Server Management Studio,
Highlight the database-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
After this is done, you can verify the contents of the attached database and then delete the log file.

[stewiegriffin]

truncate_only is removed from sql2008..Using truncateonly will breakup the log sequence and causes log backup failures..
better to have db in simple mode or get more space added for the ldf file than using this option..

[deals2buy]

naa daggara oka table undi vayya

daantlo columns iala unnayi

ID, City, Join_Date, Termi_Date

records emo ila unnayi
1, NULL, 2012-11-27, 2012-10-15
1,Chicago, 2012-11-27, 2020-12-31


ippudu nen query chesetappdu only one record raavali..eliminating that NULL record

PS: filter IS NOT NULL ani cheppakandi...konnitiki NULL undadu.. so only oka record vache maargam cheppandi vayya

[deals2buy]

nee yamma okkadu kuda reply ivvatam ledendi?

[Darling999]

[quote name='deals2buy' timestamp='1354122862' post='1302865549']
nee yamma okkadu kuda reply ivvatam ledendi?
[/quote]
em reply kaavaly neeku

[deals2buy]

Auto Recovery File Settings in SSMS

Every developer once in a while facing an unfortunate situation where they have not yet saved the work and their SQL Server Management Studio crashes. Well, you can minimize the loss by optimizing auto recovery settings. In this video we can see how to set the auto recovery settings.
Go to SSMS >> Tools >> Options >> Environment >> AutoRecover

There are two different settings:

1) Save AutoRecover Information Every Minutes
This option will save the SQL Query file at certain interval. Set this option to minimum value possible to avoid loss. If you have set this value to 5, in the worst possible case, you can loose last 5 minutes of the work.

2) Keep AutoRecover Information for Days
This option will preserve the AutoRecovery information for specified days. Though, I suggest in case of accident open SQL Server Management Studio right away and recover your file. Do not procrastinate this important task for future dates.

http://www.youtube.com/watch?feature=player_embedded&v=s4tFMc861r8

[deals2buy]

[quote name='DARLING...' timestamp='1354122945' post='1302865558']
em reply kaavaly neeku
[/quote]


paina oka question adigina chudu

[Desamudhuru]

[quote name='deals2buy' timestamp='1354122862' post='1302865549']
nee yamma okkadu kuda reply ivvatam ledendi?
[/quote]
think sesthuna agu bhayya

[Darling999]

[quote name='deals2buy' timestamp='1354054010' post='1302862441']
naa daggara oka table undi vayya

daantlo columns iala unnayi

ID, City, Join_Date, Termi_Date

records emo ila unnayi
1, NULL, 2012-11-27, 2012-12-31
1,Chicago, 2012-11-27, 2020-12-31


ippudu nen query chesetappdu only one record raavali..eliminating that NULL record

PS: filter IS NOT NULL ani cheppakandi...konnitiki NULL undadu.. so only oka record vache maargam cheppandi vayya
[/quote]
NULLs eliminate cheyyalentey NOT null pettali kada, ela chestaav pettakundaa

Select *
From tbl
Where City is not null this will eliminate the rows with nullsn neeku out put ela kaavaaloo particular ga cheppu

[deals2buy]

[quote name='DARLING...' timestamp='1354123327' post='1302865599']
NULLs eliminate cheyyalentey NOT null pettali kada, ela chestaav pettakundaa

Select *
From tbl
Where City is not null this will eliminate the rows with nullsn neeku out put ela kaavaaloo particular ga cheppu
[/quote]

Sorrry, paina data maarchanu chudu


I want output like this:

1,Chicago, 2012-11-27, 2020-12-31

[mtkr]

[quote name='deals2buy' timestamp='1354054010' post='1302862441']
naa daggara oka table undi vayya

daantlo columns iala unnayi

ID, City, Join_Date, Termi_Date

records emo ila unnayi
1, NULL, 2012-11-27, 2012-10-15
1,Chicago, 2012-11-27, 2020-12-31


ippudu nen query chesetappdu only one record raavali..eliminating that NULL record

PS: filter IS NOT NULL ani cheppakandi...konnitiki NULL undadu.. so only oka record vache maargam cheppandi vayya
[/quote]

same ade table tho join cheii with inner join r left outer join
On CITY column

select *
from table1 a
inner join table1 b
on a.city = b.city

[deals2buy]

[quote name='mtkr' timestamp='1354126273' post='1302865869']
same ade table tho join cheii with inner join r left outer join
On CITY column

select *
from table1 a
inner join table1 b
on a.city = b.city
[/quote]
seems like this one is correct....but will confirm again after talking to the report developer

[ravi87]

koncham Stored procedure baga vachina vallu unnara?

[mtkr]

[quote name='ravi87' timestamp='1354155528' post='1302868722']
koncham Stored procedure baga vachina vallu unnara?
[/quote]

endhi mama prathi saarii baaga ochina vallu unnara ani aduguthavvv....!!!! leru ante ne Q adagavaaa????

No 1 iz perfect in this world!!!!


ne dbt ei ikkadaaa......

[chelsea]

guys evaru ayyina data modeling meda chesaru database design and ER model ayyina sare koncham help cheyyara