Shrink command
-
What does the shrink command exactly?
I start the shrink command in the Enterprise Manager of SQL7/2000. Does it include re-indexing of all tables?
And is it the same command as auto-shrink can be selected in the db properties?
Thanks for your info
mipo
-
This was removed by the editor as SPAM
-
Here is what I know and it is not a lot. I'm not an expert.
The shrink command will remove unused space from the DB. If preformed after a backup or translog backup, it will chance the sizes of the data and trans log files even if the file sizes were set when you created the DB. This also depends on the recovery model you have your DB set up at (Full, Simple, Bulk-loading)
Auto shrink runs about every 30 minutes and shrinks the DB. I'm NOT sure if it does the exact same operation as the shrink command.
I drought the shrink command re-indexes. If you have a maintenance plan, you can set the DB to shrink and reindex separately.
-
Quoted from BOL.
"SQL Server 2000 autoshrinks databases that have a large amount of free space. Only those databases where the autoshrink option has been set to true are candidates for this process. The server checks the space usage in each database periodically. If a database is found with a lot of empty space and it has the autoshrink option set to true, SQL Server reduces the size of the files in the database. You can also use SQL Server Enterprise Manager or the DBCC SHRINKDATABASE and DBCC SHRINKFILE statements to shrink the files of a database manually.
Files are always shrunk from the end. For example, if you have a 5 GB file and specify 4GB as the target_size in a DBCC SHRINKDB statement, SQL Server will free as much space as it can from the last 1 GB of the file. If there are used pages in the part of the file being released, SQL Server first relocates the pages to the part being retained. You can only shrink a database to the point where it has no free space remaining. For example, if a 5GB database has 4 GB of data and you specify 3 GB as the target_size of a DBCC SHRINKDATABASE statement, only 1 GB will be freed.
If a DBCC SHRINKDATABASE or DBCC SHRINKFILE statement cannot reclaim all the space in a log file, the statement will issue an informational message indicating what action you must perform to make more space eligible to be freed."
It doesn't perform reindex to tables.
-
Here are some more useful quotes
"AUTO_SHRINK only reduces the size of the transaction log if the database is set to SIMPLE recovery model or if the log is backed up. The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater"
So, looks like the answer to your question is yes, auto shrink does the same thing as the running the shrink command. But, what may be more import to the effectiveness of shrinking is when the command is executed.
To regain the most space, backup your DB / trans log then shrink it. If you are really worried about space, set the recovery model to simple
-
Another tidbit of information: The Auto Shrink option is not available for the following System databases: MASTER and TEMPDB.
Even if you switch the MASTER database to Full Recovery you still can't set Auto Shrink. The TEMPDB can't even be set to Full Recovery.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
That makes sense since the Temporary database is recreated each time the server starts.
The concern with autoshrink is that it may run at high peak times. It should also be turned off if you are dumping large amounts of data in the database on a regular basis. Just create a job that you can run manually or schedule for off-peak hours.
Patrick
Quand on parle du loup, on en voit la queue
-
Thanks for all the information from you guys
mipo
-
Just an additional comment for pbirch.
quote:
That makes sense since the Temporary database is recreated each time the server starts.We had the unique situation where our 17 GB TEMPDB was not recreated after a server reboot.
See Reply posted on SqlServerCentral @
Cheers, hot2use
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Oh, the TempDB is not recreated in the sense that what was there when you stopped service is restored. I meant that it is created fresh (based on the size of the model database) when the server restarts.
English is just a funny language sometimes.
Patrick
Quand on parle du loup, on en voit la queue
-
From my experience never turn on "Auto-Shirnk" option in production database.SQL might starts shirnking database and will hinder the regular SQL operation. I have seen other processes "hang" because of the locks created by auto-shrink.
You can manually shrink using "DBCC SHRINKDATABASE".Even if you run DBCC SHRINKDATASE , it needs to be done after backup. Its always a good practice to make DBCC SHRINKDATABASE as part of Weekly Backup procedure.
Regarding REINDEX I don't think SHIRNKDATABSE does reindexing. I would normally run DBCC DBREINDEX as part of Weekly maintenance.
Thanks
Sreejith
-
I have TONS of experience with shrinking databases, and not by my choice.
I didn't read the whole post, so if someone already made the points I am going to make, my apologies.
First, DBCC SHRINKFILE or DATABASE doesn't reindex indexes, it merely gives back unused pages to the OS file system. On that note, I have noticed better results(faster in most cases) if you do a DBCC DBREINDEX or INDEXDEFRAG before you shrink, since both of these operations have a tendency to get rid of internal fragmentation of the database files. Non-clustered index tables obviously excluded. Pages in order are always a good thing, and over time shrinking and growing database files will cause both internal and external(file system) fragmentation which will cause a deep performance drain due to the I/O system working more to read stuff. Sequential reads are always better than "random jumping around the disk" reads. I swear that's the technical term. 😉
When I got to my current position the database files and log files were being grown and shrunk daily since, quite frankly, they had no ideal it was bad and they loaded the database up with so much data without any planning. They also ran huge SELECT INTOs which grew the log files to extremely large amounts. Some of our databases take 5 hours to shrink a database by a few gigs. Or at least they used to.
If you aren't noticing any results, run a shrink for a smaller amount. To find out what's actually being used, I put together a stored procedure. I'll paste it here I guess.
USE master
GO
CREATE PROC uspGetDbSpaceStats
@dbname sysname
AS
/*
Author: James S. Fal II
Email: james.fal@attbi.com
Purpose:
Returns extents reserved, used, and percentage of extents used
for database passed into dbname, in megabytes
Notes:
Does not update space usage.
Copyright 2002 James S. Fal II, like anyone really cares, I just hope this proves useful
*/
SET NOCOUNT ON
DECLARE @tsql nvarchar(1000)
IF EXISTS(SELECT 1 FROM master..sysdatabases WHERE name = @dbname)
BEGIN
SET @tsql =
' USE ' + @dbname + ';' + char(10) + char(13) +
' CREATE TABLE #tmp_sfs ( ' + char(10) + char(13) +
' fileid int,' + char(10) + char(13) +
' filegroup int, ' + char(10) + char(13) +
' totalextents decimal, ' + char(10) + char(13) +
' usedextents decimal,' + char(10) + char(13) +
' name varchar(1024),' + char(10) + char(13) +
' filename varchar(1024)' + char(10) + char(13) +
' ) ' + char(10) + char(13) +
' INSERT INTO #tmp_sfs EXECUTE(''DBCC showfilestats WITH NO_INFOMSGS'')' + char(10) + char(13) +
' SELECT name, ' + char(10) + char(13) +
' (totalextents*64)/1024 As Reserved, (usedextents*64)/1024 As Used, (usedextents/totalextents) * 100 As PrcntUsed ' + char(10) + char(13) +
' FROM #tmp_sfs ' + char(10) + char(13) +
' DROP TABLE #tmp_sfs' + char(10) + char(13)
--PRINT @tsql
EXEC sp_executesql @tsql
SET NOCOUNT OFF
END
GO
Run this sproc and you'll have the amount you can shrink down to. Based on that you can do something like DBCC SHRINKFILE(file_name,10256). That would shrink the database down to 10,256 MB. I run smaller shrinks for, say a few gigs, instead of trying to shrink a database by 30 gigs all at once.
Hope this helps!
-
Thanks for your extensive information regarding shrinking databases.
May I pose a general question:
I am wondering how people maintain their SQL Servers which run at several customers sites and disk space is getting smaller and smaller? I want to say that we have tables which hold a lot of date consisting of statistics, errors, logs etc. They grow and grow and existing data is not needed anymore as soon as the data get older than let's say for one year. How do you overcome the problem reducing the tables but not charging the system too much as the major application also runs on the same server?
Thanks for any input
mipo
-
Set up some sort of archiving process. We have a similar situation. I have a dedicated box that I use for data mining. I have created a SP solution that archives data from our Prod DB's to the archiving box . You can schedule it to run various times a week. I have also created reporting solutions from the archived DB.
Each situation is different and you would need to create a solution that is tailored to your needs.
-
"Shrinking the Transaction Log" is an excellent topic from BOL to answer this topic . It has a clear illustration as to what happens when you shrink the logfile.
sp_msforeachdb " use ?;exec sp_spaceused "
gives you information on all the unused space by db.
here is a job that I execute on all my development servers
sp_msforeachdb "use ?; backup log ? with truncate_only; dbcc shrinkfile(2,25)"
note: I assume the second file in all your databases are the log file
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply