July 3, 2012 at 12:42 pm
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.
July 3, 2012 at 12:58 pm
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
July 4, 2012 at 12:44 am
It is in Full Recovery Model and DBCC Opentran returns nothing..
July 4, 2012 at 1:31 am
What do you get from the below query
select log_reuse_wait_desc, name from sys.databases
July 4, 2012 at 1:55 am
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
July 4, 2012 at 8:03 am
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
July 5, 2012 at 3:00 am
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.
July 9, 2012 at 9:32 am
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..
July 9, 2012 at 9:40 am
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
July 9, 2012 at 9:42 am
Do not shrink tempdb, you risk corrupting the database and requiring a start of SQL Server to correct.
July 9, 2012 at 12:27 pm
So what step do i need to take in production environment to get free space in drive.
Please suggest me asap..
July 9, 2012 at 12:39 pm
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
July 9, 2012 at 3:20 pm
Tempdb seems is normal only and it is Production Environment so i could not restart server as of now..
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply