none
How to manage size in SQLServer 2012

    Question

  • I am running out of disk space in SQLServer2012 running as a SharePoint 2013 repository. I though I setup limits during the setup of SharePoint 2013 and SQLserver2012. On the SQL server management studio I see SQLServer logs current and 6 archives. There are only a few users of SharePoint now and mostly calendar & task lists.

    Howl do I set the server to limit the size. I appreciate any help.


    John Lenz

    Monday, February 6, 2017 8:53 PM

All replies

  • Is it the data files (.mdf,.ndf) or the log file (.ldf) taking up the space?

    A common config error is to have databases in FULL recovery mode, but not have a Transaction Log Backup job running, in which case the log files will just grow and grow.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Monday, February 6, 2017 8:57 PM
  • Here is what I see in:

    \Program Files\Microsoft SQL server\MSSQL11.MSSSQLSERVER2012\MSSQL\DATA

    SharePoint_Admin_Content

     - mdf 135,232KB

     - ldf 11,118,592KB

    SharePoint_Config

     - mdf 147,520KB

     - ldf 205,478,720KB

    WSS_Content_My

     - mdf 37,952KB

     - ldf 5,186,960

    WSS_Content_Portal

     - mdf 34,880

     - ldf 5,192960

    WSS_Logging

     - mdf 2,144,320

     - ldf 6,400KB

    Portal is the main content site. Log file seems a bit LARGE. How do I control it?


    John Lenz

    Monday, February 6, 2017 11:03 PM
  • You need to review your backup plan for this SQL Server.  Probably your databases are in Full recovery and you are not taking Log backups.

    Are you even taking Full Backups? 

    The easiest thing is to configure a Maintenance Plan to take Full and Log backups of your databases.  Once you start doing that, you can shrink those log files if you need to.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Tuesday, February 7, 2017 1:20 AM
  • Right now you need to address your out of control log file sizes. Immediately do full database backups. Then try something like this:

    Alter database SharePoint_Config set recovery simple Alter database SharePoint_Config set recovery full Alter database SharePoint_Config set recovery simple Alter database SharePoint_Config set recovery full USE [SharePoint_Config] GO DBCC SHRINKFILE (N'SharePoint_Config_log' , 0) GO


    backup database SharePoint_Config to disk='nul'


    Set if this shrinks it. You may need to do repetitive cycles of this.

    Tuesday, February 7, 2017 1:55 AM
  • In SSMS T-SQL I kept getting an error on the alter. It said user access rights or DB could not be accessed.

    Tried this option and it worked:

    Using the SSMS GUI to shrink the transaction log file
    Open SQL Server Management Studio (SSMS).
    1. Expand the Databases node and expand User Databases.
    2. Right-click the database name that you want to shrink, and click Properties to open the Database Properties dialog box.
    3. In the Select a Page pane, click Options.
    4. View the current recovery model in the Recovery Model list box, which should be set to Full.
    5. Click the dropdown arrow in the Recovery Model section and select the Simple Recovery Model.
    6. Click OK.
    7. Right-click on the same database name and click Task-> Shrink-> Files.
    8. Use the File type drop-down menu and choose Log.
    9. You can use the default setting of Release Unused Space or select Reorganize Pages before releasing unused space. You can specify the file size by supplying a value in the Shrink file to option. (Note: The shrink may take some time depending on the size of the file.)
    10. After the shrink is completed, change the Recovery Model back to Full by clicking the Recovery Model dropdown arrow and selecting the Full Recovery Model.

    I went from 1GB free space to now 218GB free space. I will now setup a monthly process to monitor and linit log file space.

    Thanks


    John Lenz

    Wednesday, February 8, 2017 5:01 PM
  • >I will now setup a monthly process to monitor and linit log file space.

    You need a daily job to take Database and, if you stay in FULL recovery, Log backups.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Wednesday, February 8, 2017 5:54 PM
  • I am using Nova Backup for both the VM and SQLServer. 2 separate jobs.

    John Lenz

    Wednesday, February 8, 2017 6:47 PM
  • Does "Nova Backup" take log backups?  Do you ever intend to perform a point-in-time restore?

    David


    Microsoft Technology Center - Dallas

    My Blog

    Wednesday, February 8, 2017 11:58 PM
  • Sorry for the delay... just started new client engagement.

    Nova backup has a plug-in for Exchange & SQLServer. These plug-ins backup all relevant data for the application and restore full or selected portions, on demand. I do this daily

    Here is by backup plan:

    1. Daily - backup of Physical host system which includes VMs
    2. Weekly - VM image
    3. Daily exchange & SQLServer DBs

     I have already had to restore the physical system via Nova backup when I replaces/upgraded "C Drive" to a larger size. Worked fine.


    John Lenz

    Saturday, February 11, 2017 5:54 PM
  • Hi John,

    I am not familiar with Nova backup, however, I would recommend you involve SQL Server native backups in your plan. In other words, take full backup and transaction log backups regularly using Maintenance plan or SQL Server agent jobs, this way, it will prevent the transaction log grow rapidly or run out of space.

    In addition, as the issue has been solved, you can mark helpful replies as answer, other community members could benefit from the solution when they get same issues.

    Thanks,
    Lydia Zhang


    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.


    Thursday, March 2, 2017 9:34 AM
    Moderator