Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server msdb

Posted on 2008-06-10
3
Medium Priority
?
646 Views
Last Modified: 2008-10-07
A SQL Server article says:

"Frequently you'll want to know how fast your database has been growing. Ideally, you'll have historical size information on all the databases that you work on. In the real world, however, this is not necessarily the case.

What we have most often is the backup history. Luckily, we can get a rough outline of the growth of your database, over time, from the msdb..backupset table. This query will give the size of the backup, every time that a backup was done. From this you can get a pretty good idea of how fast your database is growing."

The above statement is followed by the following T-SQL query:

select
 BackupDate = convert(varchar(10),backup_start_date, 111)
 ,SizeInGigs=floor( backup_size/1024000000)
from msdb..backupset
where
 database_name = 'DatabaseName'
 and type = 'd'
order by
 backup_start_date desc


Please advise how to interpret the result. Thanks.

0
Comment
Question by:ksfok
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Expert Comment

by:sbagireddi
ID: 21755780
This gives you the size in gigs of a particular database ..say 'pubs' in starting from today to about a month.
The backup_size/1024000000 converts the size from bytes to gigabytes.

So for eg I ran it on one of our dbs:

2008/06/10      0
2008/06/10      13
2008/06/10      0
2008/06/09      2
2008/06/09      52
2008/06/09      11
2008/06/09      0
2008/06/09      0
2008/06/09      14



0
 

Author Comment

by:ksfok
ID: 21761545
But excuse me. What does the number list exactly mean? How can it be applied?
0
 
LVL 8

Accepted Solution

by:
sbagireddi earned 2000 total points
ID: 21761722
The numbers are the size in Gigabytes for the database backup  on that particular day.

Just a means for you to track the database size and estimate how much hard drive space you might need.

So for eg :If the backup increases by 10% every month, then you can expect the size to double in 10 months and accordingly you can size you hard drive or buy extra or new hard drives.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question