April 11, 2011 at 7:26 am
Hi all,
We are having log shipping implemented on a server where around 100+ database exists.
Due to which the logs history tables and all are having more than 3CR of data now i m planning of truncating and deleting data from some of the tables in msdb.
Please provide me ur good suggestion where i can truncate and delete the tables data.
Also let me know do i have delete it from both primary and sec.
Thkz.
April 11, 2011 at 11:40 am
the stored procedure
sp_delete_backuphistory
is used to trim the history tables. You can find details of this in Books Online
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2011 at 12:51 am
Thankz perry but it does not delete the data from all the tables....
Perry will there be any issue if i truncate the tables.... as i dnt have any space to delete the data.
April 12, 2011 at 1:54 am
Hi
execute the following against MSDB and post the results
select object_name(object_id),
(used_page_count * 8) / 1024 as TableSizeInMBs,
row_count
from sys.dm_db_partition_stats
order by row_count desc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 15, 2011 at 8:05 am
Thkz for the reply below are the details for one server msdb :- also there are more server where the size and count is more
20055backupfile38504640
6311backupset12824185
662backupset12824185
2224backupmediafamily12824149
758backupmediafamily12824149
1260backupmediaset12824131
720backupmediaset12824131
827backupfilegroup7201321
402log_shipping_monitor_history_detail1261739
81log_shipping_monitor_history_detail1261739
103restorefile692465
84restorehistory691260
April 15, 2011 at 8:11 am
Just keep in mind that you lose a very valuable piece of information if you clear that history.
I use that to figure out when I'll get autogrowth, when I'll run out of space of hds.
Check this out
IF OBJECT_ID('tempdb..#dbs') > 0
DROP TABLE #dbs
IF OBJECT_ID('tempdb..#Drives') > 0
DROP TABLE #Drives
IF OBJECT_ID('tempdb..#Results') > 0
DROP TABLE #Results
CREATE TABLE #dbs
(
DBNAME sysname
, DBID INT
, [Total Size in MB] INT
, [Available Space In MB] INT
, DriveLetter CHAR(1)
)
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space In MB]
, DriveLetter
)
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS ''Total Size in MB''
, SUM(size / 128 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128) AS ''Available Space In MB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
CREATE TABLE #Drives
(
DriverLetter CHAR(1) PRIMARY KEY CLUSTERED
, FreeMBs INT NOT NULL
, FreeGBs AS CONVERT(DECIMAL(18 , 2) , FreeMBs / 1024.0)
)
INSERT INTO
#Drives ( DriverLetter , FreeMBs )
EXEC xp_fixeddrives
--
--SELECT
-- DB_NAME() As DBNAME
-- , DB_ID() AS DBID
-- , SUM(size / 128) AS 'Total Size in MB'
-- , SUM(size / 128 - CAST(FILEPROPERTY(name , 'SpaceUsed') AS int) / 128) AS 'Available Space In MB'
--FROM
-- sys.database_files
--WHERE
-- type_desc = 'ROWS'
--Rémi : I deleted 4 logging tables I had build on March 25th, hence the ±350 MB drop.
;
WITH CTE_Backups ( database_name, BackupDate, MinutesForBackup, GB_backup_size, seqFirst, seqLast )
AS (
SELECT
bs.database_name
, DATEADD(D , 0 , DATEDIFF(D , 0 , bs.backup_start_date)) AS BackupDate
, CONVERT(DECIMAL(18 , 1) , DATEDIFF(s , bs.backup_start_date ,
bs.backup_finish_date)
/ 60.0) AS MinutesForBackup
, CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024 / 1024) AS GB_backup_size
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date ) AS seqFirst
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) AS seqLast
FROM
msdb.dbo.backupset bs
WHERE
name IS NULL
AND bs.[type] = 'D'
)
SELECT
CONVERT(INT , dtBackups.[Available Space In GB]
/ CASE WHEN dtBackups.GB_ExpectedDailyGrowth <> 0
THEN dtBackups.GB_ExpectedDailyGrowth
ELSE 0.0001
END) AS DaysUntillDBGrowth
, *
-- INTO
-- #Results
FROM
(
SELECT
a.database_name
, dbs.DriveLetter
, drv.FreeGBs AS FreeGBs_Drive
, CONVERT(DECIMAL(18 , 1) , ( drv.FreeGBs * 0.85 )
/ CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate) * 30.468)) AS FreeGBs_Drive_InMonths_WithExpected_DB_Growth
-- , a.BackupDate AS BackupDate_First
-- , b.BackupDate AS BackupDate_Last
, DATEDIFF(dd , a.BackupDate , b.BackupDate) AS DaysPeriod
-- , a.MinutesForBackup AS MinutesForBackup_First
-- , b.MinutesForBackup AS MinutesForBackup_Last
-- , b.MinutesForBackup - a.MinutesForBackup AS MinutesForBackup_Delta
-- , a.GB_backup_size AS GB_backup_size_First
-- , b.GB_backup_size AS GB_backup_size_Last
-- , b.GB_backup_size - a.GB_backup_size AS GB_BackupGrowth
--, a.seqLast - a.seqFirst AS QtyofBackups
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate)) AS GB_ExpectedDailyGrowth
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate) * 365.256) AS GB_ExpectedAnnualGrowth
, CONVERT(DECIMAL(18 , 3) , dbs.[Total Size in MB] / 1024.0) AS [Total Size in GB]
, CONVERT(DECIMAL(18 , 3) , dbs.[Available Space In MB] / 1024.0) AS [Available Space In GB]
FROM
CTE_Backups a
INNER JOIN CTE_Backups b
ON a.seqFirst = b.seqLast
AND a.seqLast = b.seqFirst
AND a.database_name = b.database_name
INNER JOIN #dbs dbs
ON b.database_name = dbs.DBNAME
INNER JOIN #Drives drv
ON dbs.DriveLetter = drv.DriverLetter
WHERE
a.seqFirst = 1
) dtBackups
ORDER BY
database_name
IF OBJECT_ID('tempdb..#dbs') > 0
DROP TABLE #dbs
IF OBJECT_ID('tempdb..#Drives') > 0
DROP TABLE #Drives
IF OBJECT_ID('tempdb..#Results') > 0
DROP TABLE #Results
ROLLBACK
April 15, 2011 at 8:13 am
samsql (4/12/2011)
Thankz perry but it does not delete the data from all the tables....Perry will there be any issue if i truncate the tables.... as i dnt have any space to delete the data.
How about exporting the tables to other servers so you keep the history? I wouldn't delete all of it. I'd keep at least the last few weeks (3-4).
April 15, 2011 at 8:20 am
samsql (4/15/2011)
Thkz for the reply below are the details for one server msdb :- also there are more server where the size and count is more20055backupfile38504640
6311backupset12824185
662backupset12824185
2224backupmediafamily12824149
758backupmediafamily12824149
1260backupmediaset12824131
720backupmediaset12824131
827backupfilegroup7201321
402log_shipping_monitor_history_detail1261739
81log_shipping_monitor_history_detail1261739
103restorefile692465
84restorehistory691260
where is the output from the query i gave you above?
Note: Typically you would keep anything between 30-90 days history
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 15, 2011 at 9:17 am
output of the query i have pasted ....
Columns
tablename rowcount
20055 backupfile 38504640
6311 backupset 12824185
662 backupset 12824185
2224 backupmediafamily 12824149
758 backupmediafamily 12824149
1260 backupmediaset 12824131
720 backupmediaset 12824131
827 backupfilegroup 7201321
402 log_shipping_monitor_history_detail 1261739
81 log_shipping_monitor_history_detail 1261739
103 restorefile 692465
84 restorehistory 691260
April 15, 2011 at 9:47 am
Sorry i do apologise, i forgot the WHERE clause :blush:
Please execute this against MSDB and post results
select object_name(object_id),
(used_page_count * 8) / 1024 as TableSizeInMBs,
row_count
from sys.dm_db_partition_stats
where index_id in (0,1)
order by row_count desc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 25, 2011 at 11:11 pm
Hi Perry,
Please find the below output
Columns
tablename rowcount
20055 backupfile 38504640
8800 backupset 12824185
2824 backupmediafamily 12824149
1260 backupmediaset 12824131
827 backupfilegroup 7201321
402 log_shipping_monitor_history_detail 1261739
103 restorefile 692465
84 restorehistory 691260
April 26, 2011 at 2:30 am
First thing is go to the agent and then right click on it first set the history maintain in the database as attachement shows
Second things is go to Job Activity click on any job view history then click on the JOB History of all jobs and then click on the DELETE as shown in the attachment
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 26, 2011 at 4:29 am
samsql (4/12/2011)
Thankz perry but it does not delete the data from all the tables....Perry will there be any issue if i truncate the tables.... as i dnt have any space to delete the data.
What did it do, also what was the exact command and parameters you used
For the log shipping monitor history you will need to execute this SP, details are in BOL
sp_cleanup_log_shipping_history
You will need to decide how much history you would like to retain.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 26, 2011 at 9:25 pm
will it delete the data from all the tables.... as my msdb size is more than 80 GB in some server.
i dnt have space for logsize
April 27, 2011 at 12:33 am
What command are you using to execute sp_delete_backuphistory?
Both those procedures I have mentioned trim their related tables, you need to decide how history you will keep. Execute the procedures trimming 30 days at a time
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply