February 3, 2012 at 1:51 pm
I have a vendor product that has some tables like track each change or history of activity, which make our tables really big, Currently the database data file reserved 13 gb.
we don't want to keep those, so vendor send us some maintence script that deletes data to a cut off date for some tables.
It is a stored procedure that do above task. Basically is to delete some records in some history tables to a cutoff date.
But then he wrote some code like below after the exc stored procedure command:
dbcc shrinkfile(SurveyorDB_Data, 1)
go
dbcc shrinkfile(SurveyorDB_Log, 1)
go
I am a little hesitated to run this, because it shrinks files to 1 MB?
February 5, 2012 at 2:23 am
This is a duplicate of this post:
http://www.sqlservercentral.com/Forums/Topic1246826-1550-1.aspx
Shrink file does not cause any data loss. If you specify 1MB, it will shrink only to the smallest possible size. As your DB has very little unallocated space, this won't free up the disk space. Try rebuilding your indexes first on the tables you've removed data from.
Again, SHRINKFILE will introduce fragmentation in your indexes if you don't use TRUNCATEONLY. I'd advise rebuilding all your indexes after the shrink. Do the shrink only as a one off operation only after freeing large volumes of space in your data files. 13-14GB is pretty small for a database - If disk space is that tight on your server it might be time to invest in some new disks.
Hope this helps,
David
DBA Dash - Free, open source monitoring for SQL Server
February 5, 2012 at 12:34 pm
sqlfriends (2/3/2012)
I have a vendor product that has some tables like track each change or history of activity, which make our tables really big, Currently the database data file reserved 13 gb.we don't want to keep those, so vendor send us some maintence script that deletes data to a cut off date for some tables.
It is a stored procedure that do above task. Basically is to delete some records in some history tables to a cutoff date.
But then he wrote some code like below after the exc stored procedure command:
dbcc shrinkfile(SurveyorDB_Data, 1)
go
dbcc shrinkfile(SurveyorDB_Log, 1)
go
I am a little hesitated to run this, because it shrinks files to 1 MB?
As a sidebar, you should never run 3rd party provided code for such things unless you know precisely what the code is going to do. It's fine to ask for some help but what you really need to do is lookup the command in Books Online (the free "Help" system that comes with SQL Server) and understand the command so that you actually know what people are talking about when you do ask for help. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply