My MSDB Database is over 31 GB in the development server

Question

Post reply

My MSDB Database is over 31 GB in the development server

Viewing 15 posts - 46 through 60 (of 71 total)

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

bubby SSCertifiable Points: 5638 More actions · Answer 1

bubby

SSCertifiable

Points: 5638

August 11, 2010 at 11:32 am

#1205572

No the database size did not change

jvanderberg SSCertifiable Points: 6762 More actions · Answer 2

Well, the database size isn't going to change unless you shrink the database, but the amount of free space may have change. Do another right click on msdb > Reports > Standard Reports > Disk Usage. Hopefully in the right chart, you unallocated is a very high percentage.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Another alternative to that report is to re-run the last query that you ran showing the values for the sysxmit... table.

Run that query and post back the values returned for the top 10 or so rows.

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

bubby SSCertifiable Points: 5638 More actions · Answer 4

I see almost all in green color and says Unallocated, but even if I shrink the files and data the size still stays the same,

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

What is the process you are using to try and shrink the files?

through the gui, via script?

If script are you using dbcc shrinkdb or shrinkfile?

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

bubby SSCertifiable Points: 5638 More actions · Answer 6

bubby

SSCertifiable

Points: 5638

August 11, 2010 at 12:38 pm

#1205649

gui,

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

i recommend you use the dbcc shrinkfile.

Script it out in a script window and you will probably have greater success.

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

jvanderberg SSCertifiable Points: 6762 More actions · Answer 8

Shrinking the file is an important first step, and it will definitely help your immediate need. I want to reiterate, however, that it is not a long-term solution. You will have the same problem again unless you find the root cause.

bubby SSCertifiable Points: 5638 More actions · Answer 9

Hello,

I found this syntax, could you please edit it for my need,

DBCC SHRINKDATABASE

(database_name | database_id | 0

[ ,target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]

Thanks,

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Lookup DBCC Shrinkfile. I believe you will have greater success with that.

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

bubby SSCertifiable Points: 5638 More actions · Answer 11

I ried shrinking database and datfiles but with no luck still the size is 31 Gb,

i used these,

DBCC SHRINKDATABASE

( msdb);

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Re-run this query and post back the results:

USE [msdb]

SELECT object_name(i.object_id) as objectName,

i.[name] as indexName,

sum(a.total_pages) as totalPages,

sum(a.used_pages) as usedPages,

sum(a.data_pages) as dataPages,

(sum(a.total_pages) * 8) / 1024 as totalSpaceMB,

(sum(a.used_pages) * 8) / 1024 as usedSpaceMB,

(sum(a.data_pages) * 8) / 1024 as dataSpaceMB

FROM sys.indexes i

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

GROUP BY i.object_id, i.index_id, i.[name]

ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)

;

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

bubby SSCertifiable Points: 5638 More actions · Answer 13

sysssispackagesNULL120312031990

sysmail_attachmentsNULL5105094330

sysobjvaluesclst418388329332

syscollector_blobs_internalPK_syscollector_blobs_internal_paremeter_name1201201000

syscolparsclst817977000

syscolparsnc413533000

syssoftobjrefsclst413331000

sysjobactivityclust342421000

sysmultiobjrefsclst333129000

sysobjkeycryptscl333230000

sysrscolsclst252018000

sysschobjsclst252220000

syssoftobjrefsnc1251917000

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

I don't see the sysxmit table listed anymore. Does that show up in your results?

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

bubby SSCertifiable Points: 5638 More actions · Answer 15

I even ran that,

USE msdb

EXEC sp_spaceused sysxmit

it did not find the table, I do not see that any more but stll msdb is about 32 GB