none
SQL detach (delete logs) RRS feed

  • Question

  • Hi,

    This is happening on SBS 2008

    I'm new to sql first off all.

    This is the problem:

    Cpu 100%

    looked for the problem and came out in sql.

    Logs where to big.

    I've logged in to sql with \\.\pipe\mssql$microsoft##ssee\sql\query

    So i've detached WSS_SEARCH_HVBSRV_1 and deleted log files

    then i tryed to reatach it ,here it went wrong . I got a fault saying it can't be attached.

    So now months later memory i still going 96-98 % , because it can't find that database.

    Events are 1827,1105,3760

    Can somebody help me?

    Regards,

    Chris

    PS: can you say excact what i have to do


    • Edited by Chris_VL Wednesday, June 27, 2012 1:51 PM
    Wednesday, June 27, 2012 1:46 PM

Answers

  • Hi Chris_VL,

    >> Could not allocate space for object 'dbo.EventLog'.'PK_EventLog' in database 'SBSMonitoring' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    Regarding to the error message, seems might be related to the unplanned growth of the Data or Transaction log can take up space in the disk. Please check if the autogrow option for both Data and log are not set * By percent *. You can see this by selecting Database->properties->Data files tab and Transaction log tab.

    Based on my research, "SQL Server only releases all the pages that a heap table uses when the following conditions are true: A deletion on this table occurs. A table-level lock is being held. Note A heap table is any table that is not associated with a clustered index.

    If pages are not deallocated, other objects in the database cannot reuse the pages. However, when you enable a row versioning-based isolation level in a SQL Server 2005 database, pages cannot be released even if a table-level lock is being held.

    For more information, please refer to Microsoft's solution from this KB

    Additional please refer the following links for more information:
    1. http://support.microsoft.com/default.aspx/kb/315512/EN-US/
    2. http://support.microsoft.com/kb/272318/ : Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
    3. http://support.microsoft.com/kb/317375/ : A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
    4. http://support.microsoft.com/kb/247751/:  BUG: Database maintenance plan does not shrink the database
    5. http://support.microsoft.com/kb/305635/ :  A timeout occurs when a database is automatically expanding

    Regards, Amber zhang

    Thursday, June 28, 2012 2:40 AM
    Moderator
  • Chris,

    There are many different things going on here.

    1. The log file for the database was deleted.

    This could cause inconsistant data issues, among others. If the log file grows too large then investigate why and fix accordingly, we can help you with that.

    2. Memory utilization is high.

    I believe workgroup edition comes with SBS2008 but I'm not positive. If it is workgroup edition it'll use up to 4 GB of memory for the instance (not counting memory allocated outside of the BP). If this is too much, then Min and Max memory must be set appropriately.

    3. Permissions, rights, etc.

    It seems you have this one figured out?

    -Sean


    Sean Gallardy, MCC | Blog

    Thursday, June 28, 2012 1:16 PM
    Answerer

All replies

  • Here are some other pics

    Wednesday, June 27, 2012 1:53 PM
  • Hi Chris,

    If you have data file (.mdf) you can attach the database.

    CREATE DATABASE WSS_Search_HVBSRV_1
    ON (FILENAME = 'D:\WSS_Search_HVBSRV_1_data.mdf')
    FOR ATTACH_REBUILD_LOG ;

    Note:- here you need to replace the path of data filename with your path.

    Ideally you should never delete the log file after detaching. There are other ways to reduce log file size. As on busy system doing this cause data loss.


    - Chintak (My Blog)

    Wednesday, June 27, 2012 1:58 PM
  • Hi Chintak,

    Thx for fast response.

    I've also added NT AUTHORITY\NETWORK SERVICE to the database becuase there was a event saying failed login for this user

    I've detached but now it raises events like

    Insufficient SQL database permissions for user '' in database 'WSS_Search_HVBSRV_1' on SQL Server instance 'np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query'. Additional error information from SQL Server is included below.

    The EXECUTE permission was denied on the object 'proc_MSS_Cleanup', database 'WSS_Search_HVBSRV_1', schema 'dbo'.

    and

    Database 'WSS_Search_HVBSRV_1' on SQL Server instance 'np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query' is not empty and does not match current database schema.

    also have this

    CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

    Could not allocate space for object 'dbo.EventLog'.'PK_EventLog' in database 'SBSMonitoring' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    Wednesday, June 27, 2012 2:50 PM
  • Hi Chris_VL,

    >> Could not allocate space for object 'dbo.EventLog'.'PK_EventLog' in database 'SBSMonitoring' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    Regarding to the error message, seems might be related to the unplanned growth of the Data or Transaction log can take up space in the disk. Please check if the autogrow option for both Data and log are not set * By percent *. You can see this by selecting Database->properties->Data files tab and Transaction log tab.

    Based on my research, "SQL Server only releases all the pages that a heap table uses when the following conditions are true: A deletion on this table occurs. A table-level lock is being held. Note A heap table is any table that is not associated with a clustered index.

    If pages are not deallocated, other objects in the database cannot reuse the pages. However, when you enable a row versioning-based isolation level in a SQL Server 2005 database, pages cannot be released even if a table-level lock is being held.

    For more information, please refer to Microsoft's solution from this KB

    Additional please refer the following links for more information:
    1. http://support.microsoft.com/default.aspx/kb/315512/EN-US/
    2. http://support.microsoft.com/kb/272318/ : Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
    3. http://support.microsoft.com/kb/317375/ : A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
    4. http://support.microsoft.com/kb/247751/:  BUG: Database maintenance plan does not shrink the database
    5. http://support.microsoft.com/kb/305635/ :  A timeout occurs when a database is automatically expanding

    Regards, Amber zhang

    Thursday, June 28, 2012 2:40 AM
    Moderator
  • Thx for the replies

    So i did manage to solve a few events with the permission rights and exceeding the limit of SBS 2008 4GB.

    but can you help me with this one

    Database 'WSS_Search_HVBSRV_1' on SQL Server instance 'np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query' is not empty and does not match current database schema.

    The server went from 98 to 87% memory usage ,so i see a bit off light in the tunnel.

    Thx again for the responses

    Christophe

    Thursday, June 28, 2012 10:50 AM
  • Chris,

    There are many different things going on here.

    1. The log file for the database was deleted.

    This could cause inconsistant data issues, among others. If the log file grows too large then investigate why and fix accordingly, we can help you with that.

    2. Memory utilization is high.

    I believe workgroup edition comes with SBS2008 but I'm not positive. If it is workgroup edition it'll use up to 4 GB of memory for the instance (not counting memory allocated outside of the BP). If this is too much, then Min and Max memory must be set appropriately.

    3. Permissions, rights, etc.

    It seems you have this one figured out?

    -Sean


    Sean Gallardy, MCC | Blog

    Thursday, June 28, 2012 1:16 PM
    Answerer
  • Sean,

    I've searched for the log file and it's still there,but my last error

    Database 'WSS_Search_HVBSRV_1' on SQL Server instance 'np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query' is not empty and does not match current database schema.

    How do i need to solve this if i don't have backup?

    Permissions are ok i think. No more faults in event viewer.

    For this faults

    CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

    Could not allocate space for object 'dbo.EventLog'.'PK_EventLog' in database 'SBSMonitoring' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    i've turned off SBS manager and SBSmonitoring service.

    renamed SBSmonitoring.mdf and ldf  to sbsmonitoring-bak.mdf and sbsmonitoring_log-bak.ldf

    and copied the new one there.

    turned on services again.

    Can you help with this?

    Thx

    Christophe

    Thursday, June 28, 2012 3:17 PM