June 10, 2010 at 9:00 am
I am unable to shrink data and log files in dev/test server.
I am using below query
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
MyDB
data file 763 MB By 1 MB, unrestricted growth
Log file 5 MB By 10 percent, restricted growth to 2097152 MB
D:Mydb.mdf 781312 KB
Mydb.ldf 5000 KB
June 10, 2010 at 9:12 am
laddu4700 (6/10/2010)
I am unable to shrink data and log files in dev/test server.
In what way are you unable to shrink ?
Why do you want to shrink ?
It looks like you are atempting to shrink the log file to 1 meg twice ? It would just grow again anyway as it is used.
June 10, 2010 at 9:21 am
We have a space issue on drive, so need to shrink the data and log files for databases. I tried to shrink but the mdf and ldf file sizes remain same. Please let me know if there are any other options
June 10, 2010 at 9:33 am
How much free space is in the .MDF and .LDF ?
June 10, 2010 at 9:45 am
sp_spaceused details
database_namedatabase_sizeunallocated space
mydb 767.88 MB 0.08 MB
reserveddata index_sizeunused
781232 KB486760 KB286096 KB8376 KB
June 10, 2010 at 9:59 am
The log might not shrink, depending on where the active portion is. You can use this to help: http://qa.sqlservercentral.com/scripts/30026/
I know you mentioned a space issue, but unless your files are wildly out of spec with what's needed, you need to get space. You don't want to shrink data files, since it fragments indexes. That reduces performance, and to fix it, you need to reindex, which needs free space.
For logs, if you shrink below what's used, they'll just grow again.
It's a poor operational practice to keep shrinking below what the system needs. You should have free space in both files to handle loads. Log needs space between backups to handle the largest size it will grow to with a pad. The data file needs space to handle data growth.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 10, 2010 at 10:30 am
+1
Great reply Steve. I've been using a similar script for many years to shrink what we've always referred to as "unruly logs" 😀
Additionally, laddu4700, if you find that you are forced to use this script frequently (read "more than once"), you should begin to suspect that someone that has access to the environment is not your friend and is introducing uncommitted transactions. You may want to use the undocumented command, DBCC LOGINFO, to determine that. If you want more info on the command, my favorite explanation (and the one I point all my DBAs to) is: http://www.mssqltips.com/tip.asp?tip=1225 (go about half way down...or just search for LOGINFO)
Anyway, good luck, and post back with an update when you get it fixed. (and by that, I mean when you get some disk space added to allow for that log to grow and function normally 😉 )
-Patrick
June 10, 2010 at 10:56 am
Frequent transaction log backups can help manage log growth.
June 10, 2010 at 12:14 pm
ldf are fine. I need to shrink mdf inorder to release some space
June 10, 2010 at 12:52 pm
Doesn't look like you have any free space to shrink.
June 10, 2010 at 1:02 pm
here is a script I use to see database stats
declare @myTable as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
fileid varchar(4),
fileGroup varchar(4),
TotalExtents int,
usedExtents int,
DBName varchar(100),
filename1 varchar(max))
insert into @myTable
exec ('sp_msforeachdb @command1="use [?]; dbcc showfilestats"')
select
((SUM(usedExtents)*64)/1024) as DatabaseUsedSpaceinMB,
((SUM(totalExtents)*64)/1024) as databaseSizeinMB
from @myTable
select DBName, usedExtents, TotalExtents,
((usedExtents*64)/1024) as DatabaseUsedSpaceinMB,
((totalExtents*64)/1024) as databaseSizeinMB
from @mytable
dbcc sqlperf(logspace)
this should tell you what your db space usage as well as log space usage is, as well as what the unused space is.
you may need to delete some data in order to do a shrink. remember if you do a shrink you will fragment you db and you will need to reindex. re-indexing will cause it to grow again, depending on how much room you free up will depend on whether this is worth it to you or not.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
June 10, 2010 at 1:12 pm
laddu4700 (6/10/2010)
ldf are fine. I need to shrink mdf inorder to release some space
You only have 8Mb space free in your mdf. I don't think that is going to help you.
You may want to check for tmp tables in the database (or other permanent tables that are only there to house temporary data, some developers like to store that data in a tmptable without the # symbol. When that is done, the table will not clean out.)
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply