log file shrinking..

Question

log file shrinking..

mohan.bndr

SSCrazy

Points: 2360
More actions
July 3, 2012 at 12:42 pm

#271942

As one of my drive doesn't have sufficient free space. so i am doing log files shrinking but it is not shrinking to release the free space to drive.
can any one explain me to what steps to take to shrink the file.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

What is your recovery model?

Does dbcc opentran return anything?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mohan.bndr SSCrazy Points: 2360 More actions · Answer 2

It is in Full Recovery Model and DBCC Opentran returns nothing..

Ant-Green SSC Guru Points: 113528 More actions · Answer 3

What do you get from the below query

select log_reuse_wait_desc, name from sys.databases

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please read through this - Managing Transaction Logs[/url] and maybe this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Feeg SSCrazy Points: 2766 More actions · Answer 5

dbcc sqlperf(logspace)

this will tell you how big the log files are and what % the log file is using ...

This will give you an indication of what you could shrink the file to BUT go through Gail's links ...

I hope shrinking is not a habit as it is not good for file fragmentation.

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

DBA328 Hall of Fame Points: 3952 More actions · Answer 6

Is it the log file large or data file large? If log file large and you want to shrink the file, you could modify to simple recovery mode.

mohan.bndr SSCrazy Points: 2360 More actions · Answer 7

anthony.green (7/4/2012)
What do you get from the below query
select log_reuse_wait_desc, name from sys.databases

When i ran the above query It shows ' ACTIVE TRANSACTION' , So can i go for shrink the data file to get free space in drive. I am going to shrink Tempdb..

And i checked with dbcc sqlperf(logspace) , here the only tempdb data file is large compare to other dbs and logs..

Please suggest me..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Don't shrink TempDB. Leave it be and work out what is requiring so much space, then see if you can fix it.

As for log growth. http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Do not shrink tempdb, you risk corrupting the database and requiring a start of SQL Server to correct.

mohan.bndr SSCrazy Points: 2360 More actions · Answer 10

So what step do i need to take in production environment to get free space in drive.

Please suggest me asap..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Many possible things.

Restart SQL (though if TempDB is sized correctly for its usage that shouldn't reduce it much)

Move files to other drives (TempDB should be separated from user DBs if possible)

Extend the drive

The first question, is this TempDB usage normal or is something wrong today?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mohan.bndr SSCrazy Points: 2360 More actions · Answer 12

Tempdb seems is normal only and it is Production Environment so i could not restart server as of now..

log file shrinking..

Cookies on SQLServerCentral