August 8, 2011 at 1:01 pm
what i have to do this situation please giveme some suggetions...
thankyou in advance
August 8, 2011 at 1:08 pm
my first inclination is to see if there are any databases that are in FULL recovery mode, but have NEVER been backed up:
they probably have gigabite sized log files and megabyte sized data...
how many of these databases are NULL for the backup date?
SELECT
d.name,
MAX(b.backup_finish_date) AS last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b
ON d.name = b.database_name
AND b.type = 'D'
WHERE d.database_id NOT IN (2, 3) -- Bonus points if you know what that means(temp, model)
GROUP BY d.name
order by last_backup_finish_date
Lowell
August 8, 2011 at 1:33 pm
Lowell (8/8/2011)
my first inclination is to see if there are any databases that are in FULL recovery mode, but have NEVER been backed up:they probably have gigabite sized log files and megabyte sized data...
Databases in full recovery that have never been backed up will likely have small log files, because they're in pseudo-simple until a backup is taken. What you should be looking for is databases that are in full recovery model (which, btw, database id 2 cannot be in) that have never had a log backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2011 at 1:59 pm
thankyou...
August 8, 2011 at 2:07 pm
Actually the first thing you need to do is identify what's on that drive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 12:08 am
AAKR (8/8/2011)
what i have to do this situation please giveme some suggetions...thankyou in advance
Run the following query you can easily find it actually SQL is occupying space or not.
select size/128 as size_MB,db_name(database_id)as dbname ,* from sys.master_files
where physical_name like 'j:%'
order by size/128 desc
select database_name,d.recovery_model_desc,d.log_reuse_wait_desc,backup_finish_date,type,
backup_size/1024/1024 size_mb,physical_device_name
from msdb.dbo.backupset b join msdb.dbo.backupmediafamily m
on (b.media_set_id=m.media_set_id)
join sys.databases d
on (d.name=b.database_name)
where
physical_device_name like 'j:%'
--database_name='DS_ReArch' and
--type='D'
order by backup_size/1024/1024 desc
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply