April 30, 2014 at 7:40 am
You might be working under a misconception around shrink.
It's not compression or anything similar. It's not something that has a persistent state. It's not something where you can say 'this database is still shrunk'. It's just a process.
Shrink moves data pages towards the beginning of the file and then releases any free space to the operating system. That's all it does. In the process of moving pages around, it causes index fragmentation, that's a side effect.
It's like going through your lounge and putting all the books in one spot, along with all the unpaid and paid bills, junk mail, etc. Once it's done, it's done and the room isn't in some state of quantum super-stability for a period of time.
It's just that everything is in one corner and if you need the bill that's due tomorrow you have to sort through the unread mystery novel, the paid bill from 3 months ago and a pile of garbage offering you cheap garden furniture in order to find it
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
April 30, 2014 at 7:43 am
Ha! In short, no, it doesn't "stay shrunk". As you add data (or junk mail) to the system, it's going to need to grow to contain that additional data. You can grow it manually or you can let SQL Server grow it for you, but it's largely dictated by the amount of data you have in your system (among other things, there are tons of details here you can get into if we have to).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2014 at 7:55 am
Beautifully explained. Thanks guys. Cleared almost everything. :satisfied:
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 14, 2014 at 2:32 pm
I 'd like to run this trace between 8 am and 2 pm and I just wanted to know if it looked OK, I got this from the website but made some minor changes. I am assuming once I run it around 8am, it will stop after 6 hours?
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 05/14/2014 02:52:24 PM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @EndTime DATETIME
declare @OutputFileName nvarchar(100)
set @maxfilesize = 200
set @OutputFileName = 'C:\Trace\FirstTrace' +
CONVERT(VARCHAR(20), GETDATE(),112) +
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')
set @EndTime = DATEADD(mi, 360, getdate())
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @maxfilesize, @EndTime
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply