DBCC ShrinkFile

May 25, 2013 by Leave a Comment

Being day 25 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC SHRINKFILE.

Description:

DBCC SHRINKFILE is used to shrink an individual log in SQL Server.

DBCC SHRINKFILE Syntax:

dbcc shrinkfile
(
    { 'file_name' | file_id }
    {
        [ , EMPTYFILE]
        | [ [, target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
    [ WITH NO_INFOMSGS ]

Example:

The following example DBCC ShrinkFile is used to shrink a log file that grew too large.

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
 FROM sys.database_files;

GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DBHealthHistory SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DBHealthHistory_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DBHealthHistory SET RECOVERY FULL;
GO
-- Be sure to do a full backup, then kick off transaction log backups

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
 FROM sys.database_files;

Related Posts:

Blog Post Shrinking a Log File.

Notes:

For more information see TSQL Wiki DBCC shrinkfile.

DBCC Command month at SteveStedman.com is almost as much fun as chasing geoducks on the beach.

Filed under DBCC Commands Tagged with , , , , ,

Common Table Expressions Presentations Updated.

May 20, 2013 by Leave a Comment

I used to be able to do the Common Table Expression in a single 1 hour session, but after writing the book on Common Table Expressions, I discovered that I just can’t fit it into an hour with the detail that Common Table Expressions deserve. I would rather go into depth so that everyone can master CTEs rather than just breezing over the highlights and leaving everyone wondering.

For future SQL Saturdays and other presentations the sessions have been broken out as follows. Currently I have proposed the following 2 sessions for SQL Saturday Denver.

Common Table Expressions – Introduction

Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expression session you will learn everything needed to start using CTEs for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTEs. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. The class is designed for people who haven’t used CTEs before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expression session.

Common Table Expressions – Advanced

You have been introduced to Common Table Expression, you understand the ;WITH syntax, but you want to know more. Learn how to recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE and DELETE data from CTEs. Some of the common use cases for CTEs will be covered including finding holes in patterns, finding and removing duplicate data, string parsing, and more.  See how CTEs compare to SQL Server 2012 offset and fetch paging techniques. Get an in depth understanding of the performance behind a common table expression. Understand when the CTE is the right solution, and the wrong solution to use. Finally we will take a look at some classic recursive algorithms and how they can be implemented with CTEs. This session pairs well with the Introduction to Common Table Expression session.

Filed under CTE, SQL Saturday Tagged with , , , , , ,

DBCC InputBuffer

May 17, 2013 by Leave a Comment

Being day 17 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC INPUTBUFFER.

Description:

DBCC INPUTBUFFER allows access to the last query run for a specific session (SPID).

DBCC INPUTBUFFER Syntax:

dbcc inputbuffer
(
    session_id [ , request_id ]
)
    [WITH NO_INFOMSGS ]

Example:

The following example we get a list of the sessions, then browse the queries that they have been running.

SELECT *
 FROM master.dbo.sysprocesses P
 ORDER BY spid DESC;

DBCC_InputBuffer

Next if we use one of the SPIDs shown above we can look up the query that was last run as shown below for SPID 55.


DBCC INPUTBUFFER (55);

Which produces the following:

DBCC_InputBuffer2

Which shows us that SPID 55 is running the stored procedure called ReportServer.dbo.GetMyRunningJobs.

Browse around on your own SQL Server and see what you can find.

Notes:

For more information see TSQL Wiki DBCC inputbuffer.

DBCC Command month at SteveStedman.com is almost as much fun as last minute preparation for a SQL Saturday presentation.

Filed under DBCC Commands Tagged with ,

DBCC IndexDefrag

May 16, 2013 by 1 Comment

Being day 16 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC INDEXDEFRAG.

Description:

DBCC INDEXDEFRAG was used for to defragment indexes on SQL Server 2000, the same thing can be done with Alter Index on newer versions of SQL Server..

DBCC INDEXDEFRAG Syntax:

dbcc indexdefrag
(
    { 'database_name' | database_id | 0 }
    , { 'table_name' | table_id | 'view_name' | view_id }
    [ , { 'index_name' | index_id }
    [ , { partition_number | 0 } ] ]
)
    [ WITH NO_INFOMSGS ]

For more info on index defragmentation, check out the Database Health Reports application. Below is a sample screenshot of the Index Fragmentation report.

Index_Fragmentation_Report

Notes:

Note that DBCC INDEXDEFRAG was deprecated and replaced by ALTER INDEX.

For more information see TSQL Wiki DBCC indexdefrag.

DBCC Command month at SteveStedman.com is almost as much fun as conveyor belt sushi.

Filed under DBCC Commands Tagged with , , ,

DBCC FreeSystemCache

May 14, 2013 by Leave a Comment

Being day 14 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC FREESYSTEMCACHE.

Description:

DBCC FREESYSTEMCACHE is used to free all memory associated with all of the SQL Server caches.

DBCC FREESYSTEMCACHE Syntax:

dbcc freesystemcache
(
    'ALL' [, 'POOL NAME']
)
    [ WITH
        {
            [ MARK_IN_USE_FOR_REMOVAL ]
            [, [ NO_INFOMSGS ] ]
        }
    ]

Example:

The following example we first select form sys.dm_os_memory_clerks to find all of the available system caches that could be freed.

SELECT DISTINCT name
 FROM sys.dm_os_memory_clerks;
DBCC FREESYSTEMCACHE('SQL Plans');

In order to clear all the plans for a specific database we can call DBCC FreeSystemCache witht he database name as shown here.

DBCC freesystemcache ('Northwind');

If we want to free everything we would us the ‘ALL’ parameter as shown here.

DBCC FREESYSTEMCACHE ('ALL');

And finally the option to mark the in use items for removal when they are done being used.

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Notes:

For more information see TSQL Wiki DBCC freesystemcache.

DBCC Command month at SteveStedman.com is almost as much fun as samauri sudoku.

Filed under DBCC Commands Tagged with ,

DBCC FreeSessionCache

May 13, 2013 by Leave a Comment

Being day 13 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC FREESESSIONCACHE.

Description:

DBCC FreeSessionCache is only available on SQL Server 2005 and newer it removes all queries from the distributed query cache. This however does not impact anything in the standard procedure cache, to clear the standard procedure cache use DBCC FreeProcCache instead. The distributed query cache contains the queries that used the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources.  There is not output generated other than the standard completed message.

DBCC FreeSessionCache Syntax:

DBCC FreeSessionCache

Example:

The following example clears all distributed queries from the distributed query cache.

DBCC FreeSessionCache

Which produces the following output.

DBCC_freeSessionCache1

Notes:

One of the less common DBCC commands, keep in mind if you really want to clear the procedure cache use DBCC FreeProcCache instead.

For more information see TSQL Wiki DBCC freesessioncache.

DBCC Command month at SteveStedman.com is almost as much fun as sitting in the waiting room at the dentist.

 

 

Filed under DBCC Commands Tagged with , , ,

← Older posts