locked
My production DB MDF file increased RRS feed

  • Question

  • hi

    My production DB MDF file increased , then I have taken transition log backup(3)times & also scheduled . now its normal, with out   Shrink the DB. It  is possible

     


    ajitkumar


    Thursday, January 16, 2020 10:27 AM

All replies

  • if  your data file (.MDF) is increased I would suggest adding more disk space to the drive and avoid using dbcc shrinkfile . but in order to control  a log file (.LDF)  you did right thing that started backup log file

    Generally people used to shrink only log file (out of space due to FULL recovery model and not doing backup log file)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 16, 2020 10:57 AM
  • hi ,

    But here the condition is different , I was taken Tlog backup 3 times MDF file will be reduce.

    Is it possible. Because want RCA regarding this situation & Solution. That's why  I want the confirm mention  


    ajitkumar


    Thursday, January 16, 2020 11:46 AM
  • Are you sure that by taking LOG File backup the MDF was reduced size? Perhaps  you looked at db size that contains MDF and LDF sizes

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 16, 2020 11:52 AM
  • yes sure ,,MDF file

    ajitkumar

    Thursday, January 16, 2020 12:08 PM
  • Taking log backup has no effect on the data file (MDF) and it's size.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 16, 2020 12:24 PM
  • I cannot reproduce this issue

    CREATE DATABASE Test
    GO
    Use Test
    GO

    CREATE TABLE Orders (ProductID INT, OrderDate DATE)
    ;WITH 
    -- first CTE which returns 10 rows (0-9)
    digits AS (
    SELECT 0 as Number
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
    UNION SELECT 6
    UNION SELECT 7
    UNION SELECT 8
    UNION SELECT 9
    )
    -- second CTE which returns 10 million rows by using
    -- a CROSS JOIN on the first CTE
    , dig AS (
    SELECT 
      (millions.Number * 1000000)
    + (hThousands.Number * 100000) 
    + (tThousands.Number * 10000) 
    + (thousands.Number * 1000) 
    + (hundreds.Number * 100) 
    + (tens.Number * 10) 
    + ones.Number AS Number
    FROM digits AS ones 
    CROSS JOIN digits AS tens
    CROSS JOIN digits AS hundreds
    CROSS JOIN digits AS thousands
    CROSS JOIN digits AS tThousands
    CROSS JOIN digits AS hThousands
    CROSS JOIN digits AS millions
    )
    -- Third CTE which generates a "Product ID" and "Order Date". 
    -- Product ID is repeated after 500,000 records. 
    -- after every 1000 records, "order date" moves backward.
    , prod AS (
    SELECT number, (number % 500000) + 1 AS ProductID, GETDATE() - (number / 1000) AS OrderDate
    FROM dig 
    WHERE number < 5000000 -- we need only 5 million records :-)
    )
    -- the insert statement goes here
    INSERT Orders (ProductID, OrderDate)
    SELECT productID, 
    DATEADD(dd,0, DATEDIFF(dd,0,OrderDate)) -- strips off "time" from date value
    FROM prod



    SELECT      
                db.name AS DatabaseName,
                SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS DatabaseSize,
                SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS LogSize,
                SUM(8192.0E * af.size / 1048576.0E) AS TotalSize
    FROM        master..sysdatabases AS db
    INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
    WHERE       db.name ='Test'
              
    GROUP BY    db.name


    SELECT      
                db.name AS DatabaseName,
                SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS DatabaseSize,
                SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS LogSize,
                SUM(8192.0E * af.size / 1048576.0E) AS TotalSize
    FROM        master..sysdatabases AS db
    INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
    WHERE       db.name ='Test'
    GROUP BY    db.name

    DatabaseName DatabaseSize LogSize TotalSize
    Test           136          1544    1680


    BACKUP DATABASE [Test]  TO DISK ='E:\Test.BAK'
    BACKUP LOG [Test] TO DISK ='E:\Test_Log.BAK'

    ---Same sizes

    DatabaseName DatabaseSize LogSize TotalSize
    Test 136      1544           1680


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 16, 2020 12:24 PM
  • The MDF file contains DATA, not logs.  Taking a transaction log backup does not in any way affect the size of an MDF file.

    In addition, the physical files in SQL Server NEVER reduce in size, unless you manually shrink the file/database.

    So what exactly is your question or problem?

    Thursday, January 16, 2020 4:18 PM
  • Hi ajitkumar,

    As others mentioned, only shrinking the database or file can reduce mdf file.

    If you are sure mdf file is reduced, you could try to use default trace to analyze when and who may have shrunk the database or file. Please refer to this blog for more details.

    Hope this could help you.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 17, 2020 5:01 AM
  • hi Amelia,

    So please help how to MDF file reduce as per  our discussion, how got the log who did this. Because I a, sure?


    ajitkumar

    Tuesday, January 21, 2020 10:01 AM
  • You can only shrink the mdf file by using DBCC SHRINKFILE (1,100) for example .. But it hit performance of the database. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 21, 2020 1:45 PM
  • Hi ajitkumar,

    To shrink MDF file, please refer to DBCC SHRINKFILE.

    And please note that shrink MDF file may cause Index Fragmentation.

    Hope this could help you.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 22, 2020 6:40 AM