How to capture results of a DBCC SHRINKDATABASE?

Question

How to capture results of a DBCC SHRINKDATABASE?

Adam Bean

One Orange Chip

Points: 26631
More actions
February 28, 2008 at 8:42 am

#154160

So, basically I'm trying to do an insert into exec(dbcc shrinkdatabase)
DECLARE
@sqlVARCHAR(1024)
,@DBNameVARCHAR(512)
SET @DBName= 'admin'
IF OBJECT_ID('tempdb.dbo.#ShrinkDB') IS NOT NULL
DROP TABLE #ShrinkDB
CREATE TABLE #ShrinkDB
(
DbIdINT
,FileIDINT
,CurrentSizeBIGINT
,MinimumSizeBIGINT
,UsedPagesBIGINT
,EstimatedPagesBIGINT
)
SET @sql=
'
INSERT INTO #ShrinkDB
(DbId,FileId,CurrentSize,MinimumSize,Usedpages,EstimatedPages)
EXEC(''DBCC SHRINKDATABASE(' + @DBName + ')'')
'
EXEC(@SQL)
SELECT * FROM #ShrinkDB
and receive the following:
Cannot perform a shrinkdatabase operation inside a user transaction. Terminate the transaction and reissue the statement.
I've tried adding a begin tran and commit tran around it, doesn't help ...
Is there any way around this? Is there any other way to capture the output of a shrink database from a procedure perspective?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 1

First, you know you shouldn't use shrinkdatabase except in rare instances. So I'd say this should be done rarely and then just use the select all, save feature of SSMS.

Second, great question. Maybe execute it from SQLCMD instead?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Steve Jones - Editor (2/28/2008)
First, you know you shouldn't use shrinkdatabase except in rare instances. So I'd say this should be done rarely and then just use the select all, save feature of SSMS.
Second, great question. Maybe execute it from SQLCMD instead?

This I know, but it's part of a very large database maintenance application I am building to allow for many a options ... this being one of them. Mainly geared towards DEV/QA servers.

Just tried it out in SQLCMD, same issue. Not sure what that would have bought me ... but no go 🙁 Good thought though.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Adam Bean

One Orange Chip

Points: 26631

February 29, 2008 at 7:33 am

#783760

Anyone?

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 4

Sorry, the only thing I've hard back is use a CLR procedure to log this information, which seems overly complicated.

I can get the data out in SQLCMD, but I get the messages as well as the results and then you'd have to do some parsing.

Perhaps you can get before and after info from the DMVs and ignore the output from here?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Steve Jones - Editor (2/29/2008)
Sorry, the only thing I've hard back is use a CLR procedure to log this information, which seems overly complicated.
I can get the data out in SQLCMD, but I get the messages as well as the results and then you'd have to do some parsing.
Perhaps you can get before and after info from the DMVs and ignore the output from here?

That's a bummer, kind of what I thought it would have to be ... but I really don't want to take that path. Damn you SQL!

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 6

Someone mentioned that you should report this on Connect as something to get fixed/changed. Or moved to a DMV.

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 7

Got this back from Erland Sommarskog, MVP.

Haven't tested, but this might work for you:

http://www.sommarskog.se/share_data.html#CLR. Also:

--

I've tested this now, and the code is below. Alas, it only works on

SQL 2008. On SQL 2005 SP2 (no CUs), I get an access violation. Since

I don't know if it works in SQL 2008 by chance, or because the bug is

fixed, I've filed

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331103

captureshrink.cs

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void captureshrink(String dbname)

{

// Connect through the context connection.

using (SqlConnection conn = new SqlConnection("context

connection=true"))

{

conn.Open();

SqlCommand cmd = new SqlCommand(

"DBCC SHRINKDATABASE('" + dbname.Replace("'", "''") +

"')", conn);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable datatable = new DataTable();

da.Fill(datatable);

cmd.CommandText = "INSERT #shrinkresult(dbid, fileid,

currentsize, " +

" minimumsize, usedpages, estimatedpage) " +

"VALUES(@dbid, @fileid, @currentsize, " +

" @minimumsize, @usedpages,

@estimatedpages)";

cmd.Parameters.Add("@dbid", SqlDbType.Int);

cmd.Parameters.Add("@fileid", SqlDbType.Int);

cmd.Parameters.Add("@currentsize", SqlDbType.Int);

cmd.Parameters.Add("@minimumsize", SqlDbType.Int);

cmd.Parameters.Add("@usedpages", SqlDbType.Int);

cmd.Parameters.Add("@estimatedpages", SqlDbType.Int);

foreach (DataRow row in datatable.Rows) {

cmd.Parameters["@dbid"].Value = row["DbId"];

cmd.Parameters["@fileid"].Value = row["FileId"];

cmd.Parameters["@currentsize"].Value =

row["CurrentSize"];

cmd.Parameters["@minimumsize"].Value =

row["MinimumSize"];

cmd.Parameters["@usedpages"].Value = row["UsedPages"];

cmd.Parameters["@estimatedpages"].Value =

row["EstimatedPages"];

cmd.ExecuteNonQuery();

}

datatable.Dispose();

da.Dispose();

cmd.Dispose();

}

};

Test script:

create assembly captureshrink from 'C:\temp\captureshrink.dll'

go

create procedure captureshrink @db sysname as external name

captureshrink.StoredProcedures.captureshrink

go

create table #shrinkresult (dbid int NOT NULL,

fileid int NOT NULL,

currentsize int NOT NULL,

minimumsize int NOT NULL,

usedpages int NOT NULL,

estimatedpages int NOT NULL)

exec captureshrink 'Northwind'

select * from #shrinkresult

go

drop table #shrinkresult

drop procedure captureshrink

drop assembly captureshrink

--

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

Yeah I had thought about that, but again, this maintenance routine needs to support SQL2000 and above. While it is a good workable solution, I simply log before and after from sysfiles and I'm content with that.

Thanks for the idea though.