December 27, 2011 at 10:49 pm
Hi Experts,
Please tell me with easy language to understand.
Thank you,
December 27, 2011 at 11:40 pm
Shrinking a Data File brings considerable percentage of Logical Fragmentation and I don’t recommend it.
i would recommend you to study how to manage data files.
anyways Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.
DBCC SHRINKDATABASE (dbName)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 28, 2011 at 12:13 am
If your mdf file is full then shrinking is out of the question and is not even applicable for discussion since you can't shrink a full file.
If your file is full, then you have a couple of options. Either grow your mdf file or add an additional data file to your database. If you don't have disk space for it, then the next logical step is to procure some disk space in order to allow for this database to grow.
Another possible answer would be to remove data - but that is dependent on your application needs and your retention policy. One place where this answer is applicable is in an "Admin" type database where various processes have been logged. At some point the data may not be useful so may need to be removed (e.g. index frag levels that are >1 year old).
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
December 28, 2011 at 12:17 am
Thanx for your reply.
December 28, 2011 at 12:19 am
You're welcome.
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
December 28, 2011 at 12:21 am
If your mdf file is full then shrinking is out of the question and is not even applicable for discussion since you can't shrink a full file.
can u suggest me any related article for above quote?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 28, 2011 at 12:22 am
And i have one more doubt.
Is there any option to use to find blocking with out profiler?
Thank you,
December 28, 2011 at 12:29 am
try this
select
t1.resource_type,
db_name(resource_database_id)as db_name,
t1.resource_associated_entity_id,
t1.request_mode as lock_requested,
t1.request_session_id as spid_of_waiter ,
t2.wait_duration_ms,
(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as get_sql_for_waiter,
(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as statement_executing_now, --- this is the statement executing right now
t2.blocking_session_id ,-- spid of blocker
(select text from sys.sysprocesses as p --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id) as get_sql_for_blocker,
getdate() as getdate
from sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where t1.lock_owner_address = t2.resource_address
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 28, 2011 at 12:29 am
Bhuvnesh (12/28/2011)
@jason,If your mdf file is full then shrinking is out of the question and is not even applicable for discussion since you can't shrink a full file.
can u suggest me any related article for above quote?
Think of it this way: Have you ever successfully shrunk an mdf file that was full (when not emptying a file by copying data to a different file)? 0% free space in that file? To shrink a file, you need to have free space in that file.
http://msdn.microsoft.com/en-us/library/ms189493.aspx
DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
Take it a bit further, if the data file is 10MB and 10MB is used, then then shrinkfile will only shrink it to 10MB.
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
December 28, 2011 at 12:32 am
For blocking, here is what Microsoft uses in Activity Monitor.
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0),
[Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
Another alternative is to use event notifications and the blocked process report. You can read about that from here.
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
December 28, 2011 at 12:35 am
thank you for your help
December 28, 2011 at 12:38 am
No problem.
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
December 28, 2011 at 1:23 am
Bhuvnesh (12/28/2011)
@jason,If your mdf file is full then shrinking is out of the question and is not even applicable for discussion since you can't shrink a full file.
can u suggest me any related article for above quote?
If you have a bottle that can hold 10 litres of water, and it currently holds 10 litres of water (it is full), it it logical to suggest using a smaller jug (shrinking)?
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply