Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2010
    Posts
    14

    Unanswered: Database backup when size exceeds certain value

    I would like to do a Automated full backup of a Database when the size of the Database exceeds certain value. Is that possible?
    if yes, could I write a T-SQL Script to do it?

    also, after doing the backup of the database, I would want the database to be truncated, such that it only keep some of the latest records. How do I do that in T-SQL?

    I know about Maintenance Plans can do Automated Scheduling, but its recurring is based on Date. How can this be modified such that it is based on size of the database?

    thanks.

  2. #2
    Join Date
    Sep 2011
    Posts
    71
    Hello : Please try the solution below and tell me the result

    FileSizeLimitInBytes is set to 5000000 which limits your download so just set it to maximum! (this is client side btw on windows 7)



    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\WebClient\Parameters

    Right click on the FileSizeLimitInBytes and click Modify
    Click on Decimal
    In the Value data box, type 4294967295, and then click OK. Note this sets the maximum you can download from the Webdav to 4 gig at one time, I haven’t figured out how to make it unlimited so if you want to download more you need to split it up.



    hopefully this helps for you guys!

  3. #3
    Join Date
    Sep 2011
    Posts
    31
    Quote Originally Posted by tanthiamhuat View Post
    I would like to do a Automated full backup of a Database when the size of the Database exceeds certain value. Is that possible?
    if yes, could I write a T-SQL Script to do it?

    also, after doing the backup of the database, I would want the database to be truncated, such that it only keep some of the latest records. How do I do that in T-SQL?

    I know about Maintenance Plans can do Automated Scheduling, but its recurring is based on Date. How can this be modified such that it is based on size of the database?

    thanks.
    You can create a job that keeps on checking the size of the database every few minutes. If it exceeds the threshold defined by you, the job takes the backup, Deletes records from the table and shrink the data file.

    However, Few things to note here:
    1. Backups should be scheduled to run on a regular interval.
    2. Shrink is not really a good option unless there is a very large amount of free space and you are sure your database wont reach that size in near future.
    Last edited by cindyaz; 10-12-11 at 11:02. Reason: replaced the word backup with database in first line

  4. #4
    Join Date
    Mar 2010
    Posts
    14

    MS SQL Server Maintenance Plan

    but MS SQL Server Maintenance Plan does not allow us to create jobs to check the size of database every timer interval, does it?

  5. #5
    Join Date
    Sep 2011
    Posts
    31
    No maintenance plan doesn't have this option. You will have to write your own code for it. something like this:
    Code:
    Find database size
    If database size>certain value then
    1. code to backup the database
    2. code to truncate tables
    3. code to shrink data files.
    end if
    Schedule the script you write based on above logic to execute after 5 minutes or so using SQL Agent.

    Repeat this for all databases (if you need to). Easy way to do this is by using sp_msforeachdb undocumented stored proc.

  6. #6
    Join Date
    Mar 2010
    Posts
    14
    1. code to backup the database
    2. code to truncate tables

    I can understand the 2 above, can you explain below:
    code to shrink data files.
    any good relevant links?

    thanks.

  7. #7
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Cheers....

    baburajv

  8. #8
    Join Date
    Mar 2010
    Posts
    14
    Hi Cindyaz,

    thanks for your posting..
    Find database size
    If database size>certain value then
    1. code to backup the database
    Schedule the script you write based on above logic to execute after 5 minutes or so using SQL Agent.

    basically, I can find the database size with this : sp_helpdb MyDatabase
    now, I need to retrieve "db_size", and compare it with my threshold value set.
    how do I retrieve "db_size" in T-SQL script, so that I can compare with threshold?
    can we put everything in one T-SQL script?

    I am quite new to T-SQL script, and need some of your guidance, thanks.

  9. #9
    Join Date
    Mar 2010
    Posts
    14
    private void CheckDatabaseSize_Click(object sender, RoutedEventArgs e)
    {
    try
    {
    SqlDataReader myReader = null;
    SqlCommand myCommand1 = new SqlCommand("sp_helpdb DatabaseABC", conn);

    myReader = myCommand1.ExecuteReader();

    while (myReader.Read())
    {
    DatabaseSize.Text = myReader["db_size"].ToString();

    }
    myReader.Close();
    }
    catch (Exception ex)
    {
    DatabaseSize.Text = ex.ToString();
    }

    }

    private void PerformFullBackup_Click(object sender, RoutedEventArgs e)
    {
    MessageBox.Show("This is a time consuming process and could take more than 30 minutes");
    char[] charsToTrim = { ' ', 'M', 'B' };
    string dbsize = DatabaseSize.Text.TrimEnd(charsToTrim);
    double Database_Size = Convert.ToDouble(dbsize);
    double threshold_dbsize = 5000; // in MB

    // if DataBase_Size is greater than threshold_dbsize, then perform full backup
    if (Database_Size > threshold_dbsize)
    {
    SqlDataReader myReader = null;
    string backup = "BACKUP DATABASE [XSAT_Logging] TO " +
    "DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Backup\\Dat abaseABC.bak'" +
    "WITH NOFORMAT, NOINIT, NAME = N'XSAT_Logging-Full Database Backup'," +
    "SKIP, NOREWIND, NOUNLOAD, STATS = 10";

    SqlCommand myCommand1 = new SqlCommand(backup, conn);
    myCommand1.CommandTimeout = 10000000;
    myReader = myCommand1.ExecuteReader();
    myReader.Close();
    }
    }

    basically, how do I put the above code in a single T-SQL script, so that the SQL Agent can execute it?

  10. #10
    Join Date
    Mar 2010
    Posts
    14
    -- My First T-SQL
    DECLARE @threshold varchar(200), @databasesize varchar(200)
    SET @threshold = 500

    DECLARE @DB varchar(100)
    SELECT @DB = 'DatabaseABC'
    CREATE TABLE #helpdb (name varchar(200), db_size varchar(200), owner varchar(200), dbid int, created datetime, status varchar(5000), compatibility_level varchar(200))
    INSERT INTO #helpdb EXECUTE sp_helpdb
    SET @databasesize = (SELECT db_size FROM #helpdb WHERE name = @DB )
    DROP TABLE #helpdb

    PRINT @databasesize
    IF @databasesize > @threshold
    PRINT 'We want to perform backup'

    -- PRINT @databasesize shows 9165.63 MB
    -- The IF statement is not evaluated, because @threshold = 500, and I cannot put 500 MB. Are we able to remove MB from @databasesize? or what other better solutions?

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    DECLARE @threshold varchar(200), @databasesize varchar(200)
    SET @threshold = 500
    Shouldn't that be
    Code:
    DECLARE @threshold varchar(200), @databasesize varchar(200)
    SET @threshold = '500'
    Or even better
    Code:
    DECLARE @threshold BIGINT, @databasesize BIGINT
    SET @threshold = 500
    Perhaps it should be INT instead of BIGINT or DEC, but at least a numerical data type.

    If you do comparisons with strings, you can get this kind of pleasant, but unexpected result:
    Code:
    IF '2' > '1000000000000' 
         PRINT 'WOOT! I am richer than Bill!!!!! I quit my job.'
    Last edited by Wim; 10-14-11 at 03:19.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Sep 2011
    Posts
    31
    Quote Originally Posted by tanthiamhuat View Post
    Hi Cindyaz,

    thanks for your posting..
    Find database size
    If database size>certain value then
    1. code to backup the database
    Schedule the script you write based on above logic to execute after 5 minutes or so using SQL Agent.

    basically, I can find the database size with this : sp_helpdb MyDatabase
    now, I need to retrieve "db_size", and compare it with my threshold value set.
    how do I retrieve "db_size" in T-SQL script, so that I can compare with threshold?
    can we put everything in one T-SQL script?

    I am quite new to T-SQL script, and need some of your guidance, thanks.
    sp_helpdb is an SP.
    You can create a temp table with the column names returned by sp_helpdb
    create table #mySpaceTable
    (name varchar(255),
    db_size varchar(20)
    ...
    ..
    ...
    )

    insert into #mySpaceTable
    Exec sp_helpdb

    select * from #mySpaceTable where db_size>'your value'

    However a better way to do is query sysfiles.
    select sum(size/128) as 'DB Size in MB' from sysfiles

    This will give you the database size in MB which you can compare with your value.
    Make your script to work for a particular database and later use sp_msforeachdb.

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by tanthiamhuat View Post
    -- My First T-SQL
    Try this:
    Code:
    DECLARE @threshold_MB DEC(10, 2)
    DECLARE @databasesize_MB DEC(10, 2)
    DECLARE @DB sysname
    
    SET @threshold_MB = 500
    SELECT @DB = 'test'
    
    CREATE TABLE #helpdb (
    	name sysname, 
    	db_size Nvarchar(13), 
    	owner sysname, 
    	dbid smallint, 
    	created nvarchar(11), 
    	status varchar(6000), 
    	compatibility_level tinyint
    )
    
    INSERT INTO #helpdb EXECUTE sp_helpdb
    
    SELECT @databasesize_MB = ROUND(
    			CAST(substring(LTRIM(RTRIM(db_size)), 1, len(LTRIM(RTRIM(db_size))) - 3) as DEC(10, 2)) *
    			CASE WHEN LEFT(Right(LTRIM(RTRIM(db_size)), 2), 1) = 'K' 
    					THEN 0.001 
    				WHEN LEFT(Right(LTRIM(RTRIM(db_size)), 2), 1) = 'M' 
    					THEN 1 
    				WHEN LEFT(Right(LTRIM(RTRIM(db_size)), 2), 1) = 'G' 
    					THEN 1000
    				WHEN LEFT(Right(LTRIM(RTRIM(db_size)), 2), 1) = 'T' 
    					THEN 1000000
    				ELSE NULL
    			END, 2)
    FROM #helpdb 
    WHERE name = @DB
    
    DROP TABLE #helpdb
    
    PRINT @databasesize_MB
    IF @databasesize_MB > @threshold_MB
    	PRINT 'We want to perform backup'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •