August 7, 2010 at 10:41 am
dsohal (8/6/2010)
I get this info,dbo. sysssispackages # of records = 20 Data 9544 KB
dbo.sysmail_attachments # of records = 18 Data 4064 KB
dbo.sysjobhistory # of records = 1000 Data = 2184
These are the top 3 please
Looking at the size of the tables, the Data File is not consuming more space. Please schedule regular transaction log backups, so that the size of the Transaction Log remains under control.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 7, 2010 at 10:47 am
Adiga (8/7/2010)
dsohal (8/6/2010)
I get this info,dbo. sysssispackages # of records = 20 Data 9544 KB
dbo.sysmail_attachments # of records = 18 Data 4064 KB
dbo.sysjobhistory # of records = 1000 Data = 2184
These are the top 3 please
Looking at the size of the tables, the Data File is not consuming more space. Please schedule regular transaction log backups, so that the size of the Transaction Log remains under control.
Adiga, we've established that his transaction log is 500M. Besides, you can't do a transaction log backup on msdb.
August 7, 2010 at 4:11 pm
Have you checked to make sure that the data file is truly using as much space as the size reports?
You have a few reports that show that your space usage is not that much compared to the database size.
What is the free space in your data file?
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
August 7, 2010 at 4:15 pm
Here is one more resource you can use to help determine your data space usage.
http://jasonbrimhall.info/2010/05/05/sql-2005-tablespace/
That link has a script to help find the size of the tables and directly translate to MB.
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
August 7, 2010 at 4:18 pm
AndrewSQLDBA (8/6/2010)
There is approx 17 Gigs in these 4 tables.dbo syscollector_blobs_internal 1 960 952 8 0
dbo sysmail_attachments 18 4080 4064 8 8
dbo sysssispackages 21 9640 9616 24 0
dbo sysjobhistory 1000 3384 2176 128 1080
Based on the script and the output, it looks like 17Mb.
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
August 8, 2010 at 12:25 pm
i'm assuming you're database mail, have you checked connectivity to the mail server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 9, 2010 at 2:54 am
MSDB has "System" Tables which is likely where the large Tables are
> Open MSDB DB, then Tables, and finally the System Tables folder in SSMS (F7)
Tables like sysjobhistory, backupmediafamily, backupfile, backupfilegroup, backupset, backupmediaset
Can @ times become quite large and may need some cleanups
August 11, 2010 at 8:43 am
This may or may not help.
Do you have a job to Clean Up History? The Maintenance Plan Wizard has an option for Clean Up History. It will delete old Backup and Restore History, Sql Server Agent Job History and Maintenance Plan History (sp_delete_backuphistory, sp_purge_jobhistory, sp_maintplan_delete_log) older than a certain time period. All of these are stored in MSDB.
Steve
August 11, 2010 at 8:57 am
Hello,
I get this error The SaveToServer method has encountered OLE DB error code 0x80040E14 (Could not allocate space for object 'dbo.sysssispackages' in database 'msdb' beacause the 'PRIMARY' filegroup is full.
August 11, 2010 at 9:03 am
If you are getting the error when try to set up a Clean Up job, I would look up the reference in BOL for sp_delete_backuphistory, sp_purge_jobhistory or sp_maintplan_delete_log and try to run it directly from a query window.
Steve
August 11, 2010 at 9:06 am
Well isn't that just the definition of irony? Your msdb database is too big and can't allocate space, either because you've limited the size or the hard drive is full.
Here's essentially what the cleanup task is doing:
DECLARE @week4 DATETIME
SET @week4 = DATEADD(wk, -4, GETDATE());
EXEC msdb.dbo.sp_delete_backuphistory @week4;
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@week4;
EXEC msdb..sp_maintplan_delete_log null,null,@week4;
That's for anything 4 weeks old or older. You can change it to whatever range you want.
I doubt it will help, though. I saw how large those tables are, and they aren't your problem. I don't know what is your problem, but it doesn't appear to be a table (which is really weird, since tables are generally what takes up the most space).
August 11, 2010 at 9:17 am
This did not help because I purged the history and even the 4 weeks did not do any help to the cause,
August 11, 2010 at 9:34 am
Have you thought about moving the MSDB database to a different drive?
That would solve all your problems. A drive with a lot more space.
Andrew SQLDBA
August 11, 2010 at 9:35 am
Space is the issue, I do not have much for it on the other drives either,
August 11, 2010 at 9:40 am
Time to purchase more. Drives are cheap. How mush is this problem costing you?
with all the time wasted on here, you could have already paid for many drives by now, if this is causing your database to not work correctly.
I don't think that you ever answered my question from last week.
Are you using this box as a SSIS Server? You are storing a large amount of SSIS Packages? If not, delete them from the table. Do you store them in the database, move them to the File System and store them on another drive.
Andrew SQLDBA
Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply