March 28, 2013 at 8:54 am
I have this query below that displays the size of the compressed backups for today's date. I need to change it but all my attempts broke the query. I would like to go back 3 days from whenever I run this and have the total sum of compressed backups from these three days displayed.
select distinct @@servername, convert(varchar,a.backup_start_date,101) 'Date', a.database_name 'DB Name',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024/1024),3)) as 'Compressed Database Size in(GB)',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024),3)) as 'Compressed Database Size in(MB)'
from msdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id
whereconvert(varchar,a.backup_start_date,101) = convert(varchar,getdate(),101)
and a.database_name in ( select name from sys.databases where database_id not in(1,2,3,4))
and a.type = 'd'
and b.file_type = 'd' and is_snapshot = 0
group by a.backup_set_id, a.database_name, a.backup_start_date
order by 1
March 28, 2013 at 8:59 am
Is this what your looking for:
select distinct @@servername, a.database_name 'DB Name',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024/1024),3)) as 'Compressed Database Size in(GB)',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024),3)) as 'Compressed Database Size in(MB)'
from msdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id
whereconvert(varchar,a.backup_start_date,101) >= convert(varchar,getdate()-3,101)
and a.database_name in ( select name from sys.databases where database_id not in(1,2,3,4))
and a.type = 'd'
and b.file_type = 'd' and is_snapshot = 0
group by a.database_name
order by 1
March 28, 2013 at 12:40 pm
Yep, Thanks!
January 11, 2014 at 9:25 pm
Hi folks,
The script is fine but as per performance concern we should not use any function in where condition. My script is
Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)
data retrieving from trigger table for last 3 days to get the last updated/inserted/deleted from our production table but script is running very slow as well i am getting wrong data also as my date format is in datetimestamp format in trigger table
can any body please guide me how can i overcome this issue.
Thanks
SUrya
January 12, 2014 at 3:08 am
Hi, two things (in where clause)
1.
select name from sys.databases where database_id not in(1,2,3,4)
should be
select name from sys.databases where database_id >4
2.
Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)
should be
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
January 12, 2014 at 9:56 am
Damian Widera-396333 (1/12/2014)
Hi, two things (in where clause)1.
select name from sys.databases where database_id not in(1,2,3,4)
should be
select name from sys.databases where database_id >4
To add to that, they could automate exclusion of certain user databases, as well. For example, they probably don't want to do this process on Read/Only or Offline databases so they could check the "is_read_only" and the "state" (0 = Online) flags in sys.databases. If a database is a scratchpad database (similar to what TempDB is), it will likely be in the SIMPLE recovery mode so they could also check the "recovery_model" for <3 (3 is SIMPLE recovery). Yeah...agree... that's IF they're doing proper backups on the other 2 models and they don't have the SIMPLE recovery mode on any databases that they're actually backing up. 😀
2.
Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)
should be
BWAA-HAAA! Considering the time of morning that you posted that, you must've done like I sometimes do... nod off right in the middle of a post.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2014 at 10:18 am
suryam (1/11/2014)
Hi folks,The script is fine but as per performance concern we should not use any function in where condition. My script is
Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)
data retrieving from trigger table for last 3 days to get the last updated/inserted/deleted from our production table but script is running very slow as well i am getting wrong data also as my date format is in datetimestamp format in trigger table
can any body please guide me how can i overcome this issue.
Yes and agreed. Wrapping columnns in a function usually results in a SCAN instead of a SEEK/Range scan and that can really hurt performance.
To convert your code to be able to use indexes on the id_updt (Seriously??? They abbreviated that??? :blink:) you need to absolutely avoid any calculations on table/view columns. The following is an exact but SARGable replacement for the code you've given.
WHERE id_updt >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-3,0) --Midnight 3 days ago not including today
"SARGable" effective means "can do an index seek" and the "0" in the code above is the date-serial number (shortcut) for '1900-01-01'. The code calculates the number of whole days since the "0" date and then converts that number of whole days back to a date effectively stripping the time off the date making it a WHOLE date.
The problem with your code and the replacement code above is that it's highly dependent on when you run it because there is no end date to it. What you might want to do is covert it to return the last 3 WHOLE days and that doesn't include today because today isn't ever done yet. I don't know what other people call it but I call it "boxing the dates".
The following code will return the rows for the last 3 whole days and will produce the same results no matter what time of day you run it (provided that id_updt rows are static).
WHERE id_updt >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-4,0) --Midnight 4 days ago not including today (4 necessary because TODAY is not included)
AND id_updt < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) --Midnight at the start of today
Let us know if you have any addition questions on this.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2014 at 10:22 am
Terry300577 (3/28/2013)
Is this what your looking for:
select distinct @@servername, a.database_name 'DB Name',convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024/1024),3)) as 'Compressed Database Size in(GB)',
convert(decimal(7,2),round(sum(compressed_backup_size/1024/1024),3)) as 'Compressed Database Size in(MB)'
from msdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id
where[font="Arial Black"]convert(varchar,a.backup_start_date,101) >= convert(varchar,getdate()-3,101)[/font]and a.database_name in ( select name from sys.databases where database_id not in(1,2,3,4))
and a.type = 'd'
and b.file_type = 'd' and is_snapshot = 0
group by a.database_name
order by 1
I know this post is a couple of months previous, but for the code that I've emphasized above, please consider "boxing the dates" rather than making a non-Sargable query out of it. Please see my post above (http://www.sqlservercentral.com/Forums/FindPost1530117.aspx).
Also, get out of the habit of using ORDER BY 1. It was never a good habit because the first column of the SELECT could certainly change and the method has actually been deprecated by Microsoft and will be removed from a future version of SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2014 at 12:40 am
Thanks Jeff ,
i agree with ur suggestion to create index on id_updt column.
the script is same, which i have modified in my script given below
where id_updt>=convert(datetime,convert(varchar(10),getdate()-3,101))
and id_updt< getdate()
your script
WHERE id_updt >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-4,0)
AND id_updt < select DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
and the output is same with correct value , execution plan is same for both script doing table scan which i can avoid by creating a index.
Thanks
SUrya
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply